Replies inline.
Thanks BruceM,
Based on some of the info you gave me I have gotten closer to my desired
[quoted text clipped - 13 lines]
dmrincrnum: Data Type= Number, Format= General
dmrdocnum: Data Type= Text, Format= None
Format doesn't matter at the table level for your purposes with this
situation.
Data Entry Form (FRMdmr) bound text boxes:
dmrdate: Default Value is: Now() (Not visible)
[quoted text clipped - 5 lines]
dmrincrnum is not in the table, it is in the query based on the table. I
called the field ConcatNumber when I described the query. The value is
calculated; calculations are usually best done on the fly rather than by
storing the value. More on this in a moment.
Result: By opening FRMdmr, a new record is created in TBLdmr inserting
the
current date "dmrdate", increasing the increment number by 1
"dmrincrnum"
[quoted text clipped - 7 lines]
time is it!). If not practical 5 digits will last about 100 years.
Thanks Again!
The expression I provided will do exactly what you require. Here is some
more explanation.
The DMax function has three parts. The first two, expression and domain
(in
this case, field and table) are required. The third, criteria, is
optional.
However, you need the criteria. I added it to my suggestion (it is the
part
starting with Year and going through intMo).
In the context of the expression:
Dim intYr As Integer, intMo As Integer
intYr = Year(Me.dmrdate)
intMo = Month(Me.dmrdate)
If Me.NewRecord Then
Me.dmrincrnum = Nz(DMax("dmrincrnum", "TBLdmr", _
"Year([dmrdate]) = " & _
intYr & " AND Month([dmrdate]) = " & _
intMo), 0) + 1
First, you are defining the variables intYr and intMo as Integers (Dim
...).
Next you are setting the value of these integers to the Year portion of
your
date field and the Month portion of your date field. Then Access takes
the
largest value (that's what DMax does) from the dmrincrnum field in TBLdmr
for the record in which the Year portion and the Month portion of the
dmrdate field equals the Year and Month portions of the current record's
dmrdate field. You may have the following records:
dmrdate dmrincrnum
10/2/07 1
10/4/07 2
11/2/07 1
11/4/07 2
The expression finds the records with today's month and year (the criteria
part of the expression), and finds from among those records the one with
the
largest dmrincrnum. That value is 2, so the next record is 3.
Re-read the part I wrote about creating a query based on the table, and
doing the calculation there. Since you are already storing the date field
and the incremented number, you would just be storing the same information
again. This is not relational database best practice, and can lead to a
variety of complications down the road.
You may choose to use the Default Value of the text box bound to
dmrincrnum
to increment the value, maybe something like:
=Nz(DMax("dmrincrnum","TBLdmr","Year([dmrdate]) = " & Year(Now()) & " AND
Month([dmrdate]) = " & Month(Now())),0)+1
It may be the right choice to store the concatenated value if you are
using
the numeric portion of it for other records (or there may be other
circumstances in which storing the value makes sense). It almost
certainly
does not make sense to store the prefix in any case, though. If you take
this route, use the expression I suggested for the query field
ConcatNumber
as the Default Value of the control bound to dmrDocNum.
Do not store the month and date in separate fields. Instead, extract
them
from the date field. In the After Update event for the text box bound
to
[quoted text clipped - 30 lines]
limited to 9999 records each month. I expect that is plenty, but I
should
mention it anyhow.
