Date: prev next · Thread: first prev next last
2013 Archives by date, by thread · List index 

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


On 7 June 2013 09:38, Hank Alper <hankalper@gmail.com> 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 <lodgemn@gmail.com>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
lodgemn@gmail.com


On 6 June 2013 20:57, Dan Lewis <elderdanlewis@gmail.com> 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
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





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






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

Context

Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.