Accessing ID of last record right after you insert it into table

Hi folks,
I have an Append Query (Insert Into...) that I use to insert values into a table from a form. But to have that tie into other tables I need to gather the Student_ID (primary key) that is autogenerated when the record is inserted. How do I do this? I want to store it in a public variable. I've done quite a bit of searching but nothing of any value has turned up. Please help.

What I have on button press:
DoCmd.OpenQuery "Register Camp Students"

After that I need something to gather that newly inserted Student_ID and store it for later use. Please help!

Ciao,
jawilli1

If you run a query to insert a row, there is no way to reliably retrieve the autonumber of the record just inserted when you are running in a multi-user environment. You will need to change your method to use DAO or ADO instead. Then, as soon as the .AddNew method has been executed, the number will be available. You can capture it at that time for later use.

Are you working with an unbound form for some particular reason?

Yes, because binding the forms doesn't give me the versatility that I want with these forms. I'd rather have full control over what goes in and comes out. In a previous forum someone told me to just run another query for it after the fact compareing some values to get the return value that I want. This makes sense, but the only problem is, running the query in the VBA. Look at this if you could.

DoCmd.SetWarnings False
DoCmd.OpenQuery "Register Camp Student"
Dim theSQL, theValue As String
theSQL = "SELECT TOP 1 Student_ID FROM Student_Info WHERE ((Student_Fname=([forms]![Camp_Create_Registration_1]![Student Fname])) AND (Student_Lname=([forms]![Camp_Create_Registration_1]![Student Lname])) AND (Student_DOB=([forms]![Camp_Create_Registration_1]![Student DOB])))"

theValue = DoCmd.RunSQL theSQL

but this isn't working. it keeps telling me that RunSQL needs and SQL statement. So I put the exact SQL statement in and it still gave me that error. Where am I going wrong with running this select statement and retrieving the value it returns (and it will only be one).

Ciao,
Jawilli1

I had a similar problem where I wanted to get some data to populate a combobox off the last few entries. After some research Ive come to the conclusion that there is no reliable way of quering the data table to obtain the last (or last few) entries ( even with autonumbering!)

Microsoft Northwind database shows a way of keeping the data you need in a table and then re-populating it on every record update, and this persists even between database sessions.

Otherwise this forum (thanks seargent) sent me this snippet which will keep a record of the last 5 entries, but only within a single session

that strRecentEntry = Nz(Me.PersID, " ") & "; " & Nz(Me.FullName, " ")
Me.lstRecent.AddItem strRecentEntry
If Me.lstRecent.ListCount > 5 Then
Me.lstRecent.RemoveItem (0)
End If

I hope either way gives you some clue

To retrieve the last inserted autonumber on the same connection, you could try first executing the query on the connection, then retrieve the @@identity:

dim cn as adodb.connection
dim rs as adodb.recordset

set cn=currentproject.connection

cn.execute "insert into.....",,adcmdtext
set rs=cn.execute("select @@identity",,adcmdtext)
msgbox rs.fields(0).value