I need a reliable way of incrementing invoice numbers

[Ricky1946]

Hello,
I need to configure a reliable way of creating incremented invoice numbers from a button_click into a text box backed up from a table.
Does anybody have a method that they have proved to be reliable?
Regards
Inky

"To say 'thankyou' encourages others."

 

[lupins46]

me.invoicenotextboxnamehere = nz(Dmax("invoicenofieldnamehere","tablenamehere"))+1

This will create a new number which is one more than the current highest number in the table.

 

[MichaelRed]

In a single user environ, the above, along with many others will work. In a multiuser environ, it is some what (GREATLY) different. faq700-184 shows a version of such a scheme, although it would need to be adapted ti suit your specifics.





MichaelRed

 

[Ricky1946]

Thanks to both of you I will give it a try and report back.
Ian

"To say 'thankyou' encourages others."

 

[Ricky1946]

Thanks Lupins46 and MichaelRed,
I have used Lupins code and it works brilliantly, but I have included a bit of code to prevent the user clicking twice in the same session.
I have put a variable flag called InvNoFlag which is set to zero. When the event is carried out the flag becomes 1. The sub exits when the flag is equal to 1 if it is triggered again. Works fine.

Also thanks MichaelRed. I can see that it is an ideal bit of code, and have logged it for future use.

Regards

Ricky



"To say 'thankyou' encourages others."

 

[willir]

Ricky

There is one concernt with lupins46 (wolf?) code.

This solution queries the current production database. If two end users run an update at the same time, they will both generate an invoice with the same number. I suspect this will generate an error if the invoice table insists on a unique invoice number.

Yet another approach is to use a table to store your control numbers. Then use a function to grab the next control record - use it for the current invoice, and write the next control record to the table.

Got to go for now, but I will post again with more info...
Richard

 

[willir]

Okay, emergency is over...

An example on how to do this...

tblControl
CtrlCode - primary key, string, 4 to 8 characters depending
CtrlNum - string, can also be numeric
CtrlName - descriptive text


Example of possible data...
[tt]
CtrlCode CtrlNum CtrlName
A/P AP000001 Accounts Payable invoice
PAY PR000001 Payroll
G/L GL000001 General Ledger
[/tt]
Although the control number can be numeric, it is often better to use a text string to allow for a more descriptive control number - for example, include the year.

The code to grab the next control number and add to it is fairly simple, although it still requires the creation of a module, and some coding. Perhaps something like this.

In the database window, select the "Modules" tab. Click on "New". Make sure the top of your code includes...

Option Compare Database
[COLOR=blue]Option Explicit[/color]

The actual module could look something like...

Function ControlNumber(strCtrlCode As String) As String

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSQL As String, strQ As String
Dim lngCtrlNum As Long, strCtrlNum As String
[COLOR=green]
'strQ used to replace double quote character[/color]
strQ = Chr$(34)
[COLOR=green]
'Select statement to retrieve and update control number
'I am assuming that
'- the prefix of 2 characters for the control number will be used
'to identify the system for the control number
'- the actual number will consist of 6 characters, 000000[/color]
strSQL = "SELECT * From tblControl WHERE CtrlCode = " & strQ & strCtrlCode & strQ

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSQL)

With rst
   .MoveFirst
[COLOR=green]   
   'Get the current control number[/color]
   strCtrlNum = Nz(!CtrlNum, "UK000000")
[COLOR=green]   
   'Assign the control number to the returning function[/color]
   ControlNumber = strCtrlNum
[COLOR=green]  
   'Strip off the 2 character string prefix to grab the number
   'This needs to be done so we can add 1 to the number
   'I could have used a more simple approach
   'lngCtrlNum = CLng(Right(strCtrlNum, 6))
   'But this code does the same and is more flexible[/color]
   lngCtrlNum = CLng(Right(strCtrlNum, Len(strCtrlNum) - 2))
[COLOR=green]   
   'Increment by 1[/color]
   lngCtrlNum = lngCtrlNum + 1
[COLOR=green]   
   'Create a new control number[/color]
   strCtrlNum = Left(strCtrlNum, 2) & Format(lngCtrlNum, "000000")
[COLOR=green]   
   'Now update the control table[/color]
   .Edit
   !CtrlNum = strCtrlNum
   .Update

End With
[COLOR=green]
'Free up resources [/color]
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Function

Most of the above is comments, in green, is for your benifit.

The calling staement is pretty easy.

For the BeforeUpdate event, or your "button click" event, assuming your field name is CtrlNumber, run the code

Me.CtrlNumber = ControlNumber("A/P")

Enjoy
Richard

 

[MichaelRed]

qillr,

but your previous thread notes the multiuser situation, and your code doesn't account for this possability, just moves it to a new venue. Further, storing the entire control value (e.g. including hte prefix chars) merely necissitates their removal and reattachment. I (previously) provided a reference to a faq which does account for the multiuser use of this type of function. Perhaps you would like to look it over?





MichaelRed

 

[jsteph]

willr,
Set rst = dbs.OpenRecordset(strSQL) does not by default provide the locking needed for this....
I think you'd need to use something like

Set rst = dbs.OpenRecordset(strSQL,dbopenDyanset,dbDenyWrite,dbPessimistic)

--J

 

[Ricky1946]

All of your comments have been noted - and I am very grateful. As this database is for a small company and I suspect only one person will create an invoice I will leave it as it is but your relevant points have been kept for future reference.
Thanks again.
Ricky

"To say 'thankyou' encourages others."