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

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
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