Incrementing Invoice number on form - Rookie with code

[clio]

Hello, I would like to add a button to my form that will generate a new invoice number with the year and an incrementing number, that will work across all organisation records (so whichever company I create the invoice for, the same number won't be reused).

I have been through the forum database and found this which I think may be bang on:

Private Sub Command22_Click()
On Error GoTo Err_Command22_Click
DoCmd.GoToRecord , , acNewRec
Dim NextNo As Long
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT NextNum FROM tblnextnum")
NextNo = rs!nextnum + 1
'now update the table
rs.Edit
rs!nextnum = NextNo
rs.Update
rs.Close
Set rs = Nothing
Me.AccountNo = NextNo
Exit_Command22_Click:
Exit Sub

My problem is I'm too much of a Rookie to know how to change this for my database as I don't understand exactly what this code does! Please can someone explain? Thanks

 

[RuralGuy]

You are going to want to use the DMax() function in the BeforeUpdate event of your form and be prepared for duplicates in a multi-user environment and adjust. Search this forum on DMax and you should find many examples.

 

[clio]

Thanks RG. I found this

Have two fields in your table:

DateEntered - Date field, form does not allow entry, Default Value --> Date()

ControlNumber - Long Integer, form does not allow entry, value assigned by the
form's BeforeInsert event.

ControlNumber = Nz(DMax("[ControlNumber]", "YourTable", "[DateEntered] = #" & Date & "#"), 0) + 1

Then whenever you want to display it your QUERY should have a field:

DisplayNumber: DatePart("yyyy", [DateEntered]") & "-" & Format([ControlNumber], "0000")

That gives you the flexibility to "alter" your number later by changing any of its
components:

DateEntered can easily go to YYYYMMDD,
ControlNumber can easily go to 5 or 6 digits,
You could easily add a "revision" suffix

which explains some of it, but still doesn't go all the way....I think it may be a design issue and I'm making some basic mistakes. I've got an organisation table and a Quote/Invoice table. Initially I was just going to increment the numbers on the Quote invoice table. Then I found out that Invoices have to be incremental with none missing. So I am now trying to add an extra 'invoice number' field to the table. The number can't reside incrementally within the table, as the unused quote rows will create missing numbers. Does this make sense to do this without redesigning, or is it bad practice to have quotes and invoices in the same table... apologies if this is really basic.

thanks

 

[gemma-the-husky]

the first solution you tried works by keeping a central table with the next invoice number to be used. when you need a number it temporarily locks this table, retrieves the number for you, and increments the lookup number

the second solution proposed by RG merely looks in your current table for the highest number used, and adds one to it for the new invoice.

either of these methods may be appropriate to your requirements - you need some vba code in any event

the first solution is probably the most secure, as it is easier to lock this table in a multi-user environment, and it is easier to seed that table with a starting point.

in the first case, you may have a problem if the user decides to cancel his invoice entry, as the number will be wasted. this can also happen in the second example, if one user (of several active) decides not to complete the task. One solution is not to retrieve the invoice number until you are sure you want to use it.

 

[clio]

Thanks - that is excellent clarification. Would you mind helping me understand the code in the first solution - I have done a bit of basic copy and paste code writing so could follow and apply an explanation (I hope)

 

[GaryPanic]

thats probably my coding up there - I pinched from one of my guys

0k

Private Sub Command22_Click() - this is a button command
On Error GoTo Err_Command22_Click
DoCmd.GoToRecord , , acNewRec - makes a new record - so for invoicing you probably don't need this bit
Dim NextNo As Long - start of the numbering process
Dim rs As DAO.Recordset- ensure that this is loaded in your libary
Set rs = CurrentDb.OpenRecordset("SELECT NextNum FROM tblnextnum") --Nextnum is a field in the table tblnextnum
NextNo = rs!nextnum + 1
'now update the table
rs.Edit
rs!nextnum = NextNo
rs.Update
rs.Close
Set rs = Nothing
Me.AccountNo = NextNo -- this would be where the next number goes - so me.invoicenumber (me to ensure this field on the form/table ) and then the name of the field
Exit_Command22_Click:
Exit Sub


I use this in my d/base one to get a squencial numbering on Accounts numbers and also on invoices

in my old company where we used this code - in 4 years of heavy invoicing split accross 8 users - 3 or 4 duplicates max ..

regards

 

[clio]

It was your code - I got it from the sample database! thanks for stepping in...

right, I've tried this:

Private Sub Command44_Click() -- button to create invoice number
On Error GoTo Err_Command44_Click
Dim NextNo As Long
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT INVNUMNEXT FROM INVOICENUMBERS") - separate table I set up with only one (number) field and an autoid
NextNo = rs!INVNUMNEXT + 1
rs.Edit
rs!INVNUMNEXT = NextNo
rs.Update
rs.Close
Set rs = Nothing
Me.INVNUMBER = Format(Date, "yy") & NextNo - INVNUMBER field in current form, but from QUOTESINVOICES table
Exit_Command44_Click:
End Sub

when I click this, I get no errors, but nothing appears in the INVNUMBER.QuotesInvoices field or INVNUMNEXT.InvoiceNumbers field in the table.... can you tell me what I've got wrong?

thanks

 

[clio]

a bit more investigation led to the fact I'd renamed the command button, but the code was not automatically updated. Updated the code and got an error. I added this:
Exit Sub

Err_CREATEINVNUM_Click:
MsgBox Err.Description
Resume Exit_CREATEINVNUM_Click

End Sub

at the end and am now getting an error: 'No Current record'. I suspect it doesn't know which record in the current form to write it to?

 

[GaryPanic]

remove the YY element - not required

in your number field on "INVNUMBER field in current form, but from QUOTESINVOICES "

make sure you have a number
now as you want year digits have 200800001 as an invoice field - then change it in 2009 to 2009 00001and see if that works .

failing that
copy the code again (make sure you ahvea clean version of your d/b)
now past behind a button the working code with the table tblnextnum and the field nextnum
now on your form ensure that you have a field called invocie and also that the underlying table also has this field
so form field and table field same name (ifd you add it to the table first then add it from the drop down box on form design - it must be on the form you have the button)
now when you click the button the number should appear , click it agian and it should add 1 on to the number

 

[gemma-the-husky]

you need to get the invoice number after you have done oyur work on the record and before you save it

you need a field (can be hidden) bound to the underlying table, invoicenumber field

then in the forms before update event you can put

if nz(invoicenumber,0)=0 then
invoicenumber = getnextnumber
msgbox("Invoice No issued: " & invoicenumber)
end if

ie - if you already have a number you dont need to get another number
but if its a new record, then call your subroutine to retrieve the next number from the lookup table. The subroutine handles all the management of the number table.

The beforeupdate is the last event called before the record is saved

 

[Dennisk]

you require the correct locking. this is how it should be done in DAO

Option Compare Database
Option Explicit

Declare Sub Sleep Lib "kernel32" (ByVal dwMS As Long)


Public Function GetNextAutoNumber() As String
' Function to Emulate Autonumber Generation
' Generates err 3262 if table is locked
On Error GoTo Error_Handler

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblAutoNumber", dbOpenDynaset, dbDenyRead)
    
    rst.Edit
        rst!AutoNumber = rst!AutoNumber + 1
    
        'GetNextAutoNumber = Right$("0000" & rst!AutoNumber, LEN_OF_CASENOTE_NUMBER)
        GetNextAutoNumber = rst!AutoNumber
    rst.Update
      
Clean_Exit:
    rst.Close
    db.Close
    Exit Function
    
Error_Handler:
    If Err = TABLE_LOCKED Then
        ' Pause and try again
        Sleep 20
        Resume ' try to open the table again
    Else
        MsgBox "Error " & Err & " " & Err.Description
        Resume Clean_Exit
    End If

End Function