1

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

Ron T
  • 397
  • 1
  • 4
  • 22

1 Answers1

1

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

SELECT Max([ID]) FROM [Table]
David G
  • 2,315
  • 1
  • 24
  • 39
  • Here is what I have but I can't get it to work:`DoCmd.RunSQL "INSERT INTO [tblLineItem] (LineItemID, InvoiceID) VALUES ('" & txtLineItem & "', 'SELECT Max([InvoiceID]) FROM [tblInvoice]')"` – Ron T Aug 18 '15 at 19:29
  • If you're trying to insert a record where one value is a string and the other comes from another table, I'm not sure what the problem with that statement is. You might want to wait for someone more knowledgeable to answer. EDIT: You could see this as an example: http://stackoverflow.com/questions/5907206/mysql-insert-into-tbl-select-from-another-table-and-some-default-values If what's giving you trouble is writing the string itself, with the right quotes and all that, I suggest you write a string, debug.print it, and when it's good runSQL that string. – David G Aug 18 '15 at 19:52
  • 1
    Thank you, I was actually able to get it to work after researching about the formula you provided! – Ron T Aug 18 '15 at 19:58