Specific Format for Control Numbers

[Guest]

I am building a database which shall handle one of the regular transactions
in our office. Each transaction recorded shall have a unique 8-digit control
number. Said control number has the following format:

- First two digits is based on the current month as of the entry of the
transaction (i.e. values should be 01 to 12 only)

- Next two digits is the current year (e.g. 06)

- Next digit is a constant "1" (This is the standard code implemented in our
office, for the group which shall use the system)

- Last 3 digits is an incrementing value starting from 001. Note that this
shall reset monthly. I.e., if the first 2 digits of the control number shall
change.

Is MS Access capable of automatically generating control numbers given the
above specified format and conditions? I assume that our numbering scheme
shall be somewhat dependent on the computer clock especially for the first 4
digits. But is it possible to "concatenate" the given values according to our
scheme?

Hoping for your kind assistance. Thank you in advance

 

[Douglas J. Steele]

Yes, it's possible, but it's not recommended. You're trying to store 4
pieces of information in a single field. That's a violation of relational
database design theory, which states that each field should contain a single
piece of information.

I think you'd be better off storing the data in 3 (or 4) separate fields,
and creating a query that has a computed field that concatenates the fields
together into a single field to creat your control number. You'd then use
the query wherever you would otherwise have used the table.

To be able to increment a field such as you need, you'd put logic into your
form's BeforeInsert event to have it calculate the next number to use, based
on the highest number used so far. You can use the DMax function to find out
the highest number used so far. If this is for a multi-user environment, you
may need to get a little more sophisticated than that, to ensure no
collisions on the off chance that two users are trying to create a new
record at the same time.

 

[BruceM]

I have learned a great deal from this forum from a variety of people, and
particularly from MVPs including Douglas. If what I suggest here is
fundamentally flawed I would like to know more about the problems with my
approach.
My thinking here is that control numbers are constructed according to a
variety of schemes. A unique text value is just that, no matter if it is
the name of a street in a city a constructed text value. The code below
could be placed in the form's Current event. MainID is a text field
(indexed, no duplicates) in the table tblTransaction.

If Me.NewRecord Then

Dim strWhere As String
Dim varResult As Variant

strWhere = "MainID Like """ & Format(Date, "mmyy") & "*"""
varResult = DMax("MainID", "tblTransaction", strWhere)

If IsNull(varResult) Then
Me.MainID = Format(Date, "mmyy") & "1001"
Else
Me.MainID = Left(varResult, 5) & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If
End If

In a multi-user environment you would need to take precautions against
duplication errors, which would occur if two users are entering a record
simultaneously. Until the record is saved (either explicitly or by
navigating away from the record) the value evaluated by DMax is the same for
both users. The first user to save the record will be OK, but the second
will receive an error message. One way of handling this is to place the
code in a public sub, and call it in the form's Current event. Use error
handling in the form's Before Update event to identify the error number (I
think it's 3022) by attempting to enter two records at the same time (by two
users in a network copy of the database, for instance). If that error is
encountered, just call the public sub again. Or if the user doesn't need to
see the number while entering the record, you could assign it in the form's
Before Update event, which would greatly reduce the chance of duplication.
You would still want error handling, but it probably wouldn't come up very
often.

 

[BruceM]

Proofreading error:
"A unique text value is just that, no matter if it is the name of a street
in a city a constructed text value"
should have "or" added:
"A unique text value is just that, no matter if it is the name of a street
in a city or a constructed text value"

 

[Guest]

My sincere appreciation for your recommendations regarding this matter. I
shall post a follow-up thread should I have further inquiries. To date, I am
still in the requirements specification and planning phase.

Your professional assistance is very much appreciated, thank you.

 

[Guest]

My sincere appreciation for your recommendations regarding this matter. I
shall post a follow-up thread should I have further inquiries. To date, I am
still in the requirements specification and planning phase.

Your professional assistance is very much appreciated, thank you.