New Record - AutoNumber

[backfromthebush]

Hi all,

I am wondering if there is a way to autopopulate a number field in Access in the following manner...
The field is called InvoiceNumber, and it is a simple integer (no letters or anything else tacked on), and for every new record it increments by 1 according to what the current max value is within the table.
I don't want to rely on the AutoNumber option as I want to be able to control and change these numbers if I wish so.
Any ideas?

 

[jackd]

Sounds like you have it under control. The method you described would work. Is there another question?

With an autonumber (data type) the value will get put in the new record by Access automatically. The number will be unique -- not necessarily continuous/sequential.

If you choose to use your current Max + 1, you will have to place that value in the new record.

 

[Joe4]

The number will be unique -- not necessarily continuous/sequential.

Actually, I am pretty sure it is - unless you start deleting old records.
At least I have always found it to be this way.

 

[boblarson]

Actually, I am pretty sure it is - unless you start deleting old records.
At least I have always found it to be this way.

Joe - It is not guaranteed to be incremental, even if you have it set to incremental. It only guarantees you a UNIQUE number. While for many people it works as you would think, it doesn't always and it can jump numbers all of a sudden, can go into random mode which will generate negative numbers as well. It is best to NOT use an Autonumber for anything but a number which you need to be unique (such as a surrogate key). Any other use than that will be playing Russian Roulette with your structure. I have yet to find any Access MVP who advocates otherwise.

 

[Joe4]

Bob that is very interesting. I only use it to guarantee uniquesness (don't really count on it for programming) , but have never noticed it behave in that manner. But I don't doubt what you say (Access has a variety of "bugs"). As matter as fact, I seem to recall that we may have discussed something like this before (the old gray matter doesn't retain information like it used to!).

Does there seem to be any rhyme or reason when this autonumber "weirdness" happens?
Or is there some way to reproduce this odd behavior?
I would love to try to "force" it happen, just to see it happen (because I am a curious guy!).

 

[xenou]

I don't know of a way to auto-populate a field except by autonumber. You *can* query for the max value and increment it when you insert a new record. If your DB is very busy you may need to be careful in case two users try to get the same next available ID number at the same time (it would fail for the second person to try to use it).

I've never (actually) seen a negative AutoID number - has anyone else?

 

[Joe4]

I don't know of a way to auto-populate a field except by autonumber. You *can* query for the max value and increment it when you insert a new record.

Yes, if you control new entries via Forms, you apply VBA code to the adding of the new record in the Form that would populate this number.

If you are importing the data, you could use some VBA code to loop through the Recordset afterwards and add the values.

 

[xenou]

As an example, use a public function in a standard module (I've called your table Table1 here):

Public Function GetNextTable1ID() As Long
    
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT MAX(Table1.ID) FROM Table1;")
    If Not rs.EOF Then
        GetNextTable1ID = rs.Fields(0).Value + 1
    End If
    Set rs = Nothing

End Function

Is there a better approach? I use something like this in one of my databases to insert new records with incrementing numeric IDs.

Edit:
Ah, just saw Joe's last post. That's a good thought. Reminds me that you could probably just use a DLookup() function on a form or in VBA which would be simpler than my function above:

=DMAX("[ID]","[Table1]")

 

[boblarson]

I've never (actually) seen a negative AutoID number - has anyone else?

I have and, I've seen it where it WAS set to incremental. But if it is set to RANDOM then it will have negatives. Or if you have it set to incremental and you choose to use replication, then it will revert to random. But many times it is a slight bit of corruption which causes it to go "off the rails" so-to-speak. So it is best to not rely on it for anything except a unique number.

 

[jasmith4]

My experiments have concluded that Access always retains the last number generated, so if you delete records -- even just the last one or all of them -- you'll still get one greater than that last number, wherever it is. I've never see an autonumber less than 1 before...

To force it to start at a particular number N, you'd have to 1) delete and re-create the table object, 2) have a make-table query which copies only the top N-1 records into a new table, and then use that new table, or 3) copy only the table's structure into a new table, and then use an append query which has all the fields except the auto-number field. Obviously these are all pretty impractical.

Autonumbers to me are like allocating memory in C/C++/C#: it doesn't matter what the actual address of the memory is, as long as you have the address, you can read and release the memory when you need to.