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.**lib**reoffice.org<http://libreoffice.org>
<users%**2Bunsubscribe@global.**libreoffice.org<users%252Bunsubscribe@global.libreoffice.org>
>
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