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