0

Trying to get the RecieptNumber (autonumber) from the most recent record in the table 'Invoices' to store the value in the variable invoiceNum (integer).

Dim rstInvoices As Recordset

Set cdCurrentDatabase = CurrentDb
Set rstInvoices = cdCurrentDatabase.OpenRecordset("SELECT LAST ([RecieptNumber]) FROM Invoices;")

invoiceNum = rstInvoices("[RecieptNumber]").Value

Started VBA programming yesterday so appreciate any help that I will be able to understand.

Erik A
  • 31,639
  • 12
  • 42
  • 67
Connor Stansfield
  • 69
  • 1
  • 2
  • 11
  • your request : `SELECT LAST ([RecieptNumber]) as rNum FROM Invoices` your vba: `InvoicesinvoiceNum = rstInvoices.Fields("rNum").Value`. see also here : http://msdn.microsoft.com/fr-fr/library/office/ff197799(v=office.15).aspx – scraaappy Dec 10 '14 at 22:57
  • possible duplicate of [How to get the last record id of a form?](http://stackoverflow.com/questions/13587638/how-to-get-the-last-record-id-of-a-form) – LondonRob Dec 10 '14 at 23:02
  • If you are on a multi-user system, anything involving Last, Top etc, is very dangerous, you need an instance of CurrentDb and @@Identity, see http://stackoverflow.com/questions/1628267/autonumber-value-of-last-inserted-row-ms-access-vba – Fionnuala Dec 11 '14 at 01:28

2 Answers2

2

You'll want to do something like:

SELECT TOP 1 RecieptNumber FROM Invoices ORDER BY RecieptNumber DESC

This will order them so the last record is first in the list, and then it takes the first record. Assuming, of course, RecieptNumber is created in numerical order.

And it's bugging me, so I'll add this - it should be ReceiptNumber, not RecieptNumber...

0

The string argument to rstInvoices has to refer to a field that is actually returned by executing the Select statement. But the query does not return a field RecieptNumber, but the Last(RecieptNumber) without a specified name. Therefore you first want to give that aggregated column a name using the AS clause:

SELECT LAST(RecieptNumber) AS LastNumber ...

Now you can refer to that field in VBA:

invoiceNum = rstInvoices("[LastNumber]").Value
paulroho
  • 1,234
  • 1
  • 11
  • 27