Access VBA code needed to input an invoice number starting at... and then...

[Lidsavr]

I am running Access 2010.

I think I am pretty adept at coding in Excel, but I am having trouble understanding how to make my Access invoice database that I wrote for my business automatically assign an invoice number and increment by one on every following invoice.

I have done a lot of reading and even searching of this forum and the best I can come up with is that I need to create a macro to perform this task. Access Macros look a little different than Excel Macros, even though the language is similar, so I do not know where to start, or get the information that I need.

Will someone please give me direction to the information or provide sample code that I can work from? Any help is appreciated!

Thanks,

Charles (Lidsavr)

 

[SydneyGeek]

Here's one way to do it.
Normally I'd just use an Autonumber but if you cancel an order, you end up skipping Invoice numbers.
This code goes into the Current event of your Invoice form. Adjust to suit the name of your invoice number field and table.

Private Sub Form_Current()
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim lngLastInv As Long
    If Me.NewRecord Then
       'Find the last invoice number and increment by 1
        Set qdf = CurrentDb.CreateQueryDef("", "SELECT Max([invoiceNo]) as LastInv FROM tblInvoice")
        Set rst = qdf.OpenRecordset
        rst.MoveLast
        lngLastInv = rst!lastinv
        
        Set rst = Nothing
        Set qdf = Nothing
        
        'update the InvoiceNo field
        Me.InvoiceNo.SetFocus
        Me.InvoiceNo = lngLastInv + 1
    End If
End Sub

Denis

 

[strive4peace]

Hi Charles

Denis, hope you don't mind if I add a few comments to what you wrote ...

object variables should be released at the end, ie:

on error resume next
set qdf = nothing
if not rst is nothing then
rst.close 'it was Opened, it needs to be closed
set rst = nothing
end if

Even though CurrentDb is only used once, imo, it is best to set a db object variable and release it

set db = CurrentDb 'or DBEngine(0)(0) but maybe not in this case since you would have to refresh it
' statements
set db = nothing

Also, I would NOT put this on the Current event ... make sure all values are filled in existing records and then use the form BeforeUpdate* event

* you can use the form BeforeInsert event for new records but that happens when the record is first created (and another user may be entering a record too) whereas BeforeUpdate happens just before it is saved

you also do not have to create a querydef

   on error goto Proc_Err
   if not isnull(Me.InvoiceNo) then exit sub 'already has a value

   dim db as dao.database _
      , rst as dao.recordset

   dim sSQL as string

   set db = CurrentDb

   sSQL = "SELECT Max([invoiceNo]) as LastInv FROM tblInvoice"
   set rst = db.OpenRecordset(sSQL, dbOpenSnapshot)

   with rst
      if .eof then
         Me.InvoiceNo = 1
      else
         Me.InvoiceNo = !LastInv + 1
      end if
   end with

Proc_Exit:
   on  error resume next
   if not rst is nothing then
      rst.close 'it was Opened, it needs to be closed
      set rst = nothing
   end if
   set db = nothing

Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   form BeforeUpdate " & Me.Name

   Resume Proc_Exit
   Resume

 

[SydneyGeek]

Hi Crystal

Thanks for picking the error on releasing the variables.
i generally have that code in by default, but forgot this time around -- in a hurry.

And I get your point about using the BeforeUpdate event but found for one system I built that the client needed an invoice number to be able to complete the rest of the order (with records in a subform) so I used the Current event. If a subform is not involved, BeforeUpdate would be the preferred event to use.

As a matter of interest why do you prefer BeforeInsert to Current? Is it because Current fires every time you select the record?

Denis

 

[strive4peace]

Hi Denis,

you're welcome

the Current event can fire multiple times ... I don't use it when BeforeUpdate or BeforeInsert works

With both Current and BeforeInsert, you have the same problem -- once a value is determined, if the record is not saved right away, another user may get the SAME value for another record.

BeforeUpdate is the most reliable place for this calculation. This means the user won't see the value till they are done filling everything out.

 

[Lidsavr]

Denis and Crystal,
Thank you for your help! The code and your instructions were a great help in getting past the last obstacle in completing my invoice program. I only need to import my past data and then I will be ready to start using it!

I apologize for the late reply; I needed to step away from the business for a few days before the holiday rush.

Again, thank you. Have a happy holiday season!

Charles

 

[strive4peace]

you're welcome, Charles happy to help

Merry Christmas ...Hanuukah, Kwanzaa, ...
Happy New Year