Re: Number + 1

Using the same number in a variety of databases may be a reason for storing
the extended number. However, there is no need to store both the
incremented portion and the extended portion. Store the extended number in
one field. You could use something like the following in the form's Current
event (or in whatever event you choose). I have not included the letter
prefix since I do not know the rule for using one prefix over another. If
it is the same prefix every time, there is no rationale whatever that I can
imagine for storing it.

Dim strWhere As String, strYrMo As String
Dim varResult As Variant

strYrMo = Format(Me.dmrdate, "yymm")

If Me.NewRecord Then
strWhere = "[dmrincrnum] Like """ & strYrMo & "*"""
varResult = DMax("[dmrincrnum]", "TBLdmr", strWhere)

If IsNull(varResult) Then
Me.dmrincrnum = strYrMo & "0001"
Else
Me.dmrincrnum = Left(varResult, 4) & Format(Val(Right(varResult,
4)) + 1, "0000")
End If
End If

The value for dmrdate is assigned automatically as today's date and time, so
the line:
strYrMo = Format(Me.dmrdate, "yymm")
could be:
strYrMo = Format(Date, "yymm")

If you need the date stored, fine, but if not then dmrdate serves no
purpose.

If there is ever an occasion when you would enter a record for a previous
date you will need to expose the dmrdate field in a text box so that it can
be edited. In that case you may need to run the code in the After Update
event for the dmrdate text box.

Also, in a multi-user environment you will need to take precautions against
two users entering records at the same time and therefore attempting to use
the same number. The simplest way probably is to assign the number in the
form's Before Update event (at the end of the record's creation).

However, I won't go down those paths just now. I have suggested several
ways of doing exactly what you need. It is up to you to try a few things.
Also, study Help so that you can learn more about DMax, Format, and other
functions that are part of the code.

"dcc15 via AccessMonster.com" <u38772@uwe> wrote in message
news:7ad8aad4cdd84@xxxxxx
I really appreciate your help,
My experience not only with my database but with other customers &
suppliers
that use similar numbering schemes, is various problems/confusions have
occurred from information extracted from databases in the form of
issuing/using the "Document Number" in various formats due to the Doc# not
being stored in its entirety. Several departments/users will be querying
the
database for information/reports and while I can make "standard" reports
build this number it would still leave simple querying by other users (no
time/unable to build the complete number, including me) with incomplete
doc#'s and this does cause problems with the various processes that
utilize
data contained within the DB. With this said, what do recommend the
best/easiest method of "saving" the "built" ID number. I sort-of see what
is
being done in your suggested "expression". I'm hoping that whatever I end
up
with the user will be able to click a button and a data entry form will
open
with the next (complete) ID number (and current date) entered and saved to
the table when the data entry form is complete. At the risk of confusing
myself could the number of instances of the date be "counted" +1 to obtain
the increment portion of the ID? I will break out my SQL book so I can
better
understand what you have been suggesting. Thanks for the help!

BruceM wrote:
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]
Format([dmrnum],
"0000")

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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200711/1



.

Loading