Creating an unique ID number in sequential order

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

After creating a very simplistic database, I recently realized that using an
autonumber as a refence for records is not a good thing. My database was
created to manage a filing system, so it is very important for it to be able
to number each record in the order that it is entered in the system. When
records are deleted, the deleted number should be reassigned to the following
new record.

I currently have 100 records in place.

I have seen a couple of fixes for this, but I am a beginner user and need
more specific instructions. If there is a need to code, please let me know
specifically where the code is to be placed.

Please Help!
 
After creating a very simplistic database, I recently realized that
using an autonumber as a refence for records is not a good thing.
Click to expand...

Arguable... :-)
My
database was created to manage a filing system, so it is very
important for it to be able to number each record in the order that it
is entered in the system.
Click to expand...

In that case, it sounds like an autonumber is indeed a poor choice.
When records are deleted, the deleted
number should be reassigned to the following new record.
Click to expand...

That is actually a really poor decision -- not in computing terms but as
sound record-keeping practice. Do you have really strong methods for
tracing _everyone_ and _everything_ and _everywhere_ that may have
collected the used number before it was deleted and reallocated?

Your best solution in Access depends on your actual business practice.

The simplest thing is just to pick up the next folder off the shelf and
type its number into the data entry form. Simple and faultless (given
suitable validation and error checking, of course).

The fancy version requires some kind of algorithm to work out the next
number to be allocated and for access to produce it, so you can write the
number on the folder. This always requires some coding; it can be simple
or complex depending on your needs. The most demanding situation is in a
multi-user database where more than one client may be trying to insert
records at the same time.

Hope that helps


Tim F
 
Tim:

I realize this was a poor decision, I had very limited knoweldge of the
autonumber's limitations.

This is not a very complicated database (no relationships), however there
will be multiple in-office users. I was hoping that someone could just let
me know what code to use to automatically increase the file number by 1. I
also do not want this field to be entered by the user or edited by the user.
I don't mind deleting all records and begining again if necessary.

If you do reply with code, please let me know where I am to enter the
code... as a macro or VB etc.
 
however there
will be multiple in-office users.
Click to expand...

There are lots of possible solutions: try googling for something "Access
custom autonumbers multi-user".

FWIW, this is what I do:

' this is a partial command!
jetSQLShort = "INSERT INTO MyTable(RequiredField, IDNum) " & _
"VALUES( ""Default"", "


' need to error trap this, obviously
dwInsertNumber = DMax("IDNum", "MyTable") + 1

' now find a new number
Do While True
' try to insert it but trap any error
On Error Resume Next
db.Execute jetSQLShort & dwInsertNumbr * ")", dbFailOnError

' if that worked, then we've finished already
If err.number = 0 then exit do

' if we are still here, then someone else already used
' that number. Need to screen for other errors too, like
' locked table etc etc

' restore normal runtime error handling (nb what about
' integer overflow..?
On Error Goto 0
' and look at next number
dwInsertNumber = dwInsertNumber + 1

Loop

' on exit dwInsertNumber holds the correctly inserted number



Hope that helps


Tim F
 
Back
Top