Return auto id from access database after a record is added through a form

Question

TIA:

I have an Access database table with autoid field.  I have a form that submits first and last name to the table in the database.  I'm trying to send a confirmation page that returns the submitted first and last name AND the auto id generated by Access.  My approach has been generate a custom confirmation page where I have entered the confirmation fields (first and last name that comes from the submit form) and a results query reqion to query first and last name for the id generated when the record was added.  I just can't figure out how to set up the names as variables to use in the sql for the query.  Thanks for any help.  Joel

Answer 1

It would depend on how you enter the record, but generally, if you use:

lngPK = DMAX("[pk]","tablename")

where pk is the name of your autonumber field, that should return the last PK entered. Assuming you do this immediately after inserting the record in the table.

---

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2009 Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

Answer 2

Joel:

There are several ways to accomplish this.  Below is a procedure for directly querying for the ID value.  The normal precautions apply here for using name as the basis for a query.  For example, what happens if you have two women named Jenny Doe in your database?  In this case you might want to add other identifying criteria (date of birth, SSN), or maybe use a MAX query to get the largest ID value.

You could also create a parameter query and then use a QueryDef object to query for the result.  Since this is a simple example I just chose to use a dynamic SQL statement.

Public Function ReturnAutoID(sLastName As String, sFirstName As String) As Long

    Dim sSQL As String

    Dim rs As DAO.Recordset

    sSQL = "SELECT [ID] as MyID FROM MyTableName WHERE LastName='" & sLastName & "' AND FirstName='" & sFirstName & "'"

    Set rs = CurrentDb.OpenRecordset(sSQL)

    ReturnAutoID = Nz(rs("MyID"), -1)   'Return -1 if ID does not exist for some unknown reason

End Function

David Lloyd

Lemington Consulting

http://lemingtonit.com