Re: [libreoffice-users] SQL statement error when linking Main form to SubForm

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
lodgemn@gmail.com


On 5 June 2013 21:51, Dan Lewis <elderdanlewis@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
> > lodgemn@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.**libreoffice.org<users%2Bunsubscribe@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+unsubscribe@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