Search code examples
sqlms-accessvbaautonumber

Getting the most recent autonumber field from another table

I have 2 tables:

tblInvoice (InvoiceID (pk), Invoice)

tblLineItem (LineItemID (pk), InvoiceID)

I have 2 forms "Main Menu" and "Shop." On the "Main Menu" form, when you click the btnNext, it will generate the autonumber for InvoiceID in tblInvoice, and go to the "Shop" form. On the "Shop" form, I have a textbox called txtLineItem, where I input the line item and when I click the btnRecord, I want it to use the most recent InvoiceID in tblInvoice. I can't get the btnRecord to work and I am not quite sure how to get the most recent autonumber in tblInvoice. Any help or advice would be great. Thanks in advance.

Here is my code for btnRecord:

Private Sub btnRecord_Click()
    DoCmd.SetWarnings False
    Set Recordset = CurrentDb.OpenRecordset(Invoice)
    InvoiceID = CLng(Recordset(InvoiceID))

    DoCOmd.RunSQL "INSERT INTO [tblLineItem] (InvoiceID) VALUES (' & Now(InvoiceID) & ')"
    DoCmd.RunSQL "INSERT INTO [tblLineItem] (LineItemID) VALUES ('" & txtLineItem & "')"
    DoCmd.SetWarnings True
End Sub

I have also provided the database in the following link: https://drive.google.com/file/d/0Bye-M8FI1tRUdHU3QkxsUFhNNnc/view?usp=sharing

Solution

  • There is a MAX function that you can use. Assuming the field with the autonumbers is named ID:

    SELECT Max([ID]) FROM [Table]