How do I add data via a form to linked tables? (Access 2000)

[ChrisGreaves]

How do I add data via a form, to the query on which the form is based when the query is a set of linked tables in a relational database?
I am feeling quite pleased with my progress on a simple relational database, but have now arrived at a metaphysical log jam, and seek counseling.

Cont_015.JPG

Back in Basic Access2000 question on linked tables I received a great boost in learning of the power of the SQL-approach in populating list boxes; I suspect a similar heavy-lifting tool is available to me now, but can’t work out how to locate it, or even ask about it.

My queries and the form are set up. The form delivers me a screen with details of a single person, the branch they belong to, and the company of that branch. (A company can have several branches, distinguished by one address, a person can be based in only one branch of a company).

My old way of thinking to add a new record would be to determine from the user whether a new Company/Branch/Person was being added to the world, or whether a new Branch/Person was being added to an existing Company, or whether a new Person was being added to an existing Branch.
The nature of the database (contact management) is that about 50% of the time I’ll be adding a new Company/Branch/Person, but about 50% of the time I’ll be adding a new person to an existing pool of people in a Company’s Branch location.

I envisage combo boxes for the company and address fields, so that the user can select an existing company and/or branch, after which the personal details can be keyed in. That is my old “VBA” approach where I micro-manage every key and every string.

My experience in Basic Access2000 question on linked tables makes me think that there is possibly a better, non-FORTRAN-looping way of doing this, but although I’ve scoured the web I can’t find any articles that spell it out in basics.

Thanks in advance for any hints or pointers. I'm having fun!

[HansV]

The "standard" approach would be:
- A form based on the Company table.
- A subform of this form based on the Branch table, linked to the main form on CompanyId.
- A subform of the Branch subform based on the Person table, linked to the Branch subform on BranchId.

The user would first navigate to the relevant company or create a new company record, then navigate to the relevant branch within that company or create a new branch record, and finally navigate to the relevant person within that branch or create a new person record.

Another approach would be a form based on your query, with a combo box for the Company and a combo box for the Branch. You can use the On Not In List event of those combo boxes to pop up a form in which the user can enter details of a new Company or Branch if they enter a non-existing name in the combo box. But this requires quite a bit of advanced tweaking to make it user-friendly.

[ChrisGreaves]

Thanks for this astute-as-usual response Hans. I am comforted to learn that i was toddling along the right track.

The "standard" approach would be: - A form based on the Company table. ...

I don't like this too much because it means the user has to do a lot of clicking/keying compared to the other method.

... a form based on your query, with a combo box for the Company and a combo box for the Branch.

Now this I like, because I can use the existing form and allow the user to do what seems most natural to them. I think I was almost here with "I envisage combo boxes for the company and address fields, so that the user can select an existing company and/or branch, after which the personal details can be keyed in."

... the On Not In List event of those combo boxes ...

... and this was my missing chunk of knowledge.

I just played with it a few minutes ago and it does the trick.
If the user makes a change to any field other than a key field (Company, Address) then they are merely editing the contents of the "record".
If the user makes a change to the Company field then they are creating a new Company; if they change the Address field they are creating a new Branch.

For example, when I learn that a new employee Gretel is in the German branch, while I am looking at Hans's (Netherlands branch) record, I enter the German address, which creates a new German branch of the company with a duplicate employee Hans (we wish!) and then I change "Hans" to "Gretel" to leave me with a duplicate of Hans's record, but with Gretel's name and address.

Hooray!