Incrementing number

[Guest]

Hi,

I have the following code in the Update event of one of my date/time fields.
When fired, it causes the job number field to increment to the next number.
In addition, the code has the job number being reset to "1" at the start of
each day.

This worked great until I split the database, and we began using it as a
multi-user system. Now when more than one person inputs a new job into the
database at the same time, they get duplicate job numbers. Yesterday I tried
inputting a new job at the same time that another user was doing the same --
we both got a job "1" for our jobs that we input.

Is t here a way to adjust the code so that it increments the way it did
before we split the database and began having multiple users doing the input?

Here is the code, which is in the AfterUpdate event of our date/time field
(ClockedIn). (The job number field's name is ReqNo.):

--------------------------------------------------------
Dim tempDate As String

Me!ClockedIn.Value = Now()
tempDate = Format(Now(), "mm/dd/yy")

If DCount("[ReqNo]", "Tasks", "[ClockedIn] > #" & tempDate & "#") = 0 Then
Me.ReqNo = 1
Else
Me.ReqNo = DMax("[ReqNo]", "Tasks", "[ClockedIn] > #" & tempDate & "#") + 1
End If

 

[Naresh Nichani MVP]

Hi:

One idea would be to update the record in the AffterUpdate of the ClockedIn
field - this would save record and next user would get next number.

To save the current record you can do this in VBA
Application.RunCommand acCmdSaveRecord

Regards,

Naresh Nichani
Microsoft Access MVP