Thanks Tom, You are very encouraging.
Noel -- Noel Lodge [email protected] On 10 June 2013 20:13, Tom Davies <[email protected]> wrote: > 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
