increment no based on previous records

[antonyx]

hello all..

i was told when trying to create this invoice number.. why not just increment? why are you using random number?

the reason i wanted it random is because that is what my user wanted.. now they say they would prefer to have an incremented number..

here is what i am using now..

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 creates a random 5 digit number.. and then uses the 'accountref' to create a number..

here are some example numbers

Q8A12765
KYP89008
MCD34445

now.. instead of this random number.. my client wants the following..

the first ever invoice created will be

Q8A10001

account ref
number

what i want to do is when the number is created.. the form looks up the previous value in the invoice table..

so if i create another invoice it will become
Q8A10002

if i then delete the 02 invoice.. and create another invoice for a different account holder.. it should be

MCD10002

so basically it always checks the last invoice and increments 1 number.. regardless of the accountref before it..

struggling at the moment.. any help is much appreciated.

i have this other code that uses a similar method using the date..

eg first ref would be (for a record in january 2007)
01070001, and then a second on the same day would be 01070002, then a record for february 2007 would be 02070001 etc.. im sure the clue to what i want for the invoice is in this snippet but i cant get it working..

here is that code..

Private Sub txtjobdate_LostFocus()
Dim maxRef As Variant, maxID As Integer
Dim codeDate As String, maxDate As String
    
    codeDate = Format(txtjobdate, "MMYY")
    maxRef = DMax("JobRef", "tblJob", "JobRef like '" & codeDate & "*'")
    If (IsNull(maxRef)) Then    'test for new month
         maxID = 0                   'reset id to 0
    Else
         maxDate = Left(maxRef, 4)        'get date code
         maxID = CInt(Right(maxRef, 4))   'convert to int
    End If
    Me.txtjobref = codeDate & Format(maxID + 1, "0000")
End Sub

 

[llkhoutx]

Use a manually incremented (indexed, no duplicates) Long Primary. Query for the Max just before incrementing.

Don't use an autonumber PrimaryKey, you might occassionally get skips and screams, "Where's the invoice?"

 

[antonyx]

im not using an autonumber..

are you saying modify the invoiceref at table level to what you suggested..

i still need the first three letters of my account holder in front of that number..

could you not show me how that number could be created at form level using the code i pasted?

 

[llkhoutx]

You've got a table with invoice numbers in it, that's where you get the next number. Your invoice number is obviously text, just query for the Max of the numeric portion, increment that portion, add the text back and voila, your new invoice number.

 

[MarkK]

- What I would do is only store raw data in the table. By prepending strings to invoice numbers and making fancy processed date codes you're losing data resolution and making sorts and searches way less efficient, and storage and retrieval way more difficult.
- Store your invoice number as a long, re: llkhoutx. Store your date/time as a date/time. If you want to present it differently then do so upon retrieval.
- Construct "QBA10002" from the raw data in the Invoice table and Customer table as needed. Parse user input like "QBA10002", extract the number from the input, and search for invoice 10002.

My 2 cents,
Cheers,

 

[llkhoutx]

User's eventually fall upon the importance of indexing and datatypes.