Automatic numbering

Not sure where to post this. I have a database for making occasional
reports. The first report of this year is numbered RPT-05-01, the second
RPT-05-02, etc. My table (tblReport) contains fields for RPT_Number (such as
05-01) and RPT_Sequence (the last two digits of RPT_Number). A form (frmRPT)
is based on the table. I have placed the following code into the form's On
Current event:

If IsNull(Me.RPT_Sequence) Then
Me.RPT_Sequence = Nz(DMax("RPT_Sequence", "tblRPT"), 0) + 1
End If

If IsNull(Me.RPT_Number) Then
Me.RPT_Number = Format(Date, "yy") & "-" & _
Format(Me.RPT_Sequence, "00")
Else: Me.txtRPT_Number.Locked = True
End If

txtRPT_Number is the text box bound to RPT_Number. People can't change the
number after the record is created.

The above code assigns 05-01, 05-02, and so forth in the proper sequence,
and the number is retained when returning to the record, but of course it
will not restart at 06-01 next year. How can I assign the correct number at
the start of next year? In general is there a better way of assigning the
number, or are there any potential problems with the approach beyond what I
have mentioned?

Dim strWhere As String
Dim varResult As Variant

strWhere = "RPT_Sequence Like """" & Format(Date, "yy") & "*"""
varResult = DMax("RPT_Sequence", "tblRPT", strWhere)

If IsNull(varResult) Then
Me.RPT_Sequence = Format(Date, "yy") & "-01"
Else
Me.RPT_Sequence = Left(varResult, 3) & _
Format(Val(Right(varResult,2))+1, "00")
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BruceM" wrote in message
...
Not sure where to post this. I have a database for making occasional
reports. The first report of this year is numbered RPT-05-01, the second
RPT-05-02, etc. My table (tblReport) contains fields for RPT_Number (such
as
05-01) and RPT_Sequence (the last two digits of RPT_Number). A form
(frmRPT)
is based on the table. I have placed the following code into the form's
On
Current event:

If IsNull(Me.RPT_Sequence) Then
Me.RPT_Sequence = Nz(DMax("RPT_Sequence", "tblRPT"), 0) + 1
End If

If IsNull(Me.RPT_Number) Then
Me.RPT_Number = Format(Date, "yy") & "-" & _
Format(Me.RPT_Sequence, "00")
Else: Me.txtRPT_Number.Locked = True
End If

txtRPT_Number is the text box bound to RPT_Number. People can't change
the
number after the record is created.

The above code assigns 05-01, 05-02, and so forth in the proper sequence,
and the number is retained when returning to the record, but of course it
will not restart at 06-01 next year. How can I assign the correct number
at
the start of next year? In general is there a better way of assigning the
number, or are there any potential problems with the approach beyond what
I
have mentioned?

=?Utf-8?B?QnJ1Y2VN?= wrote in
:

How can I assign the correct number at
the start of next year? In general is there a better way of assigning
the number, or are there any potential problems with the approach
beyond what I have mentioned?

Yes to both.

My table (tblReport) contains fields for RPT_Number (such as
05-01) and RPT_Sequence (the last two digits of RPT_Number).

The first problem with this is that it's only a matter of time until you
have a RTP_Number that says 05_23 and a RPT_Sequence that says 32 -- oh,
and a short time after that the report date will say 2004-xx-xx and the
Number will say 06-yy; and so on.

Take some time to sort out second normal form: this means that a single
fact is recorded exactly one time. If the "05" refers to the date of the
report, and assuming you have that date stored, then it's child's play to
get it out in a query:

ShortYearNumber: Year(CreateDate) Mod 100

This leaves the Sequence number in the second field as a plain integer.
You can display the entire report number like this:

ReportNumber: "RPT_" & Format(Year(CreateDate) Mod 100, "00") &
"_" & Format(Sequence, "00")

Allocating a new sequence number is pretty much as you have done, except
for using a criterion on the DMax() function:

' calculate the current year from the record
varTemp = DMax("Sequence","MyTable", _
"Year(CreateDate)=" & Year(Me!CreateDate))

If IsNull(varTemp) Then
NextSequence = 1
Else
NextSequence = varTemp + 1
End If


Hope that helps

Tim F

