Add data to a field in a table directly with sql 1

[Epsilon101]

Hi

I have a table called TblAdminDetails with lots of fields the only ones i need to explain are the foreign key in the table which is Customer ID and the primary key AdmCustomerID.

This table is linked to TblLeadInfo via the foreign key as would be expected. TblLeadInfo holds the main customer Info things like the name.

AdmCustomerID holds information that is keyed in later for the customer.

I have a form where you enter in the main customer info that goes into TblLeadInfo and when the new record is saved and the customerID is then created instead of being an autonumber , i want that number to be inputted into the AdmCustomerID table at the same time.

How can i do this?? I was thinking maybe recordsets, or an append sql statement or something but i dont know the correct syntax.

Can someone help please

Thanks to anyone who answers

---------------------------------------
If God is a DJ, Life is the dancefloor, we are the music, then who are the dancers??
.........

Neil

 

[lupins46]

Are there multiple tblAdminDetails records for each tblLeadinfo? It sounds to me like this might be a one-one link in which case you would just make the primary keys the same.

However, in terms of the question you have asked, I assume that AdmCustomerID is an autonumber field and that the primary key in tblleadinfo is also called CustomerID :

Use the AfterInsert event procedure for the tblLeadInfo form and create a new record in the TblAdminDetails table.

Dim dbs As Database
Set dbs = currentdb
dbs.Execute " INSERT INTO TblAdminDetails " _
& "(CustomerID) VALUES (" & me.Customerid & ")"

Set dbs = nothing

 

[Epsilon101]

Thanks ill give that a shot

---------------------------------------
If God is a DJ, Life is the dancefloor, we are the music, then who are the dancers??
.........

Neil

 

[Epsilon101]

what sort of layout would i need on this to do it for 3 tables in the one function.

TblAdminDetails, TblSalesDetails, TblUWDetails all to have their foreign key field (CustomerID) updated with the CustomerID from TblLeadInfo.

So that new records are made in each table, instead of later when data is entered in for the first time, cos im getting an error because of it, but it can be fixed by doing this.

---------------------------------------
If God is a DJ, Life is the dancefloor, we are the music, then who are the dancers??
.........

Neil

 

[lupins46]

You would need to use the three dbs.execute statements as in the previous example. Sounds like you just have to change the table name.

 

[Epsilon101]

Thanks that worked great!!

---------------------------------------
If God is a DJ, Life is the dancefloor, we are the music, then who are the dancers??
.........

Neil