B
BruceM
The is something of a repost. With all due appreciation to the person who
responded before, I did not frame my question very well, and the conversation
veered from its intended course.
I have a database for making a certain type of report. Currently the
reports are individual Word documents, numbered sequentially: RPT 05-01, RPT
05-02, etc. With code provided by Allen Browne and additional assistance and
explanations by Tim Ferguson I put together code that automatically adds a
number in the same format and in correct sequence to each new record. 05
represents the year, and the numbering starts over at RPT 06-01 next year.
It works perfectly in terms of assigning a number as long as this is a
single-user database. However, there could be two users starting reports at
the same time.
Here is the code, which I have as a called procedure named AssignNumber:
If Me.NewRecord Then
Dim strWhere As String
Dim varResult As Variant
strWhere = "RPT_Number Like """ & "RPT " & Format(Date, "yy") & "*"""
varResult = DMax("RPT_Number", "tblRPT", strWhere)
If IsNull(varResult) Then
Me.RPT_Number = "RPT " & Format(Date, "yy") & "-01"
Else
Me.RPT_Number = Left(varResult, 7) & _
Format(Val(Right(varResult, 2)) + 1, "00")
End If
End If
RPT_Number is the PK. I could use another field (autonumber, for instance)
as PK if that would help. I am running the code in the form's On Current
event because the users are accustomed to adding the number at the beginning
of the process. Please accept that training the users not to expect a number
until the record is saved is a course I would rather not take unless there is
no alternative. The problem is that if two users start records at the same
time, both will see the same number at first, but the second person to finish
will get an error (3022) because of duplicate PK values.
Roger Carlson's web site had a solution to this issue using the from's Error
event and a function:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Response = IncrementRPT(DataErr)
End Sub
Function IncrementRPT(DataErr)
If DataErr = 3022 Then
Call AssignNumber
IncrementRPT= acDataErrContinue
End If
End Function
However, I cannot get this to work. The user has several choices after
starting a record: send an e-mail, close the form, navigate to another
record, etc. Command buttons (such as the e-mail button) save the record
before doing SendObject or closing the form or whatever they do, thus making
the form's Before Update event run. Before Update contains some validation
to be sure all required fields are filled in. Error 3022, when it occurs, is
associated with a particular control. If the user attempted to send an
e-mail, 3022 is associated with that command button (error handling
identifies the error number and control name).
The code in Roger's sample db functions properly, but he is using it to
prevent duplicates in an incremented Number PK field. I wonder if the code
does not work the same way in my situation because the form's Error event
contains: (DataErr As Integer, Response As Integer), but RPT_Number is a text
field.
Another thought I had was to assign the number to an unbound text box (in
the Current event), then assign it to the PK field (RPT_Number) in the form's
Before Update event, with code to compare RPT_Number to the value in the
unbound control. If the numbers are not the same the user will receive a
msgbox notification that the number will be changed. However, even if this
is a reasonable approach I cannot figure out how to get the value into the
unbound control in the first place.
Sorry to be so long winded. There are a lot of pieces to the puzzle. This
database will be used only occasionally, at least for some users, and needs
to be self-explanatory to the extent possible, which is why I am going to so
much trouble.
responded before, I did not frame my question very well, and the conversation
veered from its intended course.
I have a database for making a certain type of report. Currently the
reports are individual Word documents, numbered sequentially: RPT 05-01, RPT
05-02, etc. With code provided by Allen Browne and additional assistance and
explanations by Tim Ferguson I put together code that automatically adds a
number in the same format and in correct sequence to each new record. 05
represents the year, and the numbering starts over at RPT 06-01 next year.
It works perfectly in terms of assigning a number as long as this is a
single-user database. However, there could be two users starting reports at
the same time.
Here is the code, which I have as a called procedure named AssignNumber:
If Me.NewRecord Then
Dim strWhere As String
Dim varResult As Variant
strWhere = "RPT_Number Like """ & "RPT " & Format(Date, "yy") & "*"""
varResult = DMax("RPT_Number", "tblRPT", strWhere)
If IsNull(varResult) Then
Me.RPT_Number = "RPT " & Format(Date, "yy") & "-01"
Else
Me.RPT_Number = Left(varResult, 7) & _
Format(Val(Right(varResult, 2)) + 1, "00")
End If
End If
RPT_Number is the PK. I could use another field (autonumber, for instance)
as PK if that would help. I am running the code in the form's On Current
event because the users are accustomed to adding the number at the beginning
of the process. Please accept that training the users not to expect a number
until the record is saved is a course I would rather not take unless there is
no alternative. The problem is that if two users start records at the same
time, both will see the same number at first, but the second person to finish
will get an error (3022) because of duplicate PK values.
Roger Carlson's web site had a solution to this issue using the from's Error
event and a function:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Response = IncrementRPT(DataErr)
End Sub
Function IncrementRPT(DataErr)
If DataErr = 3022 Then
Call AssignNumber
IncrementRPT= acDataErrContinue
End If
End Function
However, I cannot get this to work. The user has several choices after
starting a record: send an e-mail, close the form, navigate to another
record, etc. Command buttons (such as the e-mail button) save the record
before doing SendObject or closing the form or whatever they do, thus making
the form's Before Update event run. Before Update contains some validation
to be sure all required fields are filled in. Error 3022, when it occurs, is
associated with a particular control. If the user attempted to send an
e-mail, 3022 is associated with that command button (error handling
identifies the error number and control name).
The code in Roger's sample db functions properly, but he is using it to
prevent duplicates in an incremented Number PK field. I wonder if the code
does not work the same way in my situation because the form's Error event
contains: (DataErr As Integer, Response As Integer), but RPT_Number is a text
field.
Another thought I had was to assign the number to an unbound text box (in
the Current event), then assign it to the PK field (RPT_Number) in the form's
Before Update event, with code to compare RPT_Number to the value in the
unbound control. If the numbers are not the same the user will receive a
msgbox notification that the number will be changed. However, even if this
is a reasonable approach I cannot figure out how to get the value into the
unbound control in the first place.
Sorry to be so long winded. There are a lot of pieces to the puzzle. This
database will be used only occasionally, at least for some users, and needs
to be self-explanatory to the extent possible, which is why I am going to so
much trouble.