I appreciate the quick response. I had to do some tweaking, but the end
result is that it seems to work. When I changed my computer clock to next
year I was suddenly late for just about everything (according to Outlook
Calendar), but the numbers fell in line starting with 06-01.
In the line strWhere = "RPT_Sequence Like """" & Format(Date, "yy") & "*"""
there seemed to be an extra quote after Like, so I removed one.
RPT_Number is the concatenated value that is the official document number
(05-01, 05-02, etc.). RPT_Sequence is the incrementing value that forms the
last two digits of RPT_Number. RPT_Sequence is a number field. I changed
RPT_Sequence to RPT_Number everywhere in your code. Apparently I do not need
the RPT_Sequence field, which just incremented 01, 02, 03, etc.
I have tried to find out more about Like (in the strWhere = ... line), but
Help isn't very helpful, and I cannot find a way to frame the search in
Google groups (because Like is a very common word, I suppose). Not to impose
on your generously offered assistance, but could you either say a little more
about the strWhere = line or point me to a resource that can provide some
clarity. Thanks again.

"Allen Browne" wrote:

Dim strWhere As String
Dim varResult As Variant

strWhere = "RPT_Sequence Like """" & Format(Date, "yy") & "*"""
varResult = DMax("RPT_Sequence", "tblRPT", strWhere)

If IsNull(varResult) Then
Me.RPT_Sequence = Format(Date, "yy") & "-01"
Else
Me.RPT_Sequence = Left(varResult, 3) & _
Format(Val(Right(varResult,2))+1, "00")
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BruceM" wrote in message
...
Not sure where to post this. I have a database for making occasional
reports. The first report of this year is numbered RPT-05-01, the second
RPT-05-02, etc. My table (tblReport) contains fields for RPT_Number (such
as
05-01) and RPT_Sequence (the last two digits of RPT_Number). A form
(frmRPT)
is based on the table. I have placed the following code into the form's
On
Current event:

If IsNull(Me.RPT_Sequence) Then
Me.RPT_Sequence = Nz(DMax("RPT_Sequence", "tblRPT"), 0) + 1
End If

If IsNull(Me.RPT_Number) Then
Me.RPT_Number = Format(Date, "yy") & "-" & _
Format(Me.RPT_Sequence, "00")
Else: Me.txtRPT_Number.Locked = True
End If

txtRPT_Number is the text box bound to RPT_Number. People can't change
the
number after the record is created.

The above code assigns 05-01, 05-02, and so forth in the proper sequence,
and the number is retained when returning to the record, but of course it
will not restart at 06-01 next year. How can I assign the correct number
at
the start of next year? In general is there a better way of assigning the
number, or are there any potential problems with the approach beyond what
I
have mentioned?

I had imagined originally that when a new year started RPT_Sequence would
restart from 01. When asking a question I usually try to show I have been
working on the problem, even if I suspect I am not on track. I have
implemented another suggestion that seems to work (from Allen Browne), but I
am interested in your approach too. In the future I need to design a similar
database except with the department code in the number (D-05-01, D-05-02,
S-05-01, D-05-03, Q-05-01) so maybe your suggestion will come into play yet,
except that I am having trouble understanding it. As I understand your
suggestion, CreateDate is a field containing the date the report was created.
That would have an advantage over Format(Date(),"yy") in that a handwritten
report created on the last day of the year but entered into the system in the
new year would contain the correct report date. How does MOD 100 fit in? As
I understand it MOD is a sort of whole number division sign.
On another matter, I usually use autonumber PKs, but in this case I wonder
if there is any advantage (or disadvantage) to using the concatenated number
(RPT_Number) as the PK. I have no plans just now for relationships with
other tables, but am I correct in thinking that the FK (if there is one)
would have to be of data type Text? If so, any potential problems with that?


"Tim Ferguson" wrote:

=?Utf-8?B?QnJ1Y2VN?= wrote in
:

How can I assign the correct number at
the start of next year? In general is there a better way of assigning
the number, or are there any potential problems with the approach
beyond what I have mentioned?

Yes to both.

My table (tblReport) contains fields for RPT_Number (such as
05-01) and RPT_Sequence (the last two digits of RPT_Number).

The first problem with this is that it's only a matter of time until you
have a RTP_Number that says 05_23 and a RPT_Sequence that says 32 -- oh,
and a short time after that the report date will say 2004-xx-xx and the
Number will say 06-yy; and so on.

