I have a form that gets employee information and I want to enter that information into 2 seperate tables.  The name, email, password, etc goes into the employees table and the job title and picture go into the supervisors table.  When the info is inserted into the employees table an empID is generated by an autonumber.  I want to insert the empID from the employees table and the job title and sup picture into the supervisors table and can't get it to work.  Below is what I have to enter the info into the employees table.  This part works.

strSQL = "SELECT employees.empFName, employees.empLName, employees.empWkPhone, employees.empHPhone, employees.empEmail, employees.username, employees.password, employees.groupID, employees.venueID FROM employees"
 
   'set cursor type so we can move through the recordset
   rsSchedules.CursorType=2
 
   'lock the recordset
   rsSchedules.LockType=3
 
   'open connection
   rsSchedules.Open strSQL, adoCon
 
   'Tell the recordset we are adding a new record
   rsSchedules.AddNew
 
   'Add records to the recordset
   rsSchedules.Fields("empFName") = request.Form("Fname")
   rsSchedules.Fields("empLName") = request.Form("Lname")
   rsSchedules.Fields("empHPhone") = request.Form("Hphone")
   rsSchedules.Fields("empWkPhone") = request.Form("wKPhone")
   rsSchedules.Fields("empEmail") = request.Form("email")
   rsSchedules.Fields("username") = request.Form("username")
   rsSchedules.Fields("password") = request.Form("pass")
   rsSchedules.Fields("groupID") = 2
   rsSchedules.Update
   rsSchedules.Requery

Now I can't figure out how to find out what the empID is and then insert it and the other into into the supervisors table.  I would appreciate any help.

Thanks,

Judo2000

AHHHH!!!!!

first of all... you can clean this all up and put it into the SQL statement.

try something like this:

set nocount on
insert into tbl_A
(item1,item2) VALUES (value1,value2)

at this point there's a magical variable called @@Identity floating around. (the IDentity field of the thing you just put in) so...

set nocount off
insert into tbl_B
(item1,item2) VALUES (@@Identity,value2)

see how that works?

You will need to build the SQL statement to incorperate BOTH of those queries in the same statement. otherwise somehting could come around and snag that other identity before the second transaction fires (HIGHLY unlikely, but could happen) (oh and that nocount thing is there to keep the statement from returning "rows effected" into the second statement.)

Thanks for the advise, but I'm not really sure how to incorporate that into what I already have or to build the SQL statement to incorporate both inserts?  When I  tried it with the set nocount on it gave me an error saying it need = and with the = it still gave an error.  Anny additional help would be greatly appreciated.

Thanks,

i think these two are the unique in your table.
   rsSchedules.Fields("empEmail") = request.Form("email")
   rsSchedules.Fields("username") = request.Form("username")
so after adding data in the first table make a SELECT query like

strSQL = "SELECT Emp_id from emp where employees.username='username posted by user in the form' and employees.password='password posted by the user'";

it will give you the Id of the person who signups then
make an insert query and add there designations to the superior table.

make sure you have a field which have a uniqueness. for example the email address is the unique and the phone number too.
you know what i mean.

That makes sense but how can I write that sql statement?

strSQL = "SELECT empID FROM employees WHERE username='request.Form("username")'"  ?

Would that work?

Thanks