transfer a value from one table to another

[slider]

Hey all!

Im creating a site where users can sign up ect. I have a relational database where im using the fields USER_ID as Key fields. The tables Details and Users are linked by this....The key is an auto number btw.

When i use the sign up form i get an error stating that the data is required in the Details table. From what i can make out of it, the Key field from Users is not going over to the new record in the Details table.

I am using two sql statements to write the data to the Access Database.

Just a note, when the tables wernt linked it worked fine.

This is the first SQL statement

sSQLB = "INSERT into Users (Email, Pass) values ('" & _
Email & "', '" & Pass & "')"

Here is the second one which dosnt work due to the primary key issue

sSQL = "INSERT into Details (NickName, FirstName, Surname, Email, Country, DOB) values ('" & _
NickName & "', '" & FirstName & "', '" & Surname & "', '" & Email & "', '" & Country & "', '" & DOB & "')"

I will state it again. I can write to both tables when they are not linked.
The first SQL statement is working fine and writing the values.

So pretty much, i need to get that primary key from the Users table into the Details table.

Thanks in advance

[Purple]

Hi,

I am a little confused on the layout of your two tables and the RI links you have put inplace - however, can I suggest it may be cleaner, more efficient and safer to do this in a stored procedure where you can ensure you don't create an item in one table without the insert into the second..

Using a stored procedure you can select the autoincremented key value on the first table and use it to insert into the details section in one piece of code.. rolling the insert back if anything fails

Post back if you need a hand with it - also post the SQL statements to define the tables and RI links.

Regards Purple

[debasisdas]

As per the rule of RDBMS , in case of a MASTER-DETAIL relation ship to insert a record in the detail table data must be there in the master table.
U can easily do the same in a procedure . first insert to master then to detail. Hope that solves yopur peoblem.

Next time post with your detail table structure and relations and what/how u have tried to solve this.

Only then the experts here in the forum could help you.

[slider]

Hey thanks for your help! Ive got it all working now..all i did was write the first SQL statement, use a record set to get the data out of the table and then run another sql statement to insert it into the other table!