Creating unique, consecutive ‘Case’ number (2000)

Topic

New Reply

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

Reply | Quote

Replies

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 “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************************************************

Reply | Quote

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

Reply | Quote

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

Reply | Quote