How do I auto increment a file No. for diff category ID's

[Guest]

My wife is using Access 2003 to develop a catalog of teaching resources for
her school. She has the basic structure complete but needs to automatically
create a file number for each of the documents (these can be books,
documents, forms, cd's etc.) in each of the 29 categories (CategoryID ~ a
text field) that she has identified. This file no. will be physically fixed
to the teaching resource which will then be filed according to category. She
wants each of the categories file number structure to start at 1 then
increment automatically by 1 as records are added. ie. Category 1, File 1,
2, 3... Category 2, File 1, 2, 3, 4.......etc up to Category 29.
The system will be a single user system.

I've done some searching and Dmax() seem to be the way to go, (I'm probably
wrong there though !!!!), but I need some help with the code.

I'm a relative code 'newbie' so as mush detail (code, syntax, where to
insert the extra code etc.) would be appreciated.

thanks in advance
Roger

 

[aaron.kempf]

use an autonumber and make 2 fields out of it.

for the record; MDB is obsolete; I reccomend using Access Data Projects

 

[Guest]

A good way to do this is to use the BeforeUpdate event of a combo box that
selects a category. The code assumes the combo box is named cboCategory, the
FileNumber field is FileNum, the table is called Files, and the Category
field is named Category.

Dim varFileNumber As Variant

If IsNull(Me![cboCategory]) Then
' User has deleted the category name
MsgBox "You must enter a category."
Cancel = True
Else
varFileNumber = DMax("[FileNum]", "Files", "[Category]=" &
Me![cboCategory])

' DMax will return Null if there are no matching records
If IsNull(varFileNumber) Then
' No matching records, set field to 1
Me![FileNumber] = 1
Else
Me![FileNumber] = varFileNumber + 1
End If
End If

You will also have to decide how you want to handle deletions. If a record
is deleted, do you renumber the others, leave a hole, something else? If
you're going to leave a hole, it raises the question of the value of a
sequential record-numbering scheme in the first place (or in your wife's
case, 29 sequential numbering schemes).

If the number is just serving as a unique file identifier, I'd rather let
Access assign an AutoNumber. In my applications, I don't want my users
caring about a primary key anyway; I'd rather provide means by which they can
look up a record by more meaningful, known data--the client name, order date,
etc.

Sprinks