Inserting data into multiple tables in one sql statement - followon

Hi guys,

I see from the net that i'm not the first, nor will be the last who get scuppered by this nightmare scenario.

I currently am developing asp pages to update/insert records into an access 2000 database.

One of the forms requires inserting info into 3 tables within the same database.

I've kinda figured that I need to do this with individual insert statements, but im kind of stuck as to know how to let the main table (user) that the recently inserted next of kin (nok) and machine id (machine) relate to it?

Is there a simple method - i.e. editing the join properties, or is it going to be complicated as hell?

My current statement is as follows (and is not liked one bit by access!!):

strSQL3="INSERT INTO USER (F_NAME,S_NAME,DOB,INDUCTED,EYES_TESTED,NOKID,MID) VALUES "
strSQL3=strSQL3 & "('" & fname & "','" & sname & "','" & dob &"','" & inducted & "','" & eyes_tested & "',"
strSQL3=strSQL3 & "SELECT NOKID FROM NOK WHERE "
strSQL3=strSQL3 & "NOK_F_NAME = '" & nok_fname & "' AND NOK_S_NAME = '" & nok_sname & "' AND "
strSQL3=strSQL3 & "NOK_ADD_POSTCODE = '" & nok_postcode & " ',"
strSQL3=strSQL3 & " SELECT MID FROM MACHINE WHERE "
strSQL3=strSQL3 & "M_NAME='" & username & "' AND M_WORKGROUP='" & computername & "' AND "
strSQL3=strSQL3 & "M_DOMAIN='" & userdomain &"')"

Can anyone suggest anything to help me on my way (be it sql based, or asp based)? My particular line of thinking would be to some how do select statements to find out the corresponding pk ID's from nok and machine tables, and then just simply put them into my insert statement. Unfortunately, my asp is not that good.

Probably the easiest method that I can think of, but need your help for the code.

Thanks


Chris