How To Get The Record # From a DoCmd.RunSQL SQLInsert Stmt

[fbueller]

I have written some code where I insert a new record into a SQL table based on user input on a form if a record does not currently exist for those values. But I then need to find out what the new record number is in the SQL table.

The SQL Insert statement creates a new Notice ID in a table called RM_Notices if no previous record exists for that notice. I have a form control called Me.txtNoticeId. How can I set the value of the form control to the new record created by the previous SQL Insert statement?

 

[fbueller]

Nevermind..figured it out on my own. You can only use RunSql to act upon a table(i.e., INSERT, DELETE, UPDATE). I used the DMax fn() instead. THX!

 

[JerryKlmns]

Sounds like your PK is an autonumber field
Have you seen this faq700-184 ?