How to get the last record id of a form?

Question

I currently have a form in access.

What I want to do is get the value of the last record added.

For example, if i have 10 records, I want to get the value "10", because this is the id of the added last record. I am trying to run a query with the function last id inserted() but it is not working.

This the code I am using :

Dim lastID As Integer
Query = "select last_insert_id()"
lastID = Query
MsgBox (lastID)

What am I missing?

Answer 1

Following on from the last comments, here's a piece of code I used recently to turn the last ID value of a record set into variable for use in VBA. It's not great, however, because I still can't work out how to turn the record's ID field value directly into a variable. Instead I used the inelegant solution of copying the record set into an excel workbook, and then setting the variable value to the value of the cell I just copied into.

EDIT: Worked out how to turn the ID into a simple variable: new code at end

This is all run from a single client workbook:

Option Explicit
Public AftUpD As Long
Public BfrUpD As Long

Sub AssignLstRowAftUpD2()

    Dim dbPP As DAO.Database
    Dim ResTemp As DAO.Recordset
    Dim z As Long
    Dim SelectLast As String

    SelectLast = "SELECT Max(Table1.ID) AS MaxOfID FROM Table1"
    'Debug.Print SelectLast

    Set dbPP = OpenDatabase("C:\filepath\Database11.mdb")
    Set ResTemp = dbPP.OpenRecordset(SelectLast)

    If ResTemp.EOF Then
            GoTo EndLoop
        End If

    Worksheets("Diagnostics").Visible = True
    Worksheets("Diagnostics").Range("C4").CopyFromRecordset ResTemp
    z = Sheets("Diagnostics").Range("C4").Value
    Sheets("Diagnostics").Visible = False
    AftUpD = z
    'Debug.Print AftUpD

EndLoop:
        ResTemp.Close
        dbPP.Close



    Set dbPP = Nothing
    Set ResTemp = Nothing
    'Set SelectionLast = Nothing
    'z = Nothing

End Sub

Then I used this value as a variable to make a new SQL query:

Sub Query()
   'This query uses the highest ID value in a companion spreadsheet (the public  
   'variable BfrUpD), which is set in a sub I haven't posted here, to find out 
   'how many records have been added to the database since the last time the
   'spreadsheet was updated, and then copies the new records into the workbook

   'Be warned: If you run this query when BfrUpD is equal to or greater than AftUpD it 
   'will cause a crash. In the end user version of this, I use several If tests,
   'comparing BfrUpD with other public variables, to make sure that this doesn't
   'happen.
    Dim WBout As Excel.Workbook, WSout As Excel.Worksheet
    Dim dbPP1 As DAO.Database
    Dim qryPP1 As DAO.Recordset
    Dim ResTemp1 As DAO.Recordset
    Dim TestValue As String
    Dim strSQL2 As String


    TestValue = BfrUpD
    'Debug.Print TestValue
    strSQL2 = "SELECT * FROM Table1 WHERE (((Table1.ID)>" & TestValue & "))"
    'Debug.Print strSQL2


    Set dbPP1 = OpenDatabase("C:\filepath\Database11.mdb")
    Set qryPP1 = dbPP1.OpenRecordset(strSQL2)

    Set WBout = Workbooks.Open("C:\filepath\h.xlsm")
    Set WSout = WBout.Sheets("sheet1")
    WSout.Range("A1").End(xlDown).Offset(1, 0).CopyFromRecordset qryPP1

    qryPP1.Close
    dbPP1.Close
    WBout.Save
    WBout.Close

    MsgBox "Data copied. Thank you."

Set WBout = Nothing
Set WSout = Nothing
Set dbPP1 = Nothing
Set qryPP1 = Nothing
Set ResTemp1 = Nothing

End Sub

EDIT: Code for getting field value directly into variable

    Dim dbPP As DAO.Database
    Dim ResTemp As DAO.Recordset
    Dim z As Long
    Dim SelectLast As String

    SelectLast = "SELECT Max(Table1.ID) AS MaxOfID FROM Table1"
    'Debug.Print SelectLast

    Set dbPP = OpenDatabase("C:\filepath\Database11.mdb")
    Set ResTemp = dbPP.OpenRecordset(SelectLast)
    z = ResTemp(0) 'specifying it's array location (I think) - there is only one
    'item in this result, so it will always be (0)

    AftUpD = z
    'Debug.Print AftUpD
    ResTemp.Close
    dbPP.Close



Set dbPP = Nothing
Set ResTemp = Nothing
'Set SelectionLast = Nothing
'z = Nothing

End Sub

Answer 2

There is a function DMax that will grab the highest number.

Dim lastID As Integer
lastID = DMax("IDField","YourTable")
' or = DMax("IDField","YourTable","WhenField=Value")
MsgBox lastID

The other Domain functions are:

• DAvg
• DCount
• DFirst
• DLast
• DLookup
• DMin
• DStDev
• DStDevP
• DSum
• DVar
• DVarP

Check with your friendly F1 key for more info

Answer 3

What you would do is set up and save a query that gets the value for you first. Call it MaxID

e.g

SELECT Max(ID) as result FROM Your_Table_Name

Then, in your VBA code, set your variable to that

eg.

Dim IDresult As Integer
IDresult = DLookup("[result]", "MaxID")
MsgBox(IDresult)