Auto numbering in a filing database

  • Thread starter Thread starter Melinda
  • Start date Start date
M

Melinda

We are trying to determine if Access is the best fit for a
filing database. Access automatically assigns a file id
number to each entry. However, we have not been able to
find a way to auto number within separate tables.

We have created tables for company names, categories, file
numbers and subfiles. We would like the database to auto
number the file numbers and subfile number based upon the
company name and category it relates to. For example, ABC
Company, Category C (Legal Matters) - what is the next
available file number? or what is the next available
subfile number for file number 7?

We are currently using Access 2000, but we will be
upgrading to 2003 in early 2004.

Please let me know if this is a possibility.
 
Access can't do this automatically (actually, as far as I
know, no database can do it automatically) -- you need to
program the data entry screens to find the next
sequential number for you. A simple example:

tblCustFile:

CustID FileID Desc
ABC 1 File 1 for customer ABC
ABC 2 File 2 for customer ABC
DEF 1 File 1 for customer DEF
DEF 2 File 2 for customer DEF

Now, if you want to create the next file for customer
ABC, you would write code in the screen you use to create
new files, in the form's BeforeInsert event procedure:

[name of control that will hold the file ID number you
want to generate]= Nz(DMax
("FileID", "tblCustFile", "CustID=" & [name of control
that holds the customer ID]), 0) + 1

NOTE: Your code should check to be sure that the control
that holds the customer ID has been filled in.

This code can be adapted to supply subfile numbers as
well.

--Wendy
 
We have created tables for company names, categories, file
numbers and subfiles. We would like the database to auto
number the file numbers and subfile number based upon the
company name and category it relates to. For example, ABC
Company, Category C (Legal Matters) - what is the next
available file number? or what is the next available
subfile number for file number 7?
Click to expand...

An Autonumber field will NOT be suitable for this purpose. An
Autonumber has one purpose only - to provide and almost-guaranteed
unique key value. The numbers should NOT be used in any way that
people will see them or assign meaning to them, since they are
basically uncontrollable and will always have gaps.

If you want sequential numbers for given categories, you'll want to
use a Long Integer field and write or obtain VBA code to identify the
highest number used and increment it - a "custom counter". The code
for this can be very simple for a one-user system where there will
never be contention for the next number, or rather complex for cases
where multiple users might be simultaneously assigning numbers. The
_Access Developers' Handbook_ by Getz et al. has some excellent code
for this purpose, and if you do a google search for "Access Custom
COunters" you'll find several other examples.
 
Back
Top