returning an ID from the last record inserted with query

[jawilli1]

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

 

[modest]

Why don't you just make a new SELECT query and set the WHERE statement = to the values of the new student?


SELECT [Student_ID] FROM [TABLE NAME]
WHERE [STUDENT FIRST NAME]="Curt" AND [STUDENT LAST NAME]="Reynolds"

 

[jawilli1]

how would I store that returned value?
would it be something like this:

myVariable = SELECT Student_ID FROM ......?

 

[modest]

Use VBA to do this. At least, I would.

Just upload your database. This takes 1 sec to do, but I'm going to bed.