On Thu, 3 Jun 2010 16:22:09 -0700, Sharon_wv
wrote:

Ken & Jeff -
I totally understand your logic for wanting me to stop. However, let me
give you more details about the records that we will be capturing. With each
record, there will be a corresponding file (many binders, etc.) that go along
with the record. These binders, etc. will be filed in a large library
according to their tracking numbers. Each track of hard copy records, if my
plan for numbering works, will be filed in different areas. In order to make
finding records easier, I was hoping to assign sequential tracking numbers
for each track of records (Category A and Category B). So it wasn't just for
counting purposes, but for overall organizational purposes of both physical
records and digital records. BUT, I suppose that assigning the A-xxxx vs.
B-xxxx will do just that as well, without having to violate the integrity of
a database. I can also just assign the numbers to each record after entering
all the data as well, instead of having Access do it for me. Once I have
them all logged in Access, it will be simple to do something like that.

I really really really appreciate your expertise in leading me in the right
direction, gentlemen! So sorry to take up so much of your valuable time!!

Just to reiterate Ken and Jeff's point:

You do NOT need one field containing the category and the sequential number.

You need *two different fields* - one of them for the category, the other for
the number. Combining them into one field is neither necessary nor beneficial.
You can very easily *display* them together; just use a calculated field in a
Query or in the Control Source of a form or report textbox set to

[Category] & Format([Seqno], "0000")

to display A0001, B3128 or the like.

To assign category-specific sequential numbers, you'll need to use VBA code in
the data entry form (and yes, you will need to use a Form). You could use a
combo box to select the category (this will allow more categories if that
should happen in the future); in the combo box's AfterUpdate event use code
like

Private Sub cboCategory_AfterUpdate()
'Only assign a new number if it's the new record; one has not yet
'been assigned already; and the user did select a category
If Me.NewRecord AND IsNull(me![Seqno]) And Not IsNull(Me!cboCategory) Then
Me![Seqno] = NZ(DMax("[Seqno]", "[yourtablename]", _
"[Category] = '" & Me!cboCategory & "'")
End If
End Sub

--

John W. Vinson [MVP]