Take some time to sort out second normal form: this means that a single
fact is recorded exactly one time. If the "05" refers to the date of the
report, and assuming you have that date stored, then it's child's play to
get it out in a query:

ShortYearNumber: Year(CreateDate) Mod 100

This leaves the Sequence number in the second field as a plain integer.
You can display the entire report number like this:

ReportNumber: "RPT_" & Format(Year(CreateDate) Mod 100, "00") &
"_" & Format(Sequence, "00")

Allocating a new sequence number is pretty much as you have done, except
for using a criterion on the DMax() function:

' calculate the current year from the record
varTemp = DMax("Sequence","MyTable", _
"Year(CreateDate)=" & Year(Me!CreateDate))

If IsNull(varTemp) Then
NextSequence = 1
Else
NextSequence = varTemp + 1
End If


Hope that helps

Tim F

I have to take back what I waid earlier. It works up to a point, but
RPT_Number keeps changing for the same record. The problem is with the On
Current event, which reassigns the number every time the record is current.
Maybe I will need to exit the sub if there is already a value in that field.

"Allen Browne" wrote:

Dim strWhere As String
Dim varResult As Variant

strWhere = "RPT_Sequence Like """" & Format(Date, "yy") & "*"""
varResult = DMax("RPT_Sequence", "tblRPT", strWhere)

If IsNull(varResult) Then
Me.RPT_Sequence = Format(Date, "yy") & "-01"
Else
Me.RPT_Sequence = Left(varResult, 3) & _
Format(Val(Right(varResult,2))+1, "00")
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BruceM" wrote in message
...
Not sure where to post this. I have a database for making occasional
reports. The first report of this year is numbered RPT-05-01, the second
RPT-05-02, etc. My table (tblReport) contains fields for RPT_Number (such
as
05-01) and RPT_Sequence (the last two digits of RPT_Number). A form
(frmRPT)
is based on the table. I have placed the following code into the form's
On
Current event:

If IsNull(Me.RPT_Sequence) Then
Me.RPT_Sequence = Nz(DMax("RPT_Sequence", "tblRPT"), 0) + 1
End If

If IsNull(Me.RPT_Number) Then
Me.RPT_Number = Format(Date, "yy") & "-" & _
Format(Me.RPT_Sequence, "00")
Else: Me.txtRPT_Number.Locked = True
End If

txtRPT_Number is the text box bound to RPT_Number. People can't change
the
number after the record is created.

The above code assigns 05-01, 05-02, and so forth in the proper sequence,
and the number is retained when returning to the record, but of course it
will not restart at 06-01 next year. How can I assign the correct number
at
the start of next year? In general is there a better way of assigning the
number, or are there any potential problems with the approach beyond what
I
have mentioned?

No: don't use Form_Current. You don't need to assign the number once it
exists.

Use Form_BeforeUpdate. This is the last possible moment you can assign the
value before the record is saved (which reduces the change of duplicates
being given to different users attempting to enter new records at the same
time.)

You only need to do this is if it a new record, so:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
'put the code in here.
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BruceM" wrote in message
...
I have to take back what I waid earlier. It works up to a point, but
RPT_Number keeps changing for the same record. The problem is with the On
Current event, which reassigns the number every time the record is
current.
Maybe I will need to exit the sub if there is already a value in that
field.

"Allen Browne" wrote:

Dim strWhere As String
Dim varResult As Variant

strWhere = "RPT_Sequence Like """" & Format(Date, "yy") & "*"""
varResult = DMax("RPT_Sequence", "tblRPT", strWhere)

If IsNull(varResult) Then
Me.RPT_Sequence = Format(Date, "yy") & "-01"
Else
Me.RPT_Sequence = Left(varResult, 3) & _
Format(Val(Right(varResult,2))+1, "00")
End If


"BruceM" wrote in message
...
Not sure where to post this. I have a database for making occasional
reports. The first report of this year is numbered RPT-05-01, the
second
RPT-05-02, etc. My table (tblReport) contains fields for RPT_Number
(such
as
05-01) and RPT_Sequence (the last two digits of RPT_Number). A form
(frmRPT)
is based on the table. I have placed the following code into the
form's
On
Current event:

If IsNull(Me.RPT_Sequence) Then
Me.RPT_Sequence = Nz(DMax("RPT_Sequence", "tblRPT"), 0) + 1
End If

