Access Duplicating Numbers

[Guest]

I have a database with a form that has the following coding:

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim mTable As String, mField As String, mYearPart As Long, mNextNumber
As Long
'number is returned as 6 characters: YY####
'where YY is the last 2 digits of the year
'#### is the next number for that year
'D is the DSCR Code
'format code to display this number should be "00D-0000"
mTable = "DSCR"
mField = "DSCRNumber"
mYearPart = Right(CStr(Year(Me.DSCRYear)), 2) * 10000
mNextNumber = Nz(DMax(mField, mTable, mField & ">=" & mYearPart), 0)

If mNextNumber = 0 Then
mNextNumber = mYearPart
End If

mNextNumber = mNextNumber + 1
Me.DSCRNumber = mNextNumber

Every so often - 5 out of a 100 - duplicate numbers are being assigned to
the form. However, when you check out the database there is only one number
being assigned. Can someone tell me what a user might be doing to get the
number and then another user can get the same number? Occasionally they are
on the same date; however, most are 3 or 4 days apart.

Thanks.

 

[Allen Browne]

How about using the BeforeUpdate event of the form, so the code fires at the
last possible moment before the record is created? Test Me.NewRecord so it
only fires for new records.

This also gives you access to the values in the other fields, which would be
Null when Form_BeforeInsert fires (unless they have a default value.)

Presumably DSCRYear is a Date/Time type field, since you are using Year() on
it. It would be good to test that it does have a date to work with.

Since DSCRNumber is a Text type field (contains the "00D-0000" type data),
you could use the Like operator.

If I have understood you correctly the code should also handle the case
where it is a new year and no records have been assigned yet.

Perhaps something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim lngLastNumber As Long
If Me.NewRecord And IsDate(Me.DSCRYear) Then
strWhere = "DSCRNumber Like """ & Format(Me.DSCRYear, "yy") & "D*"
varResult = DMax("DSCRNumber", "DSCR", strWhere)
If Not IsNull(varResult) Then
lngLastNumber = Val(Right(varResult,4))
End If
Me.DSCRNumber = lngLastNumber + 1
End If
End Sub

Note that Access 2000 and later are inconsistent with matching a field that
contains a dash. They behave differently depending on whether the field is
indexed or not. This could also be giving you problems.

It might be better to redesign the table so you have separate atomic fields
to store the date, the "D", and the next number, and avoid the dash
problems, instead of putting multiple items into one field.