Add record to multiple tables in MS Access

[CVesta81]

Hi!

I have an Access database with three main tables, "tbl1Customers (MASTER)", "tbl2Contacts (MASTER)", and tbl3Addresses (MASTER)". For simplicity purposes, I will refer to these tables as Table1, Table2, and Table3 respectively. I want to be able to add a new record to Table1, and have that record also append to Table2 and Table3, in order to be able to perform data entry in Table2 and Table3. Currently in my form, I cannot perform data entry because when I add a new record in Table1, it does not get added to Table2 and Table3 and I get an error message when I try to do so.

Currently I have a form for specifically creating a new record in Table1. When I close the form (OnClose) I want the other two tables to be appended with the same primary key information. In this case the primary key for each table (1 to many relationship) is a field called, "CustomerNo".

Let me know if you need any further information about my problem. I appreciate your help!

Chris V.

 

[PHV]

I"d use a main form with 2 linked subforms.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CVesta81]

PHV,

Is there any VBA code I would need to use, or just simply set up the two linked forms with the primary keys?

 

[CVesta81]

I can't seem to get this to work. Could you give a little more detail as to how to do it? When I set up the two subforms, they are blank. I'm guessing because the new customer number doesn't exist in the table yet? I'm still confused as to how to get the new customer number in each table.

 

[PHV]

HAve a look at the LinkMasterFields and LinkChildFields properties of the subforms.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CVesta81]

I have done this and everything matches up. When I load the form, the subforms are completely blank, even when I add data to the main form. Is there something else I'm missing?

 

[dhookom]

The question is why do you want to add related records without anything unique to them? When you want a record, you just go to the subform and enter the new record in the related table. The Link Master/Child properties will maintain the primary/foreign key values.

Duane
Hook'D on Access
MS Access MVP

 

[CVesta81]

So here is the issue in a nutshell. I am trying to create a database for data entry. The database has been created but I can't get the data entry part to work. When I try to create a new record utilizing a form linked to Table1, I am fine. The problem is that when I try to update information for Tables 2 & 3, I can't do it because the unique identifier (Customer No and Organization No) do not exist in those tables. I am trying to figure out why this is because I have set up all the relationships in the table to be a 1 to many relationship. (Table1 to Table2 is a 1 to many relationship, and so is Table1 to Table 3). I've not created a database before for data entry that utilizes three different tables. Any thoughts?

 

[dhookom]

You should have a main form with two subforms: one for table2 and one for table3. Set up the Link Master/Child properties as suggested earlier.

If you can't figure this out, come back with your form and subform record sources as well as your table structures.

Duane
Hook'D on Access
MS Access MVP

 

[CVesta81]

There must be something screwed up with my relationships in my database, because the Master/Child properties are correct. I have all forms set for Data Entry but only the main form shows. As an alternative method, is it possible to run an Append Query when I close the form? I would envision the Append Query would create new records in each of the other two tables with the Organization No and Customer No using information from the main form.

 

[dhookom]

I would try to figure out what isn't working with the solution that has been suggested and is used nearly universally. You haven't provided any information about your record sources or other significant stuff.

It is possible to create and run append queries in the on close event of a form.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya CVesta81 . . .

[blue]There must be something screwed up with my relationships in my database ...[/blue]

The thread sure reads screwed up, and until you post what those [blue]relationships[/blue] are we can't help you. Post the [blue]Record Source[/blue] of the form and subforms as well.

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[CVesta81]

Actually, I solved this one onmy own. I used a pass through public variable and then assigned a each table the ID. thanks for your help!