Thanks for all the replies. Jay Yes, I have a similar structure to the id/idfam you suggested. If for example, I am displaying family 2 (Smith) I need a way of getting the 2 in place of f.id in your query, to display only Art, Sue and Ann in the Roll data sheet. (e.g. WHERE idFam = 2)
Hank Thanks for your reply. Are you using the HSQL database. The linking works fine there, but not if you are using the H2 database as I am. Dan Thanks for pointing me to the examples using a FilterExample table. Populating that table could perhaps be triggered by the Text modified Event of the Form's FamilyID field. I have previously written some Basic Macros that can read a value, (in this case it could be theFamilyID) from a Form. I've also got a Macro that can apply a filter, say to the Roll data sheet. I'll experiment with these two possibilities and report back on what I come up with. Thanks again, N oel -- Noel Lodge lodg...@gmail.com On 7 June 2013 09:38, Hank Alper <hankal...@gmail.com> wrote: > Hi Noel > I'm building a database for a fishing club. I have Member and Boat as two > of the many tables in the DB. > I have a form which shows my Member data and a sub form with the Boat data > of all the boats the member owns. > I used the wizard to produce this form. How did you establish the > relationship between your > two tables? > I linked the primary key in the Member table (equivalent to your Family > table) and a foreign key in the Boats table (equivalent to your Rolls > table). > I did this by building the tables using SQL coding, including expressing > the Constraints in SQL.showing the link between the foreign key in Boats > and the Member primary key. > My form shows the Member data in columnar form and the Boat data for the > member as a data sheet which includes all the boats owned by the member > which is what I think you want for your project. > Hank > > > On Thu, Jun 6, 2013 at 8:21 AM, Marion & Noel Lodge <lodg...@gmail.com>wrote: > >> Hi Dan, >> >> My form displays a Church Membership Roll - >> >> - The Families Table contains the family's Surname, Address, Street Map >> Reference, Date Loaded, Date Last Updated etc. >> - The Roll Table has a separate line for each family member. It includes >> First Name, Title, Date of Birth, Membership Status, Occupation etc. >> >> On the form the family fields are displayed in Columnar format with labels >> left. The Roll fields are in a Data sheet. >> >> When I call up a family I want all the family members to automatically >> appear, and I was hoping to do that using Main form / Sub Form linking. >> >> At the moment, by removing the linking, I can get all of the Roll table >> rows appearing in the Data sheet, so I think I'll write a macro to filter >> the rows to include only those associated with the currently displayed >> family. I think this would work just as well as the linking mechanism, >> but >> can you think of a better solution? >> >> Thanks, >> >> Noel >> -- >> Noel Lodge >> lodg...@gmail.com >> >> >> On 6 June 2013 20:57, Dan Lewis <elderdanle...@gmail.com> wrote: >> >> > It seems that this has been a problem since 2010 with >> OpenOffice.org >> > Base, so it is likely a problem with LibreOffice Base as well. >> > This link describes how one person solved it. >> > >> > http://h2-database.66688.n3.**nabble.com/Parameterized-** >> > Queries-with-OpenOffice-Base-**td845715.html< >> http://h2-database.66688.n3.nabble.com/Parameterized-Queries-with-OpenOffice-Base-td845715.html >> > >> >> > >> > There is something more that I would like to know: >> > 1) What purposes do this form and subform serve? (Specifically, why use >> > the parameter?) >> > 2) How does the design of the form accomplish these purposes? >> > >> > --Dan >> > >> > >> > On 06/05/2013 09:40 PM, Marion & Noel Lodge wrote: >> > >> >> Hi Dan, >> >> >> >> Thank you for your prompt and detailed reply. >> >> >> >> I'm using the H2 database and I suspect that was the vital piece of >> >> information that I failed to mention in my first post. Sorry about >> that. >> >> >> >> I originally linked the two tables using the Wizard. Then later I used >> the >> >> Form properties dialog to un-link and then relink them, but to no >> avail. >> >> >> >> When I got your reply, I created a new test Form going through all the >> >> steps you laid out, but I got the same error message as before. >> >> >> >> I then went to Queries | 'Create Query in SQL view' and ran this query >> - >> >> >> >> SELECT * FROM "LUCROLLS"."PUBLIC"."Roll" >> >> WHERE ("FamilyKey" = :link_from_FamilyID) >> >> >> >> With the SQL button on, (Run SQL command directly), I got the same >> error. >> >> With the button off I got a prompt to manually enter the value of >> >> ':link_from_FamilyID'. Entering a 1 produced the same error as before. >> >> >> >> I finally tried the query in a native, (HSQL not H2) database, (I >> changed >> >> the table name to suit the other database), and when I manually >> entered a >> >> suitable integer it worked! >> >> >> >> So my conclusion is that setting Table links in a Base Form doesn't >> work >> >> when using H2 as the database. Is that right? >> >> >> >> Thanks again for all your help - much appreciated. >> >> >> >> Noel >> >> -- >> >> Noel Lodge >> >> lodg...@gmail.com >> >> >> >> >> >> On 5 June 2013 21:51, Dan Lewis <elderdanle...@gmail.com> wrote: >> >> >> >> Onn 06/05/2013 02:29 AM, Marion & Noel Lodge wrote: >> >>> >> >>> Hi, >> >>>> I have a Form with families info in the Main Form and family members >> >>>> info >> >>>> in a Sub Form. The Tables and relevant fields are - >> >>>> Main Form (Families) >> >>>> FamilyID BIGINT IDENTITY >> >>>> <Info fields follow> >> >>>> >> >>>> Sub Form (Roll) >> >>>> RollID BIGINT IDENTITY >> >>>> FamilyKey BIGINT >> >>>> <Info fields follow> >> >>>> >> >>>> I have linked the forms using fields FamilyID and FamilyKey - a one >> to >> >>>> many >> >>>> link. However I get the error - >> >>>> The data content could not be loaded. >> >>>> SQL Statement: >> >>>> SELECT * FROM "LUCROLLS"."PUBLIC"."Roll" >> >>>> WHERE ("FamilyKey" = :[*]link_from_FamilyID) [42001-139] >> >>>> >> >>>> The problem seems to be the 'link_from_FamilyID' phrase. I Googled >> >>>> 42001, >> >>>> and all it says is that it is an invalid SQL Statement, but I don't >> know >> >>>> what is wrong. Even if I did, the SQL statement is generated by the >> >>>> Wizard! Is there a way of manually over riding the Wizard? Failing >> >>>> that >> >>>> I'll need to write a macro to filter the Roll records to achieve >> what I >> >>>> want to happen. But I shouldn't need to do this. In another >> >>>> application >> >>>> I >> >>>> have previously got this sort of linking to work. >> >>>> >> >>>> Can anybody see what I am doing wrong here? >> >>>> >> >>>> I'm running LO version 3.5.6.2 and Windows 7. >> >>>> >> >>>> Thanks, >> >>>> >> >>>> Noel >> >>>> -- >> >>>> Noel Lodge >> >>>> lodg...@gmail.com >> >>>> >> >>>> Needed information: How did you link these two tables? >> (Specific >> >>> steps please.) Are you using Base as the front end to another >> database? >> >>> Where did Base get the name "LUCROLLS"? (I know that "PUBLIC" is the >> >>> schema >> >>> and 'Roll" is the table name.) Is "LUCROLLS" the name of the database >> >>> perhaps? >> >>> I created a database with two tables: Families and Roll. >> Families >> >>> was >> >>> given the primary key, FamilyID (BIGINT). Roll was given two fields: >> >>> RollID >> >>> (primary key, BIGINT), and FamilyKey (BIGINT). Using the Form Wizard, >> I >> >>> created a form with Families as the main form and Roll as the subform. >> >>> Step >> >>> 2: (Setup sub form) Clicked "Add subform" box. Clicked Next. Step 3: >> (Add >> >>> subform fields) Selected "Table:Roll" from the Tables or queries >> >>> drop-down >> >>> list. Clicked the double right arrow to move all the fields from the >> >>> table, >> >>> Roll, to the "Fields in form" list. Clicked Next. Step 4: (Get joined >> >>> fields) Select FamilyKey from the "First joined subform field" >> drop-down >> >>> list. Select FamilyID from the "First joined main form field" >> drop-down >> >>> list. Click Next. I then went through the rest of the steps of the >> Form >> >>> Wizard. >> >>> Possible way to correct the situation: Edit the form. >> >>> Add the Form Controls toolbar. (View > Toolbars > Form Controls) The >> >>> fourth icon from the left is the Form tool (icon). But first control >> >>> click >> >>> the FamilyKey field. Then click the Form tool. (The Form properties >> >>> dialog >> >>> opens.) Click the Data tab. Click the ellipse (...) for "Link master >> >>> fields". (The "Linked fields" dialog opens.) Select FamilyKey from the >> >>> top >> >>> Roll drop-down list. Select FamilyID from the Families drop-down list. >> >>> Click OK. Close the Form Properties dialog. Save the Form. Save the >> >>> database. >> >>> >> >>> --Dan >> >>> >> >>> -- >> >>> To unsubscribe e-mail to: users+unsubscribe@global.**lib** >> reoffice.org<http://libreoffice.org> >> >>> <users%**2Bunsubscribe@global.**libreoffice.org< >> users%252bunsubscr...@global.libreoffice.org> >> >>> > >> >>> Problems? >> http://www.libreoffice.org/****get-help/mailing-lists/how-to-* >> >>> *** <http://www.libreoffice.org/**get-help/mailing-lists/how-to-**> >> >>> unsubscribe/<http://www.**libreoffice.org/get-help/** >> >>> mailing-lists/how-to-**unsubscribe/< >> http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/> >> >>> > >> >>> Posting guidelines + more: http://wiki.**documentfoundati**on.org/**< >> http://documentfoundation.org/**> >> >>> Netiquette <http://wiki.**documentfoundation.org/**Netiquette< >> http://wiki.documentfoundation.org/Netiquette> >> >>> > >> >>> List archive: http://listarchives.**libreoff**ice.org/global/users/< >> http://libreoffice.org/global/users/> >> >>> <http://**listarchives.libreoffice.org/**global/users/< >> http://listarchives.libreoffice.org/global/users/> >> >> >>> > >> >>> >> >>> All messages sent to this list will be publicly archived and cannot be >> >>> deleted >> >>> >> >>> >> >>> >> > >> > -- >> > To unsubscribe e-mail to: users+unsubscribe@global.**libreoffice.org< >> users%2bunsubscr...@global.libreoffice.org> >> > Problems? http://www.libreoffice.org/**get-help/mailing-lists/how-to-** >> > unsubscribe/< >> http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/> >> > Posting guidelines + more: http://wiki.**documentfoundation.org/** >> > Netiquette <http://wiki.documentfoundation.org/Netiquette> >> > List archive: http://listarchives.**libreoffice.org/global/users/< >> http://listarchives.libreoffice.org/global/users/> >> > All messages sent to this list will be publicly archived and cannot be >> > deleted >> > >> > >> >> -- >> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org >> Problems? >> http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ >> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette >> List archive: http://listarchives.libreoffice.org/global/users/ >> All messages sent to this list will be publicly archived and cannot be >> deleted >> >> > -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
