T
tina
i had a similar requirement from a customer recently. try
this code:
Dim strDate As String
strDate = CStr(Format(Date, "yymmdd"))
Me!ControlName = strDate & Format(Nz(DMax("Right
(PrimaryKeyField,3)", "TableName", "Left
(PrimaryKeyField,6) = '" & strDate & "'"), 0) + 1, "-000")
you might want to run this in the form's BeforeUpdate
event, so the primary key for the new record is created
immediately before the record is saved. even so, in a
multi-user environment you could run into problems if two
users try to save a new record at the same time. if that
happens, one user will probably get some sort of "unique
index" violation. you could try to trap such an error by
err.number, and Resume back to the top of the procedure so
Access will calculate the value again. good luck!
i got a hand with this code from Wayne Gillespie's reply
to a similar posted question on 7/15/03.
this code:
Dim strDate As String
strDate = CStr(Format(Date, "yymmdd"))
Me!ControlName = strDate & Format(Nz(DMax("Right
(PrimaryKeyField,3)", "TableName", "Left
(PrimaryKeyField,6) = '" & strDate & "'"), 0) + 1, "-000")
you might want to run this in the form's BeforeUpdate
event, so the primary key for the new record is created
immediately before the record is saved. even so, in a
multi-user environment you could run into problems if two
users try to save a new record at the same time. if that
happens, one user will probably get some sort of "unique
index" violation. you could try to trap such an error by
err.number, and Resume back to the top of the procedure so
Access will calculate the value again. good luck!
i got a hand with this code from Wayne Gillespie's reply
to a similar posted question on 7/15/03.