New Record number (not AutoNumber) upon new record creation

[lmjje]

So then my question would be how should I go about solving the problem for a Multi-user environment? Whatever solution works there should also work in the single user environment. I am just trying to stay away (as much as possible) from human entered data.

[Rod]

I'm back! Been asleep - it's a time difference thing.

Backtracking:

The range of functions available at the Default Value property in table design is very, very limited - no Nz, no DMax, etc. To examine the available choices click on the elipses (...) to the right of the property and open up the Functions entry by double clicking on it. I don't think you will find your solution at the table design stage.

So you will have to calclate this number at a different stage in the entry of a new employee. You mention:

... self generated number that is created as soon as the Add New Employee form is opened up.

This is not such a good idea: having issued the new number, what if the user abandons the entry? You've stated the number must be contiguous so the only way is to unissue the number - and what if another user has been issued the next number in the interim?

No the best stage to obtain the number is immediately before the new emplyee record is saved for the first time. Use Orange's code but enhance it slightly to test whether the employee number is empty - only retrieve the next number if it is - and to cater for the very first time when there are no records in the table. The event to use is the form's Before Update. So I would expect something like

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Nz(Me.txtEmployeeNum, "") = "" Then
        Me.txtEmployeeNum = Nz(DMax("EmployeeNum", "tblEmployee"), 0) + 1
    End If
End Sub

Use your own names of course.

Now what about this multi-user situation. It is possible that another user interferes/overlaps and gets the same employee number. So what to do?

The first thing is to make sure that EmployeeNum (or whatever it's called) has an index and that this index is specified as unique. The first user to use the duplicate number will be successful but the second will recieve a bitch from the JET RDMS and the record will not be added. As I write I do not know the error number but the trick is to trap the error and retry the whole thing again. I'll set up a basic prototype and publish the results for you.

[Rod]

BTW I forgot to mention in my last post that you should disable the employee number control on the form; you don't want users messing with it.

OK, the error number issued for contravening a unique index is 3022. Now the only way I know of trapping a JET error is in the Error event of the form, something I do not use often. I invite anyone with more experience to contribute how they would then handle an error other than 3022.

Here's my code:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Select Case DataErr
        Case 3022
            Me.txtEmployeeNum = Me.txtEmployeeNum + 1
            Response = acDataErrContinue
            Msgbox "An error occurred while saving the record.  Please retry."
        Case Else
    End Select
End Sub

Line 3 advances the new employee number by one. Line 4 supresses Access' error message. Line 5 invites the user to retry.

I am uncertain what code should be placed after the Case Else. My immediate reactiion is to use the Err.Raise method to force a repitition of the whole error process. Anyone else?

[lmjje]

Rod, Thank you for the help, right now I have the record creation autosave upon exit or when a second new record is created. If the employeeID is not populated it does not create the record. With this method would I need to have the record saving happen through a different method?

[Rod]

Imjje,

I'm confused, it doesn't take much, in fact more than that, I do not understand your post. What is 'record save autocreation?' I think it's time you published your db; if you need help to do this then shout. Right now I'm going back to bed.