If IsNull(Me.RPT_Number) Then
Me.RPT_Number = Format(Date, "yy") & "-" & _
Format(Me.RPT_Sequence, "00")
Else: Me.txtRPT_Number.Locked = True
End If

txtRPT_Number is the text box bound to RPT_Number. People can't change
the
number after the record is created.

The above code assigns 05-01, 05-02, and so forth in the proper
sequence,
and the number is retained when returning to the record, but of course
it
will not restart at 06-01 next year. How can I assign the correct
number
at
the start of next year? In general is there a better way of assigning
the
number, or are there any potential problems with the approach beyond
what
I
have mentioned?

Thanks again. One concern, though, is that this report, while important in
the big picture, is generally issued no more than 30 times per year. This
means that some users will only use it every few months, if that. I can
explain that the number is not assigned until they are done, but they will
forget, and then they will call me and say that they can't enter a report
number, and I will tell them it is assigned when they are done, and they will
mutter and sigh and fill out a report suggesting that they be able to see the
number right away. In fairness, it is not their job to remember
administrative details like that.
What I have done is to place the code into the On Current event, which
produces a RPT_Number value, then I have placed the same code into Before
Update, except that the first line there (after If Me.NewRecord) is to clear
the text box containing RPT_Number (I allowed zero length strings in table
design to make that possible). I have tested this with two users, and it
seems to work. I could probably find a way to generate a message advising
the user that the record number will be changed. It is not likely to occur
often. Similarly, in the very unlikely event that two users attempt to save
a record at the same time, I could probably set the RPT_Number field to not
allow duplicates, and in case of the specific error of trying to assign a
duplicate where it is not allowed I could have the code to assign the number
run again. I would have to figure out which error number that is.
Am I on the right track, or am I taking the long way around or otherwise
causing myself difficulties?

"Allen Browne" wrote:

No: don't use Form_Current. You don't need to assign the number once it
exists.

Use Form_BeforeUpdate. This is the last possible moment you can assign the
value before the record is saved (which reduces the change of duplicates
being given to different users attempting to enter new records at the same
time.)

You only need to do this is if it a new record, so:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
'put the code in here.
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BruceM" wrote in message
...
I have to take back what I waid earlier. It works up to a point, but
RPT_Number keeps changing for the same record. The problem is with the On
Current event, which reassigns the number every time the record is
current.
Maybe I will need to exit the sub if there is already a value in that
field.

"Allen Browne" wrote:

Dim strWhere As String
Dim varResult As Variant

strWhere = "RPT_Sequence Like """" & Format(Date, "yy") & "*"""
varResult = DMax("RPT_Sequence", "tblRPT", strWhere)

If IsNull(varResult) Then
Me.RPT_Sequence = Format(Date, "yy") & "-01"
Else
Me.RPT_Sequence = Left(varResult, 3) & _
Format(Val(Right(varResult,2))+1, "00")
End If


"BruceM" wrote in message
...
Not sure where to post this. I have a database for making occasional
reports. The first report of this year is numbered RPT-05-01, the
second
RPT-05-02, etc. My table (tblReport) contains fields for RPT_Number
(such
as
05-01) and RPT_Sequence (the last two digits of RPT_Number). A form
(frmRPT)
is based on the table. I have placed the following code into the
form's
On
Current event:

If IsNull(Me.RPT_Sequence) Then
Me.RPT_Sequence = Nz(DMax("RPT_Sequence", "tblRPT"), 0) + 1
End If

If IsNull(Me.RPT_Number) Then
Me.RPT_Number = Format(Date, "yy") & "-" & _
Format(Me.RPT_Sequence, "00")
Else: Me.txtRPT_Number.Locked = True
End If

txtRPT_Number is the text box bound to RPT_Number. People can't change
the
number after the record is created.

The above code assigns 05-01, 05-02, and so forth in the proper
sequence,
and the number is retained when returning to the record, but of course
it
will not restart at 06-01 next year. How can I assign the correct
number
at
the start of next year? In general is there a better way of assigning
the
number, or are there any potential problems with the approach beyond
what
I
have mentioned?

If it important that this field is entered and unique, then you really do
need to set these properties for the field in table design:
Required Yes
Allow Zero Length No
Indexed Yes (No Duplicates)

