Use one form for data entry into multiple tables

[jean]

Hello, I'm pretty new to using forms and VBA. I recently inherited a database that stored all data in a couple of tables, and used a single form for data entry.

I would still like to use a single form with multiple tabs for data entry. However, I tried to restructure the tables to make queries easier, coming up with the following relationships:




The most important structures are a Directory table, a Specs table, Project Directory table, which meet in a Prospects table. The Prospects table exists because a client could have more than one set of specs or projects, and a project could have more than one client associated.

My question is, how can I create a form that allows for easy data entry? For instance,
Tab 1 would input data about a single client into the Directory table. It would also update the Contact_Notes table (a table consisting of just a memo field and the client_id number... I split it off to prevent corruption, but conceptually it is part of the Directory table.)

Tab 2 would create a new entry in Specs with the client's id number, then the user could fill in info about the client's requirements.
Tab 3 would have the client id and specs id already set, and new dropdowns with the lists of available staff and projects, creating a new record in the Prospects table.
Tab 4 would record info about the eventual sales made based on a prospect.

I have already tried embedding a subform, which works more or less, but looks confusing to the end user, even after I removed the navigation bars and most other formatting. I also tried making a big query of all the data out of all the tables and using it as the recordsource. Though that populated the form nicely with existing data, I was unable to update it (probably because the ID numbers were not being set properly.)



Would anyone be able to help me make the embedded subforms look better? Or else there must be a way to use VBA to code an equivalent to "link master fields" for the fields of foreign tables that appear on Tabs 2, 3, and 4.
Looks matter because another person will be doing data entry and working with the database.

Thank you very much for any help you can give!
Jean

[jean]

To make it easy, we could start with the first tab, which is all fields from the Contacts Directory table except for one field, which is the memo from Contact Notes and has a one-to-one relationship with Contacts Directory, keyed with contact_id.

How could I create a form that naturally allows data entry and editing of both tables at once? Right now, unless I use the subform, I get the following error: "Index or primary key cannot contain a Null value."