Retrieve Autonumber Added

  • Thread starter Thread starter Samantha
  • Start date Start date
S

Samantha

Hi,

I have searched for this question but can't seem to get an answer. I need
to insert a new record to the ROHeader table and many records to the RODetail
table. It is a one to many relationship between the ROHeader and RODetail
tables, respectively, and they are linked by the unique ROHeaderID field
which is an AutoNumber type.
I am inserting the a new record using DAO sql. I can insert the single
record to table ROHeader with no problem. My problem is how do I get the
ROHeaderID (type AutoNumber) that I just inserted into the ROHeader table for
the RODetail table? I am using MsAccess 2000 for frontend and MySQL ver4 for
backend. Any help is very much appreciated!

Here is what I use to insert to the ROHeader table:

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
If (MsgBox("A Route is not assigned to this work order yet. Do you want
to assign it now?", vbYesNo, "Route Not Assigned to Work Order")) = vbYes Then
strSQL = "INSERT INTO ROHeader (WONumber, RouteID, RouteName,
Inactive, DateCreated)"
strSQL = strSQL & "VALUES ('" & Me!cboWO.Column(1) & "',
6,'Router1', 0, #" & Now() & "#);"
MsgBox "strSQL = " & strSQL
db.Execute strSQL, dbFailOnError

Thank you.
 
Back
Top