Now that the data is known to be unique and valid, you can do whatever
pleases you in the interface. If you don't like the idea of leaving the
value to the last minute, then use the form's BeforeInsert event so it gets
assigned as soon as the user *starts* to add a new record, instead of at the
end of the process. This does increase the chance of duplicates of course.

If the field is NOT required, you can clear the text box by setting its
value to Null, i.e.:
Me.RPT_Number = Null
which saves you the bother of having test test of both null and a
zero-length-string everywhere in the database where you use this field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BruceM" wrote in message
...
Thanks again. One concern, though, is that this report, while important
in
the big picture, is generally issued no more than 30 times per year. This
means that some users will only use it every few months, if that. I can
explain that the number is not assigned until they are done, but they will
forget, and then they will call me and say that they can't enter a report
number, and I will tell them it is assigned when they are done, and they
will
mutter and sigh and fill out a report suggesting that they be able to see
the
number right away. In fairness, it is not their job to remember
administrative details like that.
What I have done is to place the code into the On Current event, which
produces a RPT_Number value, then I have placed the same code into Before
Update, except that the first line there (after If Me.NewRecord) is to
clear
the text box containing RPT_Number (I allowed zero length strings in table
design to make that possible). I have tested this with two users, and it
seems to work. I could probably find a way to generate a message advising
the user that the record number will be changed. It is not likely to
occur
often. Similarly, in the very unlikely event that two users attempt to
save
a record at the same time, I could probably set the RPT_Number field to
not
allow duplicates, and in case of the specific error of trying to assign a
duplicate where it is not allowed I could have the code to assign the
number
run again. I would have to figure out which error number that is.
Am I on the right track, or am I taking the long way around or otherwise
causing myself difficulties?

"Allen Browne" wrote:

No: don't use Form_Current. You don't need to assign the number once it
exists.

Use Form_BeforeUpdate. This is the last possible moment you can assign
the
value before the record is saved (which reduces the change of duplicates
being given to different users attempting to enter new records at the
same
time.)

You only need to do this is if it a new record, so:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
'put the code in here.
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BruceM" wrote in message
...
I have to take back what I waid earlier. It works up to a point, but
RPT_Number keeps changing for the same record. The problem is with the
On
Current event, which reassigns the number every time the record is
current.
Maybe I will need to exit the sub if there is already a value in that
field.

"Allen Browne" wrote:

Dim strWhere As String
Dim varResult As Variant

strWhere = "RPT_Sequence Like """" & Format(Date, "yy") & "*"""
varResult = DMax("RPT_Sequence", "tblRPT", strWhere)

If IsNull(varResult) Then
Me.RPT_Sequence = Format(Date, "yy") & "-01"
Else
Me.RPT_Sequence = Left(varResult, 3) & _
Format(Val(Right(varResult,2))+1, "00")
End If


"BruceM" wrote in message
...
Not sure where to post this. I have a database for making
occasional
reports. The first report of this year is numbered RPT-05-01, the
second
RPT-05-02, etc. My table (tblReport) contains fields for RPT_Number
(such
as
05-01) and RPT_Sequence (the last two digits of RPT_Number). A form
(frmRPT)
is based on the table. I have placed the following code into the
form's
On
Current event:

If IsNull(Me.RPT_Sequence) Then
Me.RPT_Sequence = Nz(DMax("RPT_Sequence", "tblRPT"), 0) + 1
End If

If IsNull(Me.RPT_Number) Then
Me.RPT_Number = Format(Date, "yy") & "-" & _
Format(Me.RPT_Sequence, "00")
Else: Me.txtRPT_Number.Locked = True
End If

txtRPT_Number is the text box bound to RPT_Number. People can't
change
the
number after the record is created.

The above code assigns 05-01, 05-02, and so forth in the proper
sequence,
and the number is retained when returning to the record, but of
course
it
will not restart at 06-01 next year. How can I assign the correct
number
at
the start of next year? In general is there a better way of
assigning
the
number, or are there any potential problems with the approach beyond
what
I
have mentioned?

Thanks again. I couldn't care less whether the number appears until the end,
but if it is blank I will get calls from users who can't enter a number. The
text box is locked, by the way. Whether or not the field is required may not
be relevant since the value is assigned programatically (it will contain a
value in any case), but I appreciate your comments on Null values and other
field properties, and will certainly apply those suggestions. I plan to use
an autonumber PK, although at this point I do not anticipate that the table
will be part of any relationships. It is essentially a flat database for
recording twenty-five or so items that appear on a report. I can change the
PK later if need be.
Thanks again for the prompt and concise replies to my varied questions.

"Allen Browne" wrote:

If it important that this field is entered and unique, then you really do
need to set these properties for the field in table design:
Required Yes
Allow Zero Length No
Indexed Yes (No Duplicates)

Now that the data is known to be unique and valid, you can do whatever
pleases you in the interface. If you don't like the idea of leaving the
value to the last minute, then use the form's BeforeInsert event so it gets
assigned as soon as the user *starts* to add a new record, instead of at the
end of the process. This does increase the chance of duplicates of course.

If the field is NOT required, you can clear the text box by setting its
value to Null, i.e.:
Me.RPT_Number = Null
which saves you the bother of having test test of both null and a
zero-length-string everywhere in the database where you use this field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BruceM" wrote in message
...
Thanks again. One concern, though, is that this report, while important
in
the big picture, is generally issued no more than 30 times per year. This
means that some users will only use it every few months, if that. I can
explain that the number is not assigned until they are done, but they will
forget, and then they will call me and say that they can't enter a report
number, and I will tell them it is assigned when they are done, and they
will
mutter and sigh and fill out a report suggesting that they be able to see
the
number right away. In fairness, it is not their job to remember
administrative details like that.
What I have done is to place the code into the On Current event, which
produces a RPT_Number value, then I have placed the same code into Before
Update, except that the first line there (after If Me.NewRecord) is to
clear
the text box containing RPT_Number (I allowed zero length strings in table
design to make that possible). I have tested this with two users, and it
seems to work. I could probably find a way to generate a message advising
the user that the record number will be changed. It is not likely to
occur
often. Similarly, in the very unlikely event that two users attempt to
save
a record at the same time, I could probably set the RPT_Number field to
not
allow duplicates, and in case of the specific error of trying to assign a
duplicate where it is not allowed I could have the code to assign the
number
run again. I would have to figure out which error number that is.
Am I on the right track, or am I taking the long way around or otherwise
causing myself difficulties?

"Allen Browne" wrote:

No: don't use Form_Current. You don't need to assign the number once it
exists.

Use Form_BeforeUpdate. This is the last possible moment you can assign
the
value before the record is saved (which reduces the change of duplicates
being given to different users attempting to enter new records at the
same
time.)

You only need to do this is if it a new record, so:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
'put the code in here.
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BruceM" wrote in message
...
I have to take back what I waid earlier. It works up to a point, but
RPT_Number keeps changing for the same record. The problem is with the
On
Current event, which reassigns the number every time the record is
current.
Maybe I will need to exit the sub if there is already a value in that
field.

"Allen Browne" wrote:

Dim strWhere As String
Dim varResult As Variant

strWhere = "RPT_Sequence Like """" & Format(Date, "yy") & "*"""
varResult = DMax("RPT_Sequence", "tblRPT", strWhere)

If IsNull(varResult) Then
Me.RPT_Sequence = Format(Date, "yy") & "-01"
Else
Me.RPT_Sequence = Left(varResult, 3) & _
Format(Val(Right(varResult,2))+1, "00")
End If


"BruceM" wrote in message
...
Not sure where to post this. I have a database for making
occasional
reports. The first report of this year is numbered RPT-05-01, the
second
RPT-05-02, etc. My table (tblReport) contains fields for RPT_Number
(such
as
05-01) and RPT_Sequence (the last two digits of RPT_Number). A form
(frmRPT)
is based on the table. I have placed the following code into the
form's
On
Current event:

If IsNull(Me.RPT_Sequence) Then
Me.RPT_Sequence = Nz(DMax("RPT_Sequence", "tblRPT"), 0) + 1
End If

If IsNull(Me.RPT_Number) Then
Me.RPT_Number = Format(Date, "yy") & "-" & _
Format(Me.RPT_Sequence, "00")
Else: Me.txtRPT_Number.Locked = True
End If

txtRPT_Number is the text box bound to RPT_Number. People can't
change
the
number after the record is created.

The above code assigns 05-01, 05-02, and so forth in the proper
sequence,
and the number is retained when returning to the record, but of
course
it
will not restart at 06-01 next year. How can I assign the correct
number
at
the start of next year? In general is there a better way of
assigning
the
number, or are there any potential problems with the approach beyond
what
I
have mentioned?