Saving data to a table then retrieving the autonumber to save to another table

Question

I am currently trying to retrieve the AutoNumbered value from a table in which I have inserted a bit of data so that I can use the AutoNumber to insert into another table.

I have two tables: One storing Customer Information (CustMaster) and one storing RMA Information (RMAMaster). The CustNbr field from both of these tables is linked via Relationships. When I insert data into the CustMaster table, the CustNbr field is an AutoNumber, incrementing by one. When I insert data into the RMAMaster table, the RMANbr field increments by one as an AutoNumber.

I have a form where the user enters information for a new customer (if the customer is "new", i.e. not in the database) and then clicks a button aptly named "Generate RMA", which saves the New Customer to the CustMaster table (Company Name, Address, City, State, Zip, Country) and also saves the RMA Information to the RMAMaster table (generated date, The initials of the person who generated the RMA, any notes and the CustNbr from the CustMaster table).

If the customer is new, I have to take the AutoNumber CustNbr from the CustMaster entry I made and insert it into the RMAMaster table to link the two, but I have no idea how to do that successfully. I've tried the following code with no success:

DoCmd.RunSQL ("INSERT INTO [CustMaster] (fcompany,fmstreet,fcity,fstate,fzip,fcountry) VALUES ('" & Me.CustName & "','" & Me.CustAddr & "','" & Me.CustCity & "','" & Me.CustState & "','" & Me.CustZip & "','" & Me.CustCountry & "')") 'Insert the customer information into the CustMaster table
RetCustNbr = oConn.Execute("SELECT @@Identity")(0) ' (hopefully) grab the last autonumber from the CustMaster table so that we can insert it into the RMAMaster table
DoCmd.RunSQL ("INSERT INTO [RMAMaster] (Initials,DateIssued,CustNbr,ContactFirst,ContactLast,ContactEmail,Notes) VALUES ('" & Me.Initials & "','" & Today & "','" & RetCustNbr & "','" & Me.ContactFirst & "','" & Me.ContactLast & "','" & Me.ContactEmail & "','" & Me.RMANotes & "')") ' Insert data into the RMAMaster table
RetRMANbr = oConn.Execute("SELECT @@Identity")(0) ' (hopefully) grab the last AutoNumber RMANbr so that I can display it to the user
Me.RMANbr = RetRMANbr ' Set the Retrieved AutoNumber RMANbr from the table and query above and set a field on the form to it so that the user can see it and give it to the customer

I found the "SELECT @@Identity" query from How to retrieve autonumbered field via ADODB but there was no real context in that answer as to how to apply it successfully to my situation.

Using the code above in my OnClick event of my "Generate RMA" gives me an error "Object Required" with no reference as to where the error might be. The data gets saved to the CustMaster table but no new data appears in the RMAMaster table.

I am using Access 2003 on a Windows XP Machine. I have not changed any of my references from default, which lists the DAO object library before the ActiveX Data Objects library.

Can anyone assist as to what I might be missing, or where? Hopefully I'm making some sense.

Thank you in advance.

Answer 1

there are 3 ways to retrieve the last inserted Id

1)SELECT IDENT_CURRENT('Table_Name') Retrieves the last identity

2)insert into Table_Name values('ABC'...); Select @@Identity

3)SELECT SCOPE_IDENTITY() -- Gives the ID of the last inserted row in last Last inserted table

Answer 2

You can call SELECT @@IDENTITY with DAO using code like this:

Option Compare Database
Option Explicit

Sub test()
Dim cdb As DAO.Database, rst As DAO.Recordset, newID As Long
Set cdb = CurrentDb
cdb.Execute "INSERT INTO Users (Email) VALUES (""gord@example.com"")", dbFailOnError
Set rst = cdb.OpenRecordset("SELECT @@IDENTITY", dbOpenSnapshot)
newID = rst(0).Value
rst.Close
Set rst = Nothing
Set cdb = Nothing
Debug.Print newID
End Sub