Avoid duplicate fields

[BruceM]

I have code that assigns a primary key field in a particular format. The
primary key is the report number (report in the generic sense, that is, not
an Access object) in the format RPT 05-01, RPT 05-02, and so forth. That is
fine, as long as there is only one user. While multiple users are not
likely, it could happen, so I attempted something I got from Roger Carlson's
site to avoid duplicates. There is a function something like this:

Function IncrementRPT(DataErr)

If DataErr = 3022 Then
Call AssignNumber
IncrementRFI = acDataErrContinue
End If

End Function

AssignNumber is a sub that uses DMax and other manipulations to produce a
report number. It works as expected.

To go with this, in the Form's Error event I have:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Response = IncrementRFI(DataErr)

End Sub

Both pieces of code have error handling, not included here. Also, there is
a message box in the Error event advising the user that the number will
change.

However, it doesn't seem to work as I would hope. One difficulty I am
having trying to grasp this is that I don't really understand the form's
Error event. My form has a Before Update event that performs some validation
(making sure all required fields are completed). I have several ways of
saving the record (causing Before Update to run). If, for instance, I use a
command button to save the record, error 3022 (duplicate PK) is a command
button error. I know this because my error handling identifies the event in
which the error occurs. I can't seem to find a way for the database to
determine that the PK is already in use, and to go back for another one.
Perhaps I need to refresh the form, or something like that, so that the new
number appears? If I could see the message box (in the form's Error event)
advising the user that there is a new number I would be more confident about
this. Can anybody help get me on track here? I think I may be getting
close, but something is missing.

 

[Klatuu]

Bruce,
I am guessing that the format of your key is:
RPT - an identifier that this is a report and will not change
05- - Denotes the month of the report and will only change when the month
changes
01 - An incrementing number to create a unique key

If the above is correct, then this might work for you:

Function GetNextRptKey( ) As String
dim IntNexNum as Integer
dim strNextKey as String

strNextKey = DMAX([PrimaryKey],"MyTable")
intNextNum = Cint(Right(strNextNum),2) + 1
GetNextKey = Left(strNextKey,7) & Format(intNextNum,"00")
End Function

 

[BruceM]

Thanks for the reply. I noticed in the code i provided that I sometimes used
"RFI" where it should have been "RPT". Actually, 05 is the year, and -01
etc. is the sequence. There have never been more than about 30 of these
reports in any year. In 2006 the sequence will start over with RPT 06-01.
This is the code, provided in this group by Allen Browne, with some
additional assistance and explanations by Tim Ferguson:

Public Sub 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

End Sub

This is a similar general approach to yours, and it works quite well. I
expect your code would have been somewhat different had you known that 05 is
the year. By the way, I assume strNextNum in the Right function is intended
to be strNextKey. If it is, I think I understand your code; otherwise I am
puzzled.

So the problem is not with the code, but with a potential multi-user
environment. With a single user I would just place the code in the form's On
Current event. When the record is saved (at any of several events, such as
using a command button to send an e-mail, clicking on another tab, moving to
another record, closing the form, etc.) the number is written to the record.
The potential problem comes in a multi-user environment. If two people are
starting a report at the same time, each will see the same number (if the
code is in the On Current event), but after the first person saves the number
is no longer available to the second person, and I get Error 3022. I could
pretty much solve the problem of a multi-user environment if I call the code
from the form's Before Update event, but the number would not be visible to
the user until the record is saved. I could maybe convince the users that
this is OK, but some will forget, and will conclude that the database is not
working, and decide on their own to go back to doing it the old way, and all
of that stuff that comes with people who are reluctant to change the way they
are accustomed to doing things.
I had hoped that with my code Error 3022 would force the database to go back
and get another number, and to let the user know what it had done, but I
don't seem to be able to make this happen. I think I need to trap Error 3022
in the form's Before Update event, then save the record again after the
report gets a new number before proceeding with the rest of the Before Update
event, which performs some data validation.
Another possibility may be to have the number appear in an unbound text box
until the record is saved, after which the number will appear in a text box
bound to the PK field. That may be the best approach, eliminating the need
to trap the error, but I would still want to notify the user by comparing the
bound text box to the unbound one. Now I just need to figure out how to get
the number into the unbound text box.

Bruce,
I am guessing that the format of your key is:
RPT - an identifier that this is a report and will not change
05- - Denotes the month of the report and will only change when the month
changes
01 - An incrementing number to create a unique key

If the above is correct, then this might work for you:

Function GetNextRptKey( ) As String
dim IntNexNum as Integer
dim strNextKey as String

strNextKey = DMAX([PrimaryKey],"MyTable")
intNextNum = Cint(Right(strNextNum),2) + 1
GetNextKey = Left(strNextKey,7) & Format(intNextNum,"00")
End Function

I have code that assigns a primary key field in a particular format. The
primary key is the report number (report in the generic sense, that is, not
an Access object) in the format RPT 05-01, RPT 05-02, and so forth. That is
fine, as long as there is only one user. While multiple users are not
likely, it could happen, so I attempted something I got from Roger Carlson's
site to avoid duplicates. There is a function something like this:

Function IncrementRPT(DataErr)

If DataErr = 3022 Then
Call AssignNumber
IncrementRPT = acDataErrContinue
End If

End Function

AssignNumber is a sub that uses DMax and other manipulations to produce a
report number. It works as expected.

To go with this, in the Form's Error event I have:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Response = IncrementRPT(DataErr)

End Sub

Both pieces of code have error handling, not included here. Also, there is
a message box in the Error event advising the user that the number will
change.

However, it doesn't seem to work as I would hope. One difficulty I am
having trying to grasp this is that I don't really understand the form's
Error event. My form has a Before Update event that performs some validation
(making sure all required fields are completed). I have several ways of
saving the record (causing Before Update to run). If, for instance, I use a
command button to save the record, error 3022 (duplicate PK) is a command
button error. I know this because my error handling identifies the event in
which the error occurs. I can't seem to find a way for the database to
determine that the PK is already in use, and to go back for another one.
Perhaps I need to refresh the form, or something like that, so that the new
number appears? If I could see the message box (in the form's Error event)
advising the user that there is a new number I would be more confident about
this. Can anybody help get me on track here? I think I may be getting
close, but something is missing.

 

[Klatuu]

Bruce,
Glad you got most of your problem solved. My code was done on the fly and
certainly could use some twiking. I wonder, to appease the users, that you
go ahead and generate the number as you planned, then perhaps in the Before
Update event of the form, you do a Dlookup to make sure another user did not
already use the number, the do the increment stuff, etc.?

Thanks for the reply. I noticed in the code i provided that I sometimes used
"RFI" where it should have been "RPT". Actually, 05 is the year, and -01
etc. is the sequence. There have never been more than about 30 of these
reports in any year. In 2006 the sequence will start over with RPT 06-01.
This is the code, provided in this group by Allen Browne, with some
additional assistance and explanations by Tim Ferguson:

Public Sub 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

End Sub

This is a similar general approach to yours, and it works quite well. I
expect your code would have been somewhat different had you known that 05 is
the year. By the way, I assume strNextNum in the Right function is intended
to be strNextKey. If it is, I think I understand your code; otherwise I am
puzzled.

So the problem is not with the code, but with a potential multi-user
environment. With a single user I would just place the code in the form's On
Current event. When the record is saved (at any of several events, such as
using a command button to send an e-mail, clicking on another tab, moving to
another record, closing the form, etc.) the number is written to the record.
The potential problem comes in a multi-user environment. If two people are
starting a report at the same time, each will see the same number (if the
code is in the On Current event), but after the first person saves the number
is no longer available to the second person, and I get Error 3022. I could
pretty much solve the problem of a multi-user environment if I call the code
from the form's Before Update event, but the number would not be visible to
the user until the record is saved. I could maybe convince the users that
this is OK, but some will forget, and will conclude that the database is not
working, and decide on their own to go back to doing it the old way, and all
of that stuff that comes with people who are reluctant to change the way they
are accustomed to doing things.
I had hoped that with my code Error 3022 would force the database to go back
and get another number, and to let the user know what it had done, but I
don't seem to be able to make this happen. I think I need to trap Error 3022
in the form's Before Update event, then save the record again after the
report gets a new number before proceeding with the rest of the Before Update
event, which performs some data validation.
Another possibility may be to have the number appear in an unbound text box
until the record is saved, after which the number will appear in a text box
bound to the PK field. That may be the best approach, eliminating the need
to trap the error, but I would still want to notify the user by comparing the
bound text box to the unbound one. Now I just need to figure out how to get
the number into the unbound text box.

Bruce,
I am guessing that the format of your key is:
RPT - an identifier that this is a report and will not change
05- - Denotes the month of the report and will only change when the month
changes
01 - An incrementing number to create a unique key

If the above is correct, then this might work for you:

Function GetNextRptKey( ) As String
dim IntNexNum as Integer
dim strNextKey as String

strNextKey = DMAX([PrimaryKey],"MyTable")
intNextNum = Cint(Right(strNextNum),2) + 1
GetNextKey = Left(strNextKey,7) & Format(intNextNum,"00")
End Function

I have code that assigns a primary key field in a particular format. The
primary key is the report number (report in the generic sense, that is, not
an Access object) in the format RPT 05-01, RPT 05-02, and so forth. That is
fine, as long as there is only one user. While multiple users are not
likely, it could happen, so I attempted something I got from Roger Carlson's
site to avoid duplicates. There is a function something like this:

Function IncrementRPT(DataErr)

If DataErr = 3022 Then
Call AssignNumber
IncrementRPT = acDataErrContinue
End If

End Function

AssignNumber is a sub that uses DMax and other manipulations to produce a
report number. It works as expected.

To go with this, in the Form's Error event I have:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Response = IncrementRPT(DataErr)

End Sub

Both pieces of code have error handling, not included here. Also, there is
a message box in the Error event advising the user that the number will
change.

However, it doesn't seem to work as I would hope. One difficulty I am
having trying to grasp this is that I don't really understand the form's
Error event. My form has a Before Update event that performs some validation
(making sure all required fields are completed). I have several ways of
saving the record (causing Before Update to run). If, for instance, I use a
command button to save the record, error 3022 (duplicate PK) is a command
button error. I know this because my error handling identifies the event in
which the error occurs. I can't seem to find a way for the database to
determine that the PK is already in use, and to go back for another one.
Perhaps I need to refresh the form, or something like that, so that the new
number appears? If I could see the message box (in the form's Error event)
advising the user that there is a new number I would be more confident about
this. Can anybody help get me on track here? I think I may be getting
close, but something is missing.

 

[BruceM]

I have not been explaining myself well. Assigning the number was never the
problem. That works perfectly in a single-user environment, but sometimes it
could be a multi-user environment, in which case I need to do what you
suggest and check the number. Actually, Error 3022 is for a duplicate PK, so
I tried to use code as mentioned earlier to use the form's Error event to
assign a new number in the event of Error 3022. However, I cannot get it to
work. That was the problem that led me to post in the first place. I think
I am going to use code to assign the number to an unbound text box in the
form's Current event, then use the same code to assign it to a bound text box
in the form's Before Update event. After doing that the code will compare
the two text boxes. If they are different the user will be advised that the
number has changed. I think that will work, although I wish I could use
Error 3022 to accomplish what I need. Thanks for taking the time to reply.

Bruce,
Glad you got most of your problem solved. My code was done on the fly and
certainly could use some twiking. I wonder, to appease the users, that you
go ahead and generate the number as you planned, then perhaps in the Before
Update event of the form, you do a Dlookup to make sure another user did not
already use the number, the do the increment stuff, etc.?

Thanks for the reply. I noticed in the code i provided that I sometimes used
"RFI" where it should have been "RPT". Actually, 05 is the year, and -01
etc. is the sequence. There have never been more than about 30 of these
reports in any year. In 2006 the sequence will start over with RPT 06-01.
This is the code, provided in this group by Allen Browne, with some
additional assistance and explanations by Tim Ferguson:

Public Sub 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

End Sub

This is a similar general approach to yours, and it works quite well. I
expect your code would have been somewhat different had you known that 05 is
the year. By the way, I assume strNextNum in the Right function is intended
to be strNextKey. If it is, I think I understand your code; otherwise I am
puzzled.

So the problem is not with the code, but with a potential multi-user
environment. With a single user I would just place the code in the form's On
Current event. When the record is saved (at any of several events, such as
using a command button to send an e-mail, clicking on another tab, moving to
another record, closing the form, etc.) the number is written to the record.
The potential problem comes in a multi-user environment. If two people are
starting a report at the same time, each will see the same number (if the
code is in the On Current event), but after the first person saves the number
is no longer available to the second person, and I get Error 3022. I could
pretty much solve the problem of a multi-user environment if I call the code
from the form's Before Update event, but the number would not be visible to
the user until the record is saved. I could maybe convince the users that
this is OK, but some will forget, and will conclude that the database is not
working, and decide on their own to go back to doing it the old way, and all
of that stuff that comes with people who are reluctant to change the way they
are accustomed to doing things.
I had hoped that with my code Error 3022 would force the database to go back
and get another number, and to let the user know what it had done, but I
don't seem to be able to make this happen. I think I need to trap Error 3022
in the form's Before Update event, then save the record again after the
report gets a new number before proceeding with the rest of the Before Update
event, which performs some data validation.
Another possibility may be to have the number appear in an unbound text box
until the record is saved, after which the number will appear in a text box
bound to the PK field. That may be the best approach, eliminating the need
to trap the error, but I would still want to notify the user by comparing the
bound text box to the unbound one. Now I just need to figure out how to get
the number into the unbound text box.

Bruce,
I am guessing that the format of your key is:
RPT - an identifier that this is a report and will not change
05- - Denotes the month of the report and will only change when the month
changes
01 - An incrementing number to create a unique key

If the above is correct, then this might work for you:

Function GetNextRptKey( ) As String
dim IntNexNum as Integer
dim strNextKey as String

strNextKey = DMAX([PrimaryKey],"MyTable")
intNextNum = Cint(Right(strNextNum),2) + 1
GetNextKey = Left(strNextKey,7) & Format(intNextNum,"00")
End Function

:

I have code that assigns a primary key field in a particular format. The
primary key is the report number (report in the generic sense, that is, not
an Access object) in the format RPT 05-01, RPT 05-02, and so forth. That is
fine, as long as there is only one user. While multiple users are not
likely, it could happen, so I attempted something I got from Roger Carlson's
site to avoid duplicates. There is a function something like this:

Function IncrementRPT(DataErr)

If DataErr = 3022 Then
Call AssignNumber
IncrementRPT = acDataErrContinue
End If

End Function

AssignNumber is a sub that uses DMax and other manipulations to produce a
report number. It works as expected.

To go with this, in the Form's Error event I have:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Response = IncrementRPT(DataErr)

End Sub

Both pieces of code have error handling, not included here. Also, there is
a message box in the Error event advising the user that the number will
change.

However, it doesn't seem to work as I would hope. One difficulty I am
having trying to grasp this is that I don't really understand the form's
Error event. My form has a Before Update event that performs some validation
(making sure all required fields are completed). I have several ways of
saving the record (causing Before Update to run). If, for instance, I use a
command button to save the record, error 3022 (duplicate PK) is a command
button error. I know this because my error handling identifies the event in
which the error occurs. I can't seem to find a way for the database to
determine that the PK is already in use, and to go back for another one.
Perhaps I need to refresh the form, or something like that, so that the new
number appears? If I could see the message box (in the form's Error event)
advising the user that there is a new number I would be more confident about
this. Can anybody help get me on track here? I think I may be getting
close, but something is missing.