antonyx
Arsenal Supporter
- Local time
- Today, 14:41
- 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..
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 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.