VBA Code to Input Information between Tables

[SWG]

Hello,



I have attached a picture to illustrate my needs but I will provide some back ground first. I have 4 tables; one of which I call the [Master] Table, it contains information such as the item's name and a given ID (auto-number). The other tables contain information such as descriptions and a 'Program' ID (auto-number).

The 3 'Program' tables may be linked via the [Master].ID if there is a relation. Currently in my form [Master Details] I ask a simple YES or NO question to see if there is a relation. (see picture) If there is a YES specified in my Master Table for one of the Programs I manually go into the appropriate program table and add the [Master].ID into a new row creating a new Program ID for that item.

What I need is this [Master].ID to be generated automatically if YES is selected.

In the jpeg it shows the Master table at the top. Below this is my form where we create new items and select the appropriate linkages. Below this is one of my program tables [Scribe] where I input the [Master].ID and create a new row with a new Scribe ID. I would like the VBA to start when the Save and New button is clicked in the form.

Thanks.

[June7]

Why do you have 3 'Program' tables? Are the fields identical?

Use form/subform arrangement. Master/Child links properties of subform container control will automate saving the MasterID to the child table. No VBA code necessary. Nor are the Yes/No fields.

[SWG]

Ill try a different example say table 1 has client name (first and last)

ID First Last
1 Steve Dude
2 Jane Gal

Then we have 3 tables for addresses, each table is for a different country, and each person may have multiple addresses within one country as well.
Each of those are given their own ID so that we can decern them from others. What I don't want to have to do is always type in the Parent ID on the other tables in order to pull up the respective address information. The forms in this case would be enter with 1) your name. 2) Then choose the country and 3) enter address (es). Close and Save.

The thing is we had an existing client and address list that was in serious disarray and lacked the connections. So I'm having a hard time with is making parent child connections with multiple countries involved. If it were one country it would be a lot easier.

Each table has slightly different requirements that need to be met.

[June7]

Think I would have one table for Addresses. The country is just another attribute for the address and can be used to filter records by country. If each address record is specific to a single client but each client can have multiple addresses, include a foreign key field for the client ID.

[SWG]

Yah i thought about it but I get a ton of empty fields when my columns don't have information pertaining to that particular table. Example I may have ZIP for the USA and a second column Postal Code for Canada. Not to mention That database I have currently has over a thousand records if I were to compile them, making it hard for me. I would need to run huge queries to get out my needed data. We did this once and it stalled the application.