By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,804 Members | 1,631 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,804 IT Pros & Developers. It's quick & easy.

How to automatically create a record in a table when one is created in another?

P: 9
Hi Guys

Here's a quandry I could do with some help on please...

{I'm using Access 2002 SP3 on XP SP2}

I have 2 tables [Customers] and [Orders]. They're linked via a 1-to-1 relationship on field [CustomerID].

I want each and every customer to have 1 (and only 1 ; trust me this intentional) Order record, and would like to have this Order record automatically generated when a new Customer record is saved.

ie. user adds a new Customer record; system then automatically generates a new Order record associated with this new customer.

How can this be achieved programmatically?

Any help assistance appreciated, and my apols if this so simple a trained monkey could do it blindfolded ;-)

Rich
Aug 2 '07 #1
Share this Question
Share on Google+
4 Replies


JConsulting
Expert 100+
P: 603
Hi Guys

Here's a quandry I could do with some help on please...

{I'm using Access 2002 SP3 on XP SP2}

I have 2 tables [Customers] and [Orders]. They're linked via a 1-to-1 relationship on field [CustomerID].

I want each and every customer to have 1 (and only 1 ; trust me this intentional) Order record, and would like to have this Order record automatically generated when a new Customer record is saved.

ie. user adds a new Customer record; system then automatically generates a new Order record associated with this new customer.

How can this be achieved programmatically?

Any help assistance appreciated, and my apols if this so simple a trained monkey could do it blindfolded ;-)

Rich

There's not quite enough information to give you an intelligent answer here. Although you mention that you have your tables linked by CustomerID, you didn't mention whether these IDs in their respective tables were Autonumber or Foreign Keys associated with a "master" table.

My first thought is to tell you to use a simple insert statement, but you didn't mention what kind of data or what fields would be inserted.

So guessing here....Using the After_Update event of your form.

Expand|Select|Wrap|Line Numbers
  1. currentdb.execute "Insert into Orders (field1, field2, field3) values (" & me.field1 & "," & me.field2 & "," & me.field3 & ");"
  2.  
this will take values from fields on your form, and insert them into the fields in the table.

This will NOT work if you're trying to insert into an autonumber field. So if you're storing the CustomerID in the ORders table as an autonumber, you won't be able to do this.

J
Aug 5 '07 #2

missinglinq
Expert 2.5K+
P: 3,532
Maybe I'm confused, but if each Customer is going to have one and only one Order Record, then logically each Record Order will have one and only one Customer. If this is the case, why would you need two tables?

Linq ;0)>
Aug 5 '07 #3

P: 9
Thanks J.

The CustomerID field is text and the OrderID field is an Autonumber type.

Thanks for your suggestion below: How can I perform this operation with the Autonumber field, please?

Many thanks,

Rich

There's not quite enough information to give you an intelligent answer here. Although you mention that you have your tables linked by CustomerID, you didn't mention whether these IDs in their respective tables were Autonumber or Foreign Keys associated with a "master" table.

My first thought is to tell you to use a simple insert statement, but you didn't mention what kind of data or what fields would be inserted.

So guessing here....Using the After_Update event of your form.

Expand|Select|Wrap|Line Numbers
  1. currentdb.execute "Insert into Orders (field1, field2, field3) values (" & me.field1 & "," & me.field2 & "," & me.field3 & ");"
  2.  
this will take values from fields on your form, and insert them into the fields in the table.

This will NOT work if you're trying to insert into an autonumber field. So if you're storing the CustomerID in the ORders table as an autonumber, you won't be able to do this.

J
Aug 12 '07 #4

JConsulting
Expert 100+
P: 603
Thanks J.

The CustomerID field is text and the OrderID field is an Autonumber type.

Thanks for your suggestion below: How can I perform this operation with the Autonumber field, please?

Many thanks,

Rich
I guess I'm with Linq on this one. If there's no one to many relationship, then put this in one table.
Aug 12 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.