generate sequential invoice number at form level

antonyx

Arsenal Supporter
Local time
Today, 08:45
Joined
Jan 7, 2005
Messages
556
hello all..

i have been using my new system for a month or so and have come across a problem with my invoicing.

basically i create an invoice number at the moment using the account reference (3 letters) and a random 5 digit number..

so lets say my account holder is McDonalds.. a few of their invoices would be..

MCD03345,MCD56777.. etc

so every invoice is..
XXX00000

now i thought that the invoice number would be random using this code to generate it at form level..

Code:
Private Sub btnGenerate_Click()
Dim rdm As Long
Dim xlook As Variant
If IsNull(cboaccountref) Then
Me.cboaccountref.SetFocus
Else
Do
rdm = Int((99999 - 11111 + 1) * Rnd + 11111)
Me.txtinvoicenotest = Me.cboaccountref & rdm
xlook = DLookup("[InvoiceRef]", "tblInvoice", "[InvoiceRef] = '" & Forms!frmNewInvoice!txtinvoicenotest & "'")
Loop Until IsNull(xlook)
Me.txtInvoiceNo = Me.txtinvoicenotest
DoCmd.RunCommand acCmdSaveRecord
DoCmd.close
End If
End Sub

this code uses the selected account ref.. and generates a 5 digit number. It then combines the two to create an 8 digit invoice number.. it checks the previous invoices to ensure the 8 digit value is not present before it is saved.

now.. using this i have realised that the same 5 digit number is being used, but because the refs before the number are different.. the database sees no problems.. here is a screenshot of my invoice table at the moment..

http://www.londonheathrowcars.com/inv.jpg

most are coming out the same and its annoying.. what i would like to do is alter the above code to the following..


firstly i dont want the random number.. just a sequential number starting from 10001..

you create an invoice.. and using the current ref (Mcdonalds in this case) the invoice is set as

MCD10001

now, if i create another invoice number for mcdonalds.. it will create as

MCD10002

also.. with this rule in place.. if i create an invoice for Burger King (BKG) then it will create is as..

BKG10001.. this is not what i want.. i want the database to check only the last 5 digits of the previous invoices.. and increment the invoice by 1.. so really the burger king invoice after the 2 mcdonalds ones should be..

BKG10003

i hope this is clear.. i know its a lot to take in.. but i thought i would just write it all..

i look forward to your thoughts.. thanks.
 
I think I would store the last 'squential number used' in a system table variable and use this number to generate the invoice number, etc...
 
so are you saying that when the invoice number is generated..

the number 10001 can be placed in a separate table..

then when you generate an invoice.. it will search this table.. increment.. and place 10002 in the table?
 
is there a way to do this simply by altering the code im using on the button above.. or will i have to look into it properly and change everything..
 
I think you just create the table and do a dlookup() to get it the excute an update sql statement to increment it...
 

Users who are viewing this thread

Back
Top Bottom