Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Feb 2002
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating unique, consecutive 'Case' number (2000)

    I would like to create a sequential number to assign to each record as it's created to be used as a Case#. I know enough not to use the Autonumber, as the numbers change at times such as database compaction.

    I could create a one field table and store the number in it and update the table upon the creation of each new record -- when a new record is created I would increment the Case# in the table and use it. But if there are multiple users, I'm concerned that the same number would be used more than once before it gets updated.

    I suppose I could keep requerying the field in the table that all of the data is in to determine the next number, but as the table grows, it would seem that getting a new number would get slower and slower. And I still have the same concern about having multiple users being able to create the same Case#.

    If anyone has dealt with these concerns, I'd like to hear from them. Basically, I want a unique case#, with no chance for multiple users to create the same number twice.

    Thanks,

    Randy

  2. #2
    Star Lounger
    Join Date
    Sep 2001
    Location
    Pleasanton, California, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Here you go

    Here is some code I use for incrementing case numbers in a pathology lab database. These have a year designation before the incremented number, and the code accounts for the new year on Jan 1. It also inserts leading zeros. Hopefully I have commented it enough that you can figure it out. It is on a button on the form.

    What this does is find the current max number and then increments it and pastes the new number in the case # field. All users must use this button to get a new number.

    '*********************code************************ *
    Private Sub NextIDP_Click()
    'Increments the IDP number. Accounts for change-over to a new year and starts with 0001.

    Dim i As Integer 'used to increment IDPNUM
    Dim MaxNum As Variant 'Maximum IDPNUM in tblSpecimenLog
    Dim NewNum As Variant 'New year number
    Dim Zeros As Variant 'Used to add leading zeros to final number (ie. 0001)

    '//Open new record
    DoCmd.GoToRecord acDataForm, "SpecLog", acNewRec

    'Get maximum IDPNUM number from tblSpecimenLog
    MaxNum = DMax("[IDPNUM]", "tblSpecimenLog")
    'MsgBox "MaxNum =" & MaxNum 'debugging aid.

    '//TEST for year. If year is less than current year (as on Jan 1) then set the year to_
    'the current year and increment number. If year is equal to current year, then increment_
    'number. Left$(MaxNum,4) grabs the year part of the IDPNUM.

    'Check for year less than current year.
    If Left$(MaxNum, 4) < Year(Now()) Then
    'MsgBox "MaxNum is less than present year" 'debugging aid.
    NewNum = Year(Now())
    'MsgBox "NewNum =" & NewNum 'debugging aid.
    i = 1
    'MsgBox "i =" & i 'debugging aid.


    'Check for current year.
    ElseIf Left$(MaxNum, 4) = Year(Now()) Then
    'MsgBox "MaxNum = present year"

    NewNum = Left$(MaxNum, 4)
    'MsgBox "NewNum =" & NewNum 'debugging aid.
    i = Val(Right(MaxNum, 4)) + 1
    'MsgBox "I =" & i 'debugging aid.

    'Check for future year
    ElseIf Left$(MaxNum, 4) > Year(Now()) Then
    MsgBox "You cannot enter a future year. Please delete this number"

    End If


    '//Set up leading zeros for incrementing part of number.
    Zeros = IIf(i < 10, "000", IIf(i < 100, "00", IIf(i < 1000, "0", "")))

    '//Set IDPNUM equal to new numbers
    Forms!SpecLog!IDPNUM = NewNum & "-" & Zeros & Format(i)
    'Set the cursor focus to the DASH field.
    Forms!SpecLog!DASHNUM.SetFocus
    'Save the record
    DoCmd.RunCommand acCmdSaveRecord

    End Sub
    '************************end code********************************************** **

  3. #3
    Star Lounger
    Join Date
    Feb 2002
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Here you go

    Tim,

    Thanks for the reply and code sample. I was wondering if this is used by multiple users simultaneously, and if so, have you ever had a problem with duplicate case numbers?

    Randy

  4. #4
    Star Lounger
    Join Date
    Sep 2001
    Location
    Pleasanton, California, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Here you go

    Randy. this is normally used by two or three people at a time. The last line saves the record, which prevents anyone else from getting the same number. I originally had some required fields in the form but if the form is not saved a duplicate number can get assigned during the time someone is filling in the form, so I quit using required fields. So far it has worked fine.

    Tim Morken
    Atlanta

Posting Permissions

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