Numbering System

[shadowfather]

I have a problem!

1st problem:-

I have a delivery number that is required to increment each time. I would prefer not using autonumber

2nd Problem:-

I need the deliver number to restart at 1 each year?

 

[alvechurchdata]

There's some sample code to do this at

http://www.alvechurchdata.co.uk/acckey.htm

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[FastLearnerIThink]

I have similar needs in the databases I have/am devolping. While I do use primary keys on my tables, the number most useful/important to my users are order numbers.

The format my order numbers follow are example: INQ-06001 where INQ stands for Inquiry, 06 is the current fiscal year (which starts in October 1 and ends in September 30 the following year, and 001 is the serial number which increments everytime a new inquiry is processed.

You should be able to work with this. I have commented the heck out of it so you can see what it's doing. If you have difficulties let me know and I can help. Most important, you will need to have the ELookup Function in your database, which can be found at the following link, for it to work.

http://allenbrowne.com/ser-42.html

If IsNull(Me.txtInqIDNo) Then [COLOR=green]'Check for Existing Inquiry ID No on current form
'if inquiry ID does not exist on form then execute the following [/color]
    Dim strFY, strSer, strInq As String [COLOR=green]'strings that piece the order number together[/color]
    
    [COLOR=green]'Assign strFY the last two digits of current fiscal year.
    '"Me.txtDate" is the text box that holds the current date, text box disabled and locked
    'Check for current month. If on or after October, use the following year for fiscal year
    'Otherwise, use current year for fiscal year (Fiscal Year Example: Oct 04 - Sep 05)[/color]
    If Month(Me.txtDate) >= 10 Then
        strFY = Right$((Year(Me.txtDate) + 1), 2)
    Else
        strFY = Right$(Year(Me.txtDate), 2)
    End If
    
    [COLOR=green]'Assign the next available serial number to strSer
    'Check to see if there are any entries on table. If table is empty strSer = 000[/color]
    If IsNull(DLookup("[fldInqIDNo]", "[tblInq]")) Then
        strSer = "000"
    Else
        [COLOR=green]'Check to see if there are any entries on table under current fiscal year. If no
        'records exist under current fiscal year, strSer = 000. If records do exist under current
        'fiscal year, use ELookUp, to sort the existing records under current fiscal year in
        'descending order and select the top record. Assign strSer the last three digits of top
        'record.[/color]
        If IsNull(DLookup("[fldInqIDNo]", "[tblInq]", "Mid$([fldInqIDNo], 5, 2) = '" & strFY & "'")) Then
            strSer = "000"
        Else
            strSer = Right$(ELookup("[fldInqIDNo]", "tblInq", "Mid$([fldInqIDNo], 5, 2) = '" & strFY & "'", "fldInqIDNo DESC"), 3)
        End If
    End If
    
    [COLOR=green]'Add 1 to strSer to assign strSer the next available number. Example: strSer=002, add 1,
    'strSer = 003. After adding, strSer looses the leading zeros. If strSer<10, add two leading zeros
    'to strSer. If str between 10 and 99 add one leading zero to strSer. Otherwise, leave strSer alone[/color]
    strSer = strSer + 1
    If strSer < 10 Then strSer = "00" & strSer
    If strSer >= 10 And strSer < 100 Then strSer = "0" & strSer
    
    [COLOR=green]'Assign strInq the next available inquiry number by piece-ing "INQ-", strFY, and strSer
    'together and assing txtInqIDNo the result (strInq).[/color]
    strInq = "INQ-" & strFY & strSer
    Me.txtInqIDNo = strInq
Else
[COLOR=green]'if Inquiry ID does exist on form, do nothing [/color]
End If

-- Fast Learner, I Think

Here's something to think about. How come you never see a headline like 'Psychic Wins Lottery!'? - Jay Leno

 

[MichaelRed]

see faq 700-184

many of the aproaches to generating the customized version of autonumber have distinct limitations (or hazzards) when used in multiuser environments. In particular, the use of the "live" table usually inhibits locking of the table when retrieving / generating the next value. This, in turn, permits the generation of duplicate values when users 'simultaneously' add new records, and THAT is a real problem.




MichaelRed

 

[Pampers]

Hi there,
This is what I got from Bob Scriver for making unique invoice numbers in a multi-user environment, in threadno: thread703-833004.

"One of the easiest ways I have found to do this in a multi-user environment is this. Create a Unique Index(no dups) for the InvoiceNo field so that as you then monitor the ERR value in your error trapping code behind a SAVE record button. Here is some sample code that will update the record when the user is done and ready to save with the correct Next largest invoice number even if there are multiply users creating records. The field would stay empty until the save button is clicked then be updated with the correct number. If someone has just updated to the same number it will increment the number by 1 until it finds the next vacant invoice number."

Private Sub SaveButton_Click()
On Error GoTo Err_SaveButton_Click
    DoCmd.RunCommand acCmdSaveRecord
Exit_SaveButton_Click:
    Exit Sub
Err_SaveButton_Click:
    If Err = 3022 Then
        Me.InvoiceNo = Me.InvoiceNo + 1
        Resume
    Else
        Resume Exit_SaveButton_Click
    End If
End Sub

Pampers

you're never too young to learn