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

Answers


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

SELECT Max([ID]) FROM [Table]

Need Your Help

Snap.svg capturing mousewheel events

events mousewheel snap.svg

I would like to take the mousewheel event on an element, but have't found anything on the documentation. Do you have an example of the kind?

Get file version in PowerShell

file powershell versioninfo

How can you get the version information from a .dll or .exe file in PowerShell?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.