As Rick suggested, use the form's Before Update event to reduce the
possibility that two users will attempt to grab the same number at the same
time. You may want to test the number, just to guard againt the coincidence
of two users creating a record at the same instant. One thing I have done
in such cases is to create a custom function to create the number. This
would be in the form's code module:
Public Function CreateNumber ()
Dim lngNum as Long
lngNum = Nz(DMax("[MyField]","[MyTable]", _
"Year([DateField]) = " & Year(Date()),0) + 1
Me.MyField = lngNum
End Function
This way you can create the MyField value at something like the form's
Before Insert event so it is visible to the user from the start of creating
the record. Note that you are not using DefaultValue in this case:
Private Sub Form_BeforeInsert(Cancel As Integer)
CreateNumber
End Sub
Then in the form's Before Update event:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim lngTest as Long
lngTest = DCount("[MyField]","[MyTable]", _
"Year([DateField]) = " & Year(Date()),0)
If lngTest > 0 Then
CreateNumber
End If
End Sub
Again, this is a way of letting the user see the number when the record is
first created. If that is not needed, just use the form's Before Update
event to create the number.
You could try testing by having two users in sight of each other attempt to
save a new record at the same time ("one, two, three, SAVE") to see if you
can create a duplicate value in MyField. If you are reasonably satisfied
this will not occur, the Before Update event will be OK for your purposes.
If it is possible to create a duplicate number you may want to use the
form's After Update event to check again. If it is a duplicate, return to
the record, clear the value from MyField, and instruct the user to save the
record again.
Another thing you could do is to create an incremented primary key as shown
in the link. It does not need to be seen, so you can set its visible
property to No. It would be a separte field from MyField. Use the same
error handling as shown in the sample for a myulti-user database, except add
a call to the function CreateNumber. I have to admit I have not used this
exact technique, but I think it would work, and it would eliminate the need
to check and recheck the MyField value.
jaworski_m said:
Hello,
Would you be so kind and advices how below expresion should be structred
to
enable proper numbering in multi user enviroment? Unfortunately, sugested
link does not respond to above question as assumtions taken in that
example
are different than in mine case - auto numbering reset each new calendar
year. The suggested example of numbering in the muti user enviroment based
on
the tabel prioperites (unique values feature), which is not relevant tn my
case.
Regards,
BruceM said:
As I showed you. The optional third part of the DMax expression is the
Where condition:
"Year([DateField]) = " & Year(Date())
This expression is telling Access to find the largest number in the field
MyField in the table MyTable in which the year in DateField is the same
as
the current year:
DMax("[MyField]","[MyTable]", _
"Year([DateField]) = " & Year(Date())
Use your own table and field names, of course.
The Nz function comes into play at the first record each year. At that
time
no records that match the criteria (Year in DateField is the same as the
current year), so the DMax function returns Null as the result. Nz
converts
Null to 0; otherwise it doesn't touch the value returned by DMax. The
expression adds 1 to 0 (first record of the year) or to the largest
number
in MyField for the year.
DefaultValue applies only to new records. If the user backs out of the
new
record without entering any data, no value is added to MyField.
Check VBA Help for more information about these functions:
DMax
Year
Date
Nz
Also, look up the DefaultValue property.
jaworski_m said:
Thank you for reply.
What is the way to reset sequential numbering implemented with "DMax"
function when year changes (2008->2009)
2008/1
2008/2
2008/...
After year change:
2009/1
2009/2
2009/...
:
Assuming that the form is based on the table MyTable or a query that
includes MyTable, MyField is the field in MyTable containing the
incremented
number (without the year), and DateField is a date field in MyTable,
in
the
form's Before Insert event:
Me.MyField.DefaultValue = Nz(DMax("[MyField]","[MyTable]", _
"Year([DateField]) = " &
Year(Date()),0) + 1
Note that the underscore is a line break character that is used for
convenience only. It can be used in VBA, but not in text box
expressions
ort query expressions, as I recall.
Concatenate with the year as needed. For instance, in the form's
Record
Source query:
FullNumber: Year([DateField]) & "/" & [MyField]
If you want leading zeros for MyField in FullNumber:
FullNumber: Year([DateField]) & "/" & Format([MyField],"0000")
The expression could also be used as the Default Value of a text box
bound
to MyField.
In a multi-user environment you will need to take precautions against
two
users creating a record at the same time and trying to use the same
number.
One way to prevent that most of the time is to wait until the form's
Before
Update event to run the code. You could also save the record as soon
as
the
number is created if you use the Before Insert event. Another way to
handle
the problem in a multi-user environment is shown here:
http://www.rogersaccesslibrary.com/...?TID=395&SID=a41d8z5zz92c1e125z9ec2eb2a8z6c2z
