Using a list-box to limit entries in a foreign key field

chrissekely · General User Joined: 02 Jun 2009 Posts: 6

I am still using Open Office version 3.0.1 (I plan to update it soon) on Windows Vista. Just providing standard info here, though it probably won't help much.

This is our problem:

We are presently developing the data entry forms for three of the tables in a larger relational database. We have a table of drivers, a table of customers, and a table of persons. Fields unique to a driver (such as their hire date and company assigned driver number) are stored in 'drivers'. Similarly, fields unique to customers are stored in 'customers'. All common data (name, address, and such) is stored in 'persons'. In fact, 'persons' then links to 'phone_numbers', allowing each individual to have more than one phone number. With this structure, one person could be both a driver and a customer. This does occur. Storing this info this way is also more space efficient. The problem we've encountered is that two or more different records in the 'drivers' (or 'customers') table may refer to the same person. I do not believe Base provides a way to enforce a one to one relationship, effectively limiting a foreign key field to only unique entries (no duplicates).

We are approaching a solution to this problem, but are encountering some trouble. We are attempting to prevent this problem on the form level, by providing a list or combo box that limits the possible options that a user may choose. The list of options the user may choose would be populated from a query which compares the contents of two fields. 'ID' is the primary key field of 'persons' and 'Person_ID' is the foreign key field in 'Drivers' that links to 'ID' in 'Persons'. Using MySQL, our query looks like this:

iron-hands · General User Joined: 06 Jan 2009 Posts: 13

the problem, if i'm understanding correctly, is that you're unable to keep previously selected values when the listbox is changed? If that is the case, can you not, in your "on changed" event, store the value in a temp variable, refresh the listbox, then have it reselect(or add the item to the box if it doesn't exist)?

chrissekely · General User Joined: 02 Jun 2009 Posts: 6

Thank you. However, regarding a lot of this, we still feel like novices. Macros, VBA, Php, and the like are still a bit beyond the sort of thing we are willing to tackle at this point. I am wondering if there would be a solution that would only involve SQL and the OO Base interface.

RPG · Posted: Mon Jun 08, 2009 12:20 am Post subject:

Hello

If you want avoid macros then you have to understand the possibilities of the forms. Try to use the forms as a filter.

This link can maybe have information what can help you. I knew it use some macros but they are easy. But when you want avoid macros then you must use better the forms.

Romke