OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Data Entry Form and List or ComboBox

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
vicramz
General User
General User


Joined: 26 Mar 2008
Posts: 14

PostPosted: Thu Mar 27, 2008 4:23 am    Post subject: Data Entry Form and List or ComboBox Reply with quote

I'm building a database for our tennis association, and want to associate individual records (father, mother, child, etc.) with family records (to hold common data such as address, home phone).
I've set up a table for each, created a primary key in the Family table (FamilyID) and placed that field in the Individuals table. I set up a one-to-many relationship between the two tables on this field, because a family may have several members.
I built a family data entry form that works fine. My problem is with the individuals.
What I want to do is create a data entry form for individuals which contains a listbox on the FamilyID field (or better yet, the Family Last Name, as I don't want to remember ID numbers), that will force the assignment of a new individual to a pre-existing family.
Even better than that, I'd like to create a data entry form that would allow the creation of a family, then input the individuals belonging to that family, all on one form.
I built a family data form, with a subform for individuals, but can't get the subform to advance to a second individual. When I save the record, the screen moves to a new family.
I haven't tried creating the subform as a data table, because I've got a lot of data fields for each individual and believe a data table would be too wide for the screen.
Ideas?
_________________
vicramz
Back to top
View user's profile Send private message
MSPhobe
Super User
Super User


Joined: 29 Sep 2005
Posts: 529
Location: England

PostPosted: Tue Apr 01, 2008 5:37 am    Post subject: Reply with quote

Not exactly an answer to your question, but maybe useful....

At...

http://sheepdogguides.com/fdb/fdb1refinteg.htm

.. you will find a longish tutorial on referential integrity. It shows you how to make sure that the family code you enter for an individual is at least a code present in your FamilyID table.

The heart of what might be useful to you culminates about three quarters of the way down the page, in the paragraph....

Quote:
Re-open the StockNames table. Try entering "TEST / a / xxx / TestRecord". It will let you enter that... but as soon as you try to leave the record, ooBase will complain, as it should, alerting you to the fact that you are trying to enter bad data. ooBase puts it more formally: "Error inserting new record: Integrity constraint violation"... I.e., the integrity of your data would be gone if this record became part of the table. Change the "xxx" to a code that IS present in the CodesSectors table, and the new record will be accepted.


In the example in the tutorial, the Sector Code, which I tried to enter, 'xxx', was not present in the CodesSectors table, which is analogous to your FamilyID.

Hope that helps. Hope someone can show us how to automate the lookup and display of the available family IDs.
Back to top
View user's profile Send private message
vicramz
General User
General User


Joined: 26 Mar 2008
Posts: 14

PostPosted: Tue Apr 01, 2008 1:46 pm    Post subject: Problem solved, sort of Reply with quote

Thanks for the reply. I will look at the article.

I have figured out a way to do a list box on an individual data entry form. Here's how it works:
1) Click on the list box icon, and drag a rectangle into the form (in design mode). The list box will appear, and a wizard will open.
2) Set the "table" to Families", and the display field to Family last name.
3) Then, in the next screen, select FamilyID for both the Family and Individual table.

What this does, then, is create a box that displays all the Family Last Names previously entered via the Family Data Entry Form, and then when one is selected, it puts the FamilyID value from the Families table into the FamilyID field of the Individuals table.
Of course, it's important that these two fields be linked in relationships, a one-to-many relationship between FamilyID in Families and FamilyID in Individuals.

There's even a way to list the families alphabetically, and to display the family names as a combination: LASTNAME, FIRSTNAME. This is done by creating a query, using a stiring function called CONCAT (for concatenation), and then, saving the query as a "view", which puts it into the Tables lists and makes it available as a selection for the List Box.

Here's a post that I found which helped me figure this all out. http://www.oooforum.org/forum/viewtopic.phtml?t=25060

About two thirds of the way down, there's an instruction on how to create a listbox. Only thing was that I couldn't figure out how to type the double bar, so I had to do a search for the CONCAT function and used that in the wizard instead.

Hope all this helps with your issue. I'll look at that article tonight.

P.S. Still don't know how to make the subform advance, as described in my first post.
_________________
vicramz
Back to top
View user's profile Send private message
MSPhobe
Super User
Super User


Joined: 29 Sep 2005
Posts: 529
Location: England

PostPosted: Wed Apr 02, 2008 4:23 am    Post subject: Reply with quote

CONCAT....

a) It can only have two arguements, so...

CONCAT("FirstName","FamilyName") ok,
or....
CONCAT("FirstName",CONCAT("MiddleName","FamilyName"))...

Clumsy, but works.

To get Smith, George:

CONCAT("FirstName",CONCAT(', ',"FamilyName"))...

... N.B.: APOSTROPHY around comma space

===
|| alternative....

a) That's two keystrokes, as in XX. The "|" key, on my US keyboard, is a shifted \, just above the Enter key. I couldn't find it when my keyboard was set (Windows international settings) as a UK keyboard.

b) The || alternative is only available, I believe, to users of ooBase 2.4 and higher.

Would allow Smith, George, with....

"FirstName" || ', ' || "LastName"

... I think. In principle. (I don't have ooBase 2.4 on this computer to check I've got it exactly right. Sorry!)
Back to top
View user's profile Send private message
vicramz
General User
General User


Joined: 26 Mar 2008
Posts: 14

PostPosted: Wed Apr 02, 2008 7:37 pm    Post subject: Reply with quote

I did the first thing, with the CONCAT function, and it worked perfectly. Thanks.
_________________
vicramz
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group