Custom Sequential Numbering

[Raymon via AccessMonster.com]

I have an input form that has an invoice number field on it. The format for
invoice number will be 4-digits beginning with 0001 - 9999. The invoice
number will be drawn from the next sequential number or be marked as NC.

This will be accomplished with another drop down next to the invoice field
displaying the next sequential number or Manual entry or NC. It will also be
possible to select the next number and then amend it ....i.e 0245A

Is this possible? Can someone help me set this up?

Thanks,
Raymon

 

[Randy4BillsN3]

I have used the following snip of code in several cases for the same
type of function. If you are going to allow the addition of extra
characters at the end of the invoice number, however, additional
parsing is required to strip them off to find the last number. I have
added something I think should work, but did not verify through
operation, so you may need to tweak it. Not knowing what you will
allow them to enter for that field, could also complicate this
solution.


Dim intPrevInvoice as Integer, intNextInvoice As Integer
Dim strZeroFill As String

intPrevInvoice = Val(Left(DMax("[ProposalNo]",
"[tblProposals]")),4) 'Get the value from the string for invoices,
remove leading zeros and trailing characters
intNextNum = IIf(intPrevNum > 0, intPrevNum + 1, 1) 'If there are
no invoices, this is #1
strZeroFill = String$((4 - Len(intNextNum)), "0") 'Used to fill in
leading zeros
[NextInvoice] = strZeroFill & intNextNum