Forums

This topic is locked

Retrieving ID Field after insert into Access DB

Posted 23 May 2002 13:17:24
1
has voted
23 May 2002 13:17:24 Saeed Abdi posted:
Please help before I loose my sanity,


I have two forms that should insert a record into two different tables, the first form is for customer details, second form is for that customer's circuits, so what I would like to happen is when someone submits the first form with customer details that would be inserted into table one and then forwarded to the second form so they can insert that customers circuit details into the 2nd table, this is where I'm running into problems when first form is submitted and forwarded to the 2nd page, this page doesn't know anything about the customer since the 1st page didn't submit any values to the 2nd page, what I want to do is to grab the autonumber of the newly created customer details (CustID) and pass it to the 2nd page so the second form will insert the circuit details into table 2 with the CustID in a hidden field.


----------------------------------------------------------------------------------------
Found this solution but I'm not sure which bit of the code I need and where to put it in my page which is created with UD. With my limited understanding I think I only need the last few line but I'm not sure where to put them.

'Create the Connection object
set conn = server.createobject("adodb.connection"
conn.open strconn

'Create the recordset object
set rs = server.createobject("adodb.recordset"
'This statement opens the table so we can add a record notice the addnew
'The 2, 2 is how the table is opened there are many ways it can be opened
rs.open "tblAdoAdd", conn, 2, 2
'Use the addnew method of the recordset object to add a record
rs.addnew

'Set the table column = to my input text box from my form
rs("FirstName" = request("FirstName"
rs("LastName" = request("LastName"
rs("FavoriteColor" = request("FavoriteColor"
rs.update

'I do a movelast here to get the ID that is automatically generated
'I also set the value to a local variable so I can write out to the database

<b>rs.movelast
strID = rs("ID"
rs.close
Set rs = nothing
conn.close
Set conn = nothing</b>


Table Structure
----------------------------------------------------------------------------------------
Table 1
CustID|CompanyName

Table 2
CctID|CustID|CircuitNo|FibernetRef|ServiceType|POP|CircuitTailed|CctNotes|Bearer|Ports

Info
----------------------------------------------------------------------------------------
Database is access 2000 with access driver
ASP

Reply to this topic