Hi Everyone, It is with a rather red face that I report that my problem with linking the Main Form with the Sub Form has gone away!!
I imported the data for my new database from an old Paradox application and as part of the process I used Basic Macros to manipulate the various tables. I added surrogate keys as primary keys and needed to get those into various other tables as foreign keys for linking purposes. The Macros I used were copied from another application I have written, and modified as needed. I think that I must have been sloppy with my database initialization coding and possibly ended up with conflicting variables with the same names. Anyway, when I tidied up my code the problem disappeared! I tried retracing my steps, but so far I have not been able to reproduce the problem I was having. I would much rather have 'nailed' exactly where I went wrong, but I have run out of time and will have to let it be for now. So, my apologies for wasting your time. I appreciated all the help I received, and I have picked up some new resources and ideas from your replies. Thanks again, Noel -- Noel Lodge [email protected] On 7 June 2013 16:55, Marion & Noel Lodge <[email protected]> wrote: > 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 > [email protected] > > > On 7 June 2013 09:38, Hank Alper <[email protected]> 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 <[email protected]>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 >>> [email protected] >>> >>> >>> On 6 June 2013 20:57, Dan Lewis <[email protected]> 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 >>> >> [email protected] >>> >> >>> >> >>> >> On 5 June 2013 21:51, Dan Lewis <[email protected]> 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 >>> >>>> [email protected] >>> >>>> >>> >>>> 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%[email protected]> >>> >>> > >>> >>> 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%[email protected]> >>> > 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: [email protected] >>> 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: [email protected] 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
