Inserting rows into multiple tables

[amcg]

Hello.

I've got a database which has the following general structure:

Customer_Details - stores general customer information, including a customer_ID

Customer_Purchase_Link - stores customer_IDs and purchase_IDs so one can keep track of each customer's purchase history.

Various other tables, all using purchase_ID as a foreign key.

In summary:
/various
[Customer_Details]-[Customer_Purchase_Link]--other
\tables

When a new customer is added to the system, is there an easy way to automatically generate a record in the purchase_ID table as well as corresponding entries in all the other tables?

Similarly, when a new purchase is added to the system and linked to a certain customer, is there an easy way of making entries for this purchase in all the other tables too?

I've designed similar databases before and they work very efficiently apart from this point. In general I've just put code behind the various "add purchase" (or whatever) buttons to add all the records. It occurs to me that there must be an easier way, such as putting code behind a table so that when a record is added to that table, records are added to other tables using the same ID.

Thanks in advance for any help.

 

[RickSpr]

The "code behind a table" concept is called a "trigger". Another, similar way of accomplishing this is a "stored procedure", which is like a function procedure stored in the database and called by your application logic.

However, Jet doesn't support either of these. With Jet databases, putting code behind your Add buttons or in the form's AfterInsert event procedure are the normal ways of doing this.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[joeflorida]

RickSpr,
I'm a total newbie in Access and trying to build my first database. I was interested in your comments about Jet databases; how they couldn't support the "trigger" or "stored procedures." I didn't understand your comments about putting code behind the Add buttons or in the AfterInsert event procedure. What type of code?

I'm trying to understand the "many to many" relationships using a junction table. The problem I'm having is entering data and getting the other table to update. I saw this thread and thought this might be what I'm looking for. But I don't really understand your answer.

Would you mind explaining further? Or point me in the right direction to search faq's or other threads.

I've tried to search "many to many" on these forums, but I don't think I'm putting in the right words.

Any help would be appreciated.
Thanks,
Joe

 

[amcg]

Thanks for the reply. It is unfortunate that the only way is the code behind the button way, but there it is.