Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Alternate option for Autonumber (2000/XP)

    I have had it with the Autonumber function in Access and want to try and Autonumber records using the MAX+1 type function to do it.

    My first problem is if you use the Autonumber feature, it works great until you start a new record and then cancel it. You loose that autonumber for good. I am trying to setup an external PO System that automatically generates a new PO number based on the last PO entered. For example... For Fiscal year 2003 all PO's start wiht 2003001, 2003002 etc.. Then in Fiscal 2004 it will start with Fiscal 2004001, 2004002 etc... I want to create a button that creates a new record and automatically puts the new PO number in by simply finding the largest number (using the Max function) and adding 1 to that number.

    Makes sence, however I have no idea how to do it. If anyone knows how to go about doing this it would be greatly appreciated!!!

    PS where is the search function for this forum? I imagine this has probably been answered somewhere in the 40 pages of posts but I dont have time to check every page.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Alternate option for Autonumber (2000/XP)

    Announcements concerning the functioning of the Lounge as a whole, such as the search function, are made in the Words From On High forum, not in the Access forum. See <!post=Temporary suspension of Search facility,250092>Temporary suspension of Search facility<!/post>.

    The AutoNumber field type is meant to provide an identifier that is guaranteed to be unique and unchangeable. An additional advantage is that the number is available the moment the user starts to enter data in a new record; this is very convenient in forms with subforms. An AutoNumber is not meant to be a meaningful number; I always hide it from the end user, so gaps in the sequence caused by canceling new records do not matter.

    If it is essential that the user sees the identifier and that there are no gaps, AutoNumber is not the right type. You can use a Number field (Long Integer) instead. You will have to make sure that the user can only enter new records in a form, never directly in the table or in a query based on the table, and you will have to take care that the number is increased correctly in a multi-user environment. I find that the safest way is to use a separate unbound form in which the user enters data for a new record. This form contains an OK button and a Cancel button. The OK button computes the new PO number and saves the record. It is essential that the record is saved immediately after the PO number has been computed, otherwise two users could get the same number. The Cancel button just closes the form without saving it.

    Code to compute the new PO number would be as follows (table and field names are fictitious):

    Function GetNewPO As Long
    Dim lngPO As Long
    Dim lngYear As Long

    ' Get highest PO number in table
    lngPO = DMax("PO", "tblPO_Data")

    ' Get current year
    lngYear = Year(Date)

    ' Check year
    If (lngPO 1000) = lngYear Then
    ' Still in same year, so increase number
    lngPO = lngPO + 1
    Else
    ' Happy New Year!
    lngPO = 1000 * lngYear + 1
    End If

    ' Return new PO
    GetNewPO = lngPO
    End Function

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Alternate option for Autonumber (2000/XP)

    The other option, as opposed to doing a MAX of the table, is to store the next number in a single record table, and using an unbound form as Hans suggested, open that table just before the save operation, get the number, assign it to the record being saved, increment it by one, and then save the new next number - that requires ADO or DAO to manipulate the record with the counter however.
    Wendell

  4. #4
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Alternate option for Autonumber (2000/XP)

    Hi

    I gleaned this method from an earlier post to generate a specific number and maintain in a table. The code goes in the before update of the form. It may be of some help.

    Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim DB As DATABASE
    Dim rst As Recordset
    Dim lngCntr As Double
    Dim intRetry As Double
    Dim intNum As Double, intA As Integer, intB As Integer
    Dim strANum As String
    If Not IsNull(Me!InvoiceNo) Then
    Exit Sub
    ' Cancel = True
    Else
    Set DB = currentDB()
    Set rst = DB.OpenRecordset("tblCounter", DB_OPEN_DYNASET) 'Open table with the counter
    rst.MoveFirst
    If rst!Value = 999999 Then
    MsgBox "This is last Invoice Number that has been allocated. Please contact System administrator for the next series of Invoice Numbers.", vbCritical
    'DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
    'Exit Sub
    End If

    rst.Edit
    rst!Value = rst!Value + 1
    rst.Update

    If rst!Value > 1000000 Then
    MsgBox "The last Invoice Number has been used. Please contact System administrator for the next series of Invoice Numbers.", vbCritical
    DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
    Exit Sub
    End If
    lngCntr = CLng(rst!Value) - 1

    intNum = lngCntr 'Mod 100000
    'intA = (lngCntr 1000) Mod 26
    'intB = (lngCntr 1000) 26
    ' intNum = CLng(rst!Value - 1)
    'Generate the AlphaNumber
    'strANum = "ACHME" & Format$(intNum, "000")
    strANum = Format$(intNum, "000000")
    ' AlphaNumGenerate = strANum
    Me!InvoiceNo = strANum
    End If

    ExitAlphaNumGenerate:
    Exit Sub
    ErrorAlphaNumGenerate: 'If someone is editing this record trap the error
    If Err = 3188 Then
    intRetry = intRetry + 1
    If intRetry < 100 Then
    Resume
    Else 'Time out retries
    MsgBox Error$, 48, "Another user editing this number"
    Resume ExitAlphaNumGenerate
    End If
    Else 'Handle other errors
    MsgBox Str$(Err) & " " & Error$, 48, "Problem Generating Number"
    Resume ExitAlphaNumGenerate
    End If


    End Sub


    Regards
    WTH

  5. #5
    New Lounger
    Join Date
    Jun 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Alternate option for Autonumber (2000/XP)

    Thanks guys for all the hints..... Since it isn't a multi user database I didn't have to worry about that so the code was actaully quite simple......

    This is what I ended up using

    Private Sub Command14_Click()
    On Error GoTo Err_Command14_Click


    DoCmd.GoToRecord , , acNewRec
    PO = DMax("PO", "PO_tble") + 1

    Exit_Command14_Click:
    Exit Sub

    Err_Command14_Click:
    MsgBox Err.Description
    Resume Exit_Command14_Click

    End Sub

    Not sure how much trouble I may run into with the simplicity of that code but if it works, use it, when it stops working, I will figure it out then.

    As usual, you guys have come to my rescue thanks a bunch!!!

    PS Sorry about the search function question, didn't spend alot of time looking around for an explanation.

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Alternate option for Autonumber (2000/XP)

    >> PO = DMax("PO", "PO_tble") + 1 <<

    Actually, I use this type of code even in multi-user databases where an autonumber is not appropriate. However, I put it in the form's BeforeUpdate event, so it is the last thing the system does before writing a new record (I embed it in an IF clause checking for me.newrecord=true).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •