Hi :) Congrats!! Thanks for letting the list know you have solved the problem and also given clues a to how you achieved that! Often there is not much need to trouble-shoot down to precise causes because the exact situation is unlikely to recur. General principles often help more. Tidying the code might seem like a trivial "waggle the wires" type answer but that is so often the way of fixing things that it's worth knowing which types of wires are worth waggling!
Also the thread drew out some interesting discussion which a lot of us learned from and which may help us build better databases in the future. Most people are here because they like to learn and/or because they enjoy problem-solving and/or they like helping people migrate to LibreOffice and help them enjoy using it. So, you didn't waste our time. The main devs working on the main code-base for LibreOffice have been largely focussing on cleaning the code so it's good for us non-devs to see that activity does have good results rather than us just some theoretical notion that it might be a good idea. The main thing is that you did fix it!! Congrats and regards from Tom :) >________________________________ > From: Marion & Noel Lodge <[email protected]> >To: "[email protected]" <[email protected]> >Sent: Monday, 10 June 2013, 10:42 >Subject: Re: [libreoffice-users] SQL statement error when linking Main form to >SubForm > > >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 > > > -- 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
