I think I may have missed something. Allow me to explain.
I am working in Access 2000 and have a DB that was given to me to fix that is rather flat.
The DB has many clients and those clients have work places. Many clients have same the same work place but not all do. Seems like a good time to make a new table for work place info, I guess.
Here is the problem. I can do most anything I need with existing information with out too much trouble. However, what happens when I need to add a new client? (this will happen frequently) I have my Client_Tbl with the field WorkID and my Work_tbl with the key field WorkID and its related to the WorkID for Client_tbl. So if I create a new client that does not already have its work place in Work_Tbl. How do I make it so that the new work place is entered in its table with a workID and the client is entered in its table with the correct corresponding workID ?
I am entering clients from a form Client_Frm. I hope this question makes sense.
The most straightforward solution I see is to ask the user to pick from a list of existing workplaces when entering a new client, there would be an option to add a new workplace if the workplaces is not in the list. That way a client could never be entered unless their workplace was first entered.
I agree with Jeremy, Place a combobox in your Client_Form and have it list your work places.....you can then use the NOT-IN-List procedure to update the Work-Tbl if not listed. This way your users will always have to enter a work place before they enter a client. there is a simple code to ADD-too a combobox if you need one. Set your tab-order on your Client_Form to start on the Combobox with the work places listed.
Hope this helps.....