You can add another criteria to the Where argument of DMax:
If Me.NewRecord Then
Me.NumberField = Nz(DMax("[NumberField]", "YourTable", _
"Year([DateField]) = " & Year(Date()) _
"And [Category] = """ & Me.Category & """"), 0) + 1
End If
You could place this code in the form's Before Insert event or Current
event.
This assumes that NumberField is the name of the field containing the
incrementing number, and that there is a date field and a Category field
(text) in the form's record source. If the form is based on YourTable
(whatever you have named it), typically DateField and Category would appear
on the form. The expression is saying something like this:
"Find the largest number in NumberField in a record from YourTable where the
year is the same as the current year and the Category is the same as the one
in the current record, and add one to that NumberField value."
In an unbound text box on the form you could have as the Control Source:
= [Category] & Format([NumberField],"0000")
You could instead have the expression in the form's Record Source query:
Combined: [Category] & Format([NumberField],"0000")
then bind a text box to Combined.
Using DefaultValue has some advantages over testing for NewRecord. If the
user backs out without adding any data there is nothing to undo with
DefaultValue, while I believe you would need to undo data created with If
Me.NewRecord. The code would be something like this in the form's Current
or Before Insert event:
Me.txtNumberField.DefaultValue = Nz(DMax("[NumberField]", "YourTable", _
"Year([DateField]) = " & Year(Date()) _
"And [Category] = """ & Me.Category & """"), 0) + 1
Note that txtNumberField is a text box bound to NumberField.
In any case you will need to take precautions against duplicate numbers in a
multi-user environment. One way is to hold off on creating the new number
until the form's Before Update event. In many cases this will be enough.
The form's After Update event is too late, since the update has already
occurred. There are a number of approaches you could take, depending on the
details.
If you have difficulties, post more details of your situation. I had to do
some reading between the lines.
punitha said:
hey BruceM,
i was looking for the same solution and it worked great for me too. but
then
there's only one thing that i'm thinking of...
waht if i have 2 criterias to be considered... like year, which is working
well now... and category i have 2 categories of sample coming "R" & "P"
and
they each start with number 0001 in the beginning of each year. do i still
put the code in the DateRec after Update event or the form's?
thank you in advance
BruceM said:
Glad to hear you got it working. There are ways to create just about any
incrementing number or combination of text and numbers, as long as there
are
specific rules to govern the process. By the way, a "mishmash" of text
and
numbers is just text. To increment the number portion you would need to
have Access handle it as a number even though it is stored as text. It's
not all that difficult to do, but it does add an extra layer or two to
the
code.
OK, Bruce M, what you've given me worked just fine.
You're the greatest.
Thank you so much.
--
AusTexRich
:
Hi,
Thanks again for hanging in there with me.
Do you have a date field in the record? Originally no. I'll add one.
It
appears best to do so.
Is there a primary key field or combination of fields? No primary key
field.
My concern was how to add numbers to a field that had a mishmash of
numbers
and text rather than a pure number.
For example...
Field 1 (text)
1
2a
3
3c
I believe from what you given me will work. I'll give it a try anyway
Thanks
--
AusTexRich
:
It will start over at 1 next year. Note this part of the code for
DMax,
which is the optional criteria:
"Year([DateField]) = " & Year([DateField])
If the DMax statement just included the following:
DMax("NumberField", "YourTable") + 1
then Access would find the largest value for NumberField in
YourTable
and
add 1 to it.
However, with the optional criteria added:
DMax("NumberField", "YourTable","Year([DateField]) = " &
Year([DateField]) +
1
Access will find the largest value for NumberField in YourTable for
which
the Year portion of DateField is the same as the Year portion of
DateField
for the current record, and add 1 to it.
The Nz in the original code allows for the fact that for the year's
first
record there will be no records in which Year from DateField equals
Year
from the current record. In that case DMax will produce a null
value.
Nz
replaces the null with a 0, to which 1 is added, resulting in a
value
of 1
in NumberField for the first record of any year.
Do you have a date field in the record?
Is there a primary key field or combination of fields?
My original post includes the reasons why I ask these questions.
The
year
can be added to the number and stored, but first I'm trying to find
out
if
it's necessary. If it isn't you can just concatenate the two-digit
year
with the number as I described.
Hi,
Great stuff. I very appreciative of your help. You guys always
have
some
great stuff.
My problem is adding new records to a table of preexisting data
that
is
grouped by year (kind of) and each prior year has its own count
beginning
at
001 to the last record for the year.
If I use DMax I get the next record 539. Great for this year, but,
what
about next year? I'd like the "counter" to reset to "001".
I was hoping that if I add a text field to capture the year "08" I
can
base
a numbering system on that and start at "001".
--
AusTexRich
:
Assuming the year is stored somewhere in the record (as part of a
date
field), you could do something like this in the After Update
event
for
the
control bound to the date field:
If Me.NewRecord Then
Me.NumberField = Nz(DMax("NumberField", "YourTable", _
"Year([DateField]) = " & Year([DateField])), 0) + 1
End If
Use your own field and table names, of course.
To display the number, you could do something like this as the
Control
Source of an unbound text box:
=Format([DateField],"yy") & "-" & Format([NumberField],"000")
Keep in mind that NumberField cannot be used as the primary key,
since it
will start over from 0 each year. If you already have a primary
key
that
is
not a problem. If this number is to be the primary key you could
add the
year to the beginning of the number, but that involves a few more
steps,
so
I will wait until I hear more about the details. Some would
argue
that
adding the year to the number is storing the year redundantly,
but
without
that you would need either a compound primary key or a separate
primary
key
field.
The general point here is that you can build an automatically
incrementing
number into your database. As always, the details dictate the
specifics.
message
I want to create a numbering system, in a three character
format,
in a
form
starting at "001" and so on. Now, because the of the
formatting
of the
prior
data from an Excel file, I've had to distinguish one year's
records
from
the
next with a text field "08" (year). So when a new record is
added, the
text
"08" is added and then the count begins at "001". How do I do
that?
table fields are: [I_period] - "08", [row_num] - "001", "002,
etc,.
Thanks all.
