Unique, yet 'private' incremental numbers 3

[MisterEkted]

I am still learning Access. Addictive it is. I want to create auto-incrementing estimate and invoice numbers so that the customer does not know how many others I am making.
My thought was to use his ID number (188 for example) as a prefix like this:
188-0001 would be the first invoice for that customer.
188-0002 would be the second, and so on.
There is probably a better way to do this, but this would probably work for my application, if it isn't too difficult.
Thank you.

 

[ZmrAbdulla]

This is not shortest way but a workable solution

   Dim strNewInvNumber, strMstrNumber As String
    Dim intOldIncrNumber, intNewIncrNumber As Integer

    '//Get the master number
    strMstrNumber = "188"
    '//you can use a reference to a textbox
    '//Like
    '//strMstrNumber = Left(Me.txtInvoiceNumber, 3)
    '//or like
    '//strMstrNumber = Me.txtMasterNumber
    
    
    '//Get the old highest number
    intOldIncrNumber = Val(Right(DMax("InvoiceNumber", "tblInvoice", strMstrNumber), 4))

    '//assign leading zeros to number
    Dim strLdZeros As String
    Select Case Len(intOldIncrNumber)
    Case Is = 0
        strLdZeros = "0000"
    Case Is = 1
        strLdZeros = "000"
    Case Is = 2
        strLdZeros = "00"
    Case Is = 3
        strLdZeros = "00"
    End Select

    '//Create New Invoice Number formatted like "188-0001"
    strNewInvNumber = strMstrNumber & "-" & strLdZeros & Nz(intOldIncrNumber, 0) + 1

    '//Get the new number
    MsgBox strNewInvNumber

Others may have a better solution.

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.

 

[SkipVought]

Hi,

Have a table for storing, InvPfx and LastInvNbr for each CustID.

Select InvPfx & "-" & Format(LastInvNbr,"0000") from...

I'd make the query in a Function that returns the next inv nbr for a given custid.

Skip,

_[red][/red]

 

[MisterEkted]

Thank you very much for your suggestions. The first obviously is longer. So I will try the second one first. The suggestion about creating a table is a little vague to me but I will try to fill in the holes.
-Kerry

 

[SkipVought]

Well you get an order from ABC Inc. What's the next number? You get it out of a table that you maintain. You get the next number for ABC Inc (which is different than the next number for XYZ Ltd), format the invoice and then update the table with the next number for ABC Inc, so that the NEXT time you get an order from ABC Inc, you have the NEXT number for ABC Inc, which you would have forgotten, unless you wrote it down and saved it in a safe place that you would remember, but that takes alot of manual intervention which I, certainly, would not want to have to keep up with if I had a computer to do it for me. _{coming up for a breath!}

Skip,

_[red][/red]

 

[jfdabiri]

why not instead of this:

'//assign leading zeros to number
    Dim strLdZeros As String
    Select Case Len(intOldIncrNumber)
    Case Is = 0
        strLdZeros = "0000"
    Case Is = 1
        strLdZeros = "000"
    Case Is = 2
        strLdZeros = "00"
    Case Is = 3
        strLdZeros = "00"
    End Select

use this:

oldincrnbr = oldincrnbr + 1
four_digit_num = right("000" & oldincrnbr, 4)
strNewInvNumber = strMstrNumber & "-" & four_digit_num

 

[ZmrAbdulla]

That is cute.

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.