Autonumbering on record adding

[johntology]

Hello,

Sorry, I'm new to Access. Just need to know how to Autoincrement a primary key field and display it in my form in Access 2007. I have an Add New Record button which executes this:

DoCmd.GoToRecord record:=acNewRec

Should I add one more line in that Sub to create and display the new autoincremented ID? Or does that command go in the Expression Builder when you select Control Source in Design View for that field?

I want to add something like this:

ALTER TABLE [my table] ALTER COLUMN [my primary key] COUNTER(500,1);

such that records are created starting at 500 and incremented by 1, and displayed as such on the form for the user when they click to add a new record. Or do I not need to do this explicitly?

Thanks.

 

[SmallTime]

Hi

You could set the field in the table to autonumber and ensure new values are set to Increment.

With regards to starting at 500;

past this into the sql view of a blank query and change 'YourTableName' and "YourAutoNumberField" appropriately.

INSERT INTO YourTableName (YourAutoNumberField)
SELECT 499 AS Expr1

run the query (The exclamation mark in query design view) and and the next record should start at 500. Just be aware that you can't re-use autonumbers.

Regards
SmallTime

 

[boblarson]

And just be aware that if you use autonumbers you should not care if they are sequential or not. They are not guaranteed to be sequential (even if the settings are set as such). They ONLY guarantee a UNIQUE number. If you need sequential numbering you need to build your own numbering system using the DMax + 1 method (do a search for it here on the forum).

 

[SmallTime]

As always, Bob's spot-on.

Thanks

 

[johntology]

And just be aware that if you use autonumbers you should not care if they are sequential or not. They are not guaranteed to be sequential (even if the settings are set as such). They ONLY guarantee a UNIQUE number. If you need sequential numbering you need to build your own numbering system using the DMax + 1 method (do a search for it here on the forum).

Thanks, Bob. I did search this forum as you suggested and saw one post saying that an Autonumber can even be negative! Is this true? We're happy here to assign our record IDs to whatever integer Access comes up with, but not if it's negative. We're also keen to avoid numbers below 500 since at some point we're going to merge this with an older data set that has values from 1 - 400+. Can that be guaranteed since I seeded it at 500?

Also, if I choose to use DMax + 1 what event should take the command? I have a "create new record button" which simply executes

DoCmd.GoToRecord record:=acNewRec

Should I add the DMax command to this Sub? Should I delete the "Add New Record" button since Access already has that function built in? If so, what event takes the Dmax command?

Thanks.

 

[SmallTime]

If you're going to update the numbers then definitely don't use Autonumber. once a number's been used it can't be reused so merging later on wont be an option.

I'd run Dmax immediately BEFORE saving the new record. If there's multiple users, this'll reduce the possibility of more then one user trying to catching the same max number. If it's a single user then it's not so important and you could grab the number on the AfterUpdate event of the first field when creating a new record.

SmallTime

 

[johntology]

If you're going to update the numbers then definitely don't use Autonumber. once a number's been used it can't be reused so merging later on wont be an option.

I'd run Dmax immediately BEFORE saving the new record. If there's multiple users, this'll reduce the possibility of more then one user trying to catching the same max number. If it's a single user then it's not so important and you could grab the number on the AfterUpdate event of the first field when creating a new record.

SmallTime

Thanks. Can you tell me where I run DMax and what triggers it? My scenario is this: The user wants to begin data entry with incrementing ID numbers starting at 500 . At some point in the future he wants to import historical data that has IDs from 1 to 400.

 

[gemma-the-husky]

john


let's say you need a record id for a record

an autonumber will do this. It doesn't matter if its negative.

- a one byte number ranges from 0 to 255 (256 values)
- a signed one byte number ranges from -128 to 127 (still 256 values)

an autonumber is 4 bytes, and therfore can hold 2^32 values, or approx 4billion. Because it is signed it can therefore be a value form -2billion to +2billion. That's all. The sign is immaterial. Itsa just 4-bytes ie 32 bits of 1s and 0s guarnateed to be unique. We just interpret it as a number. You can use this as a PK in a table, and as a FK in a linked table. Access can generate whatever number it wants. It is most likely to run in sequence, but it may not. If you scrap a record halfway through, you will lose the number it issued. It doesn't matter that it's negative - because no-one needs to ever see it. If you wnat something meaningful, then you need a different strategy. Note that many users, myself included will often have both an autonumber AND somethnig meaningful. The autonumber is there for efficient. The meaningful is there for your business.



BUT, let's say the number needs to have meaning, and say needs to be in sequence. Now one way is to execute a function to find the highest number in the table, and add one to it. That function is DMAX. Is there a problem with this. Yes, potentially. If you have several people inputting at the same time, they may all get issued with the same DMAX. (because your DMAX isn't recorded in the table until you save your record - so other users get the same DMAX)

So how do we get round this. By waiting till the very last moment to collect the DMAX. ie - wait until you actually commit the record save, and then instantly get the number. In theory, there are a few micro seconds when a clash could still happen - but in practice it wont happen. Note asso that DMAX can be "partitioned" if you will. You can find a DMAX foir items in May, and a DMAX for items in June. You don't just have to have one DMAX.



But this way, doesn't give you the number until right at the end, which may not be what you want. You may need to use the number at any earlier stage. So, instead - store the next number in a separate table. Now when you want a number, open the table, read the next number, increment it for the next user, and put it back. You don;t HAVE to do this, but if you LOCK the table first, this ensures there is absolutely NO possibility of a duplicate number. The downside here is that once you have your number - you may change your mind, and the number gets wasted, because you have already incremented the counter. Trying to fill the gaps is definitley best avoided.


so you can choose whichever scenario works for you.

----
Now if you do go for an ordinary number, rather than a autonumber, then you can easily enter old numbers, as long as they aren't duplicates - so backfilling numbers 1-400 isn't an issue.

Even with autonumbers, you can do the same - force numbers into the table via a query - but you can't edit them directly.
Having said that - You certainly can force numbers into Access tables, but I think at least some versions of SQL won't allow you to "force" in a particular number. But I am not 100% certain about that.