Custom autonumber (1 Viewer)

[jake7363]

It’s been a while since I've used Access (360), so I'm very rusty. Is it possible to have the autoonumber primary key start from 1000?

If so, how?

Cheers,
Jake

 

[plog]

You could add 999 dummy rows and then delete them.

But why? What's so special about your autonumbers that they need to start at 1000? What purpose are you trying to make these values serve?

 

[CJ_London]

Seem to recall if you create your table with a numeric pk(not autonumber) add a record and populate the pk with 999

Then go back to table design view and change the field to autonumber

Next record you add should populate the pk with 1000

but agree with plog, why? A pk’s sole purpose is to uniquely identify a record and should not be assigned any other meaning

 

[Pat Hartman]

Once you have data in a table, you cannot change a field from long integer to autonumber.

The method is to use an append query. The append query adds a record with 999 as the value for the autonumber field. You will need to include values for any column that is required. Once the row has been added, that resets the autonumber seed so that the next row added will be 999 +1 or 1000. Once that record has been added, you can delete the dummy 999 record.

The ONLY way you can add rows to a table and include an autonumber value is by using an append query. You cannot update an existing record or add any record using any method other than an append query. If you couldn't add old PK values using an append query, conversions and restores would be a nightmare.

Keep in mind that the singular purpose of an autonumber is to provide a unique ID for a given row. If you are going to display the autonumber and use it as an "invoice" number for example, most people don't want it to start at 1. No one wants to send a customer invoice # 1. It looks better if the invoice is # 1000. If this is your situation, I would probably make it start at a larger number and not an even one. So I would insert 610023, for example as the start. Then the first invoice will be 610024 which no customer would regard as strange.

I'm attaching a link to a sample that shows how to generate custom sequence numbers if you want something more exotic.

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk

 

[CJ_London]

seems my recollection was incorrect

 

[Gasman]

It’s been a while since I've used Access (360), so I'm very rusty. Is it possible to have the autoonumber primary key start from 1000?

If so, how?

Cheers,
Jake

Yes, use the Alter statement. However you should not be concerened what that number is other than it links records correctly.
If you want a custom auto number put a number in a config table and use that.

Sub ResetTableNumber(pstrTable As String, pstrID As String)
Dim strCmd As String, strSQL As String
strSQL = "DELETE * FROM " & pstrTable
strCmd = "ALTER TABLE " & pstrTable & " ALTER COLUMN " & pstrID & " COUNTER(1,1)"
CurrentDb.Execute strSQL
CurrentDb.Execute strCmd
End Sub

 

[jake7363]

Once you have data in a table, you cannot change a field from long integer to autonumber.

The method is to use an append query. The append query adds a record with 999 as the value for the autonumber field. You will need to include values for any column that is required. Once the row has been added, that resets the autonumber seed so that the next row added will be 999 +1 or 1000. Once that record has been added, you can delete the dummy 999 record.

The ONLY way you can add rows to a table and include an autonumber value is by using an append query. You cannot update an existing record or add any record using any method other than an append query. If you couldn't add old PK values using an append query, conversions and restores would be a nightmare.

Keep in mind that the singular purpose of an autonumber is to provide a unique ID for a given row. If you are going to display the autonumber and use it as an "invoice" number for example, most people don't want it to start at 1. No one wants to send a customer invoice # 1. It looks better if the invoice is # 1000. If this is your situation, I would probably make it start at a larger number and not an even one. So I would insert 610023, for example as the start. Then the first invoice will be 610024 which no customer would regard as strange.

I'm attaching a link to a sample that shows how to generate custom sequence numbers if you want something more exotic.

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk

The append query is the method I know will work, but I forgot how to create the append query without using the query item from the menu. If I use a starting number of 1000, the query comes up with no records.


Fyii, I am trying to modify a table I made a few years ago.

Jake

 

[Gasman]

The append query is the method I know will work, but I forgot how to create the append query without using the query item from the menu. If I use a starting number of 1000, the query comes up with no records.


Fyii, I am trying to modify a table I made a few years ago.

Jake

So try the Alter method without the Delete command ?

 

[moke123]

Access – Reset AutoNumber Counter/Seed | DEVelopers HUT

Demonstrating how to use VBA in conjunction with ADO to run an SQL Statement to set/reset an AutoNumber Seed/counter value to a specific value.

www.devhut.net

 

[Pat Hartman]

This is the help entry

INSERT INTO statement (Microsoft Access SQL)

Office developer client VBA reference documentation

learn.microsoft.com

INSERT INTO YourTable fldPK, fldDT VALUES (1000, #05/28/2023#)

MS help is generally poor but sometimes you just need syntax so keeping a link to the Access VBA reference can help you to drill down even when you don't know quite what you're looking for.

 

[gemma-the-husky]

Don't use an autonumber to try to maintain a sequential list. It may jump to a new number, and it will almost certainly show missing numbers occasionally when you or your users "burn" a number by cancelling an entry.

You can obtain the next number from a next number table/field if you require. That will give you a sequential record.

 

[Pat Hartman]

Dave, this is exactly the method used by the autonumber and exactly the reason why gaps can exist. In a multi-user environment, multiple people can be adding rows at the same time. If userA gets a new sequence too early in a process, there will be time for userB to obtain the next available number. Then if userA's append fails, the generated number never gets used, leaving a gap. Access generates the autonumber as soon as the record is dirtied so it is far more susceptible to causing gaps than a custom solution which doesn't acquire the new sequence number until immediately prior to exiting the Form's beforeUpdate event. This leaves only a miniscule time for another user to sneak in. BUT, if there is some error and userA's record does not actually get saved, you can still end up with a gap.

There are ways around this by using Transaction processing and unbound forms but not for the faint of heart. Using an unbound form lets you use a transaction. The transaction locks BOTH tables and only releases the lock when the transaction completes successfully or fails and the updates for both tables are backed out. Locking multiple tables has the potential to result in a deadly embrace as it is called. For example, if the process used by userA locks tbl2 and then tbl1 but the process being used by userB locks tbl1 and then tbl2, that results in a conflict since neither can succeed. Therefore, when you are using transactions and multiple tables, it is imperative that table locks are always acquired by all processes in the same order. In most cases, it doesn't matter which lock is acquired first so just do them in alphabetical order.

In applications where gaps are problematic, it is not the missing records so much as an explanation for why a sequence number is missing. Checks are a primary issue. You simply can't have a missing check number but we always have the possibility of voiding a check. So, the sequence number table in its simple form works fine for this. You generate the number and save it. If the save for the check table fails, you update the sequence number table with a note that check 23445 was cancelled because "x". This does not require unbound forms. It simply accounts for why the gap exists.

It is the large gaps caused by append queries that get cancelled that are most problematic for autonumbers. Those don't happen when using the two table method because the checks would be generated using VBA rather than an append query.

 

[gemma-the-husky]

Dave, this is exactly the method used by the autonumber and exactly the reason why gaps can exist. In a multi-user environment, multiple people can be adding rows at the same time. If userA gets a new sequence too early in a process, there will be time for userB to obtain the next available number. Then if userA's append fails, the generated number never gets used, leaving a gap. Access generates the autonumber as soon as the record is dirtied so it is far more susceptible to causing gaps than a custom solution which doesn't acquire the new sequence number until immediately prior to exiting the Form's beforeUpdate event. This leaves only a miniscule time for another user to sneak in. BUT, if there is some error and userA's record does not actually get saved, you can still end up with a gap.

There are ways around this by using Transaction processing and unbound forms but not for the faint of heart. Using an unbound form lets you use a transaction. The transaction locks BOTH tables and only releases the lock when the transaction completes successfully or fails and the updates for both tables are backed out. Locking multiple tables has the potential to result in a deadly embrace as it is called. For example, if the process used by userA locks tbl2 and then tbl1 but the process being used by userB locks tbl1 and then tbl2, that results in a conflict since neither can succeed. Therefore, when you are using transactions and multiple tables, it is imperative that table locks are always acquired by all processes in the same order. In most cases, it doesn't matter which lock is acquired first so just do them in alphabetical order.

In applications where gaps are problematic, it is not the missing records so much as an explanation for why a sequence number is missing. Checks are a primary issue. You simply can't have a missing check number but we always have the possibility of voiding a check. So, the sequence number table in its simple form works fine for this. You generate the number and save it. If the save for the check table fails, you update the sequence number table with a note that check 23445 was cancelled because "x". This does not require unbound forms. It simply accounts for why the gap exists.

It is the large gaps caused by append queries that get cancelled that are most problematic for autonumbers. Those don't happen when using the two table method because the checks would be generated using VBA rather than an append query.

Do you mean that obtaining a next number from a [nextnumber] field can leave gaps. It depends on the timing. I only do the read when the new record is completed and accepted, immediately before the final update. If you want to be completely secure you can lock the nextnumber table first before obtaining the new number.

I didn't mean you should obtain the next number immediately upon dirtying the new record.

Doing it with an update field also means you can set the next number you want manually. I prefer the nextnumber field idea to a dmax solution.

 

[KitaYama]

I didn't mean you should obtain the next number immediately upon dirtying the new record.

I use the solution you explained in #11 and obtain the next number immediately when the new record is dirty.
I want the user see the number.
But in before update of the form, I check to see if the number is still available and not used by another user.
If it's still available, then the record is saved.
If not, the number is updated to the next available number, record get saved and the user receives a messagebox that the number was updated to xxxx.

 

[gemma-the-husky]

I use the solution you explained in #11 and obtain the next number immediately when the new record is dirty.
I want the user see the number.
But in before update of the form, I check to see if the number is still available and not used by another user.
If it's still available, then the record is saved.
If not, the number is updated to the next available number, record get saved and the user receives a messagebox that the number was updated to xxxx.

I know you are a skilled developer, but if the number that was displayed might be changed, I can hardly see the point in displaying the number at all until you get to the point that it won't be changed.

You wouldn't actually get that in my version because I read the next number, and then update the value ready for the next number to be issued. I only use this process where I do want a managed sequence. Otherwise I just use an autonumber.

 

[KitaYama]

I know you are a skilled developer,

It's very nice of you to think so, but honestly I'm not even close to being a skilled developer,

I can hardly see the point in displaying the number at all until you get to the point that it won't be changed.

You're absolutely correct. But it's really very rare that it change. We don't have enough designers to work on different projects for the same customer at the same time. In fact I've never seen it's been changed in before update. It's only a safety check.

 

[Pat Hartman]

Unless you have an extremely busy system, it is unlikely that the number will change. @KitaYama is generating the number immediately but not saving it and so that requires checking just before saving to ensure that the number hasn't been used. This method should also be coded to handle a database error in case the timing is such that no one slipped in between the check and the final save.

The autonumber is generated and saved immediately when the record is dirtied. This means that no one can sneak in and use that number. It also leads to gaps when the record that generated the number isn't saved.

Using your own table is just like the autonumber. You lock the number immediately so no one can use it But, the method allows for gaps. Not the huge gaps you get with autonumbers but gaps nevertheless because you have to commit the ID to the ID table prior to committing the record that will use it. There are times when the second record might not get saved. That is what would cause the gap. If I were to use this method, I would do a lot of validation in the form's BeforeUpdate event to minimize the chance that the database engine will throw an error and not sve the record, AND I would generate the ID and save it as the last step in the BeforeUpdate event to minimize the possibility of a gap.

I'm not saying what method is best. All three have their issues so you have to decide what you prefer. The first method won't leave gaps but rather than checking before the save, I would capture the error and loop back to the save after generating another ID.

 

[gemma-the-husky]

Unless you have an extremely busy system, it is unlikely that the number will change. @KitaYama is generating the number immediately but not saving it and so that requires checking just before saving to ensure that the number hasn't been used. This method should also be coded to handle a database error in case the timing is such that no one slipped in between the check and the final save.

The autonumber is generated and saved immediately when the record is dirtied. This means that no one can sneak in and use that number. It also leads to gaps when the record that generated the number isn't saved.

Using your own table is just like the autonumber. You lock the number immediately so no one can use it But, the method allows for gaps. Not the huge gaps you get with autonumbers but gaps nevertheless because you have to commit the ID to the ID table prior to committing the record that will use it. There are times when the second record might not get saved. That is what would cause the gap. If I were to use this method, I would do a lot of validation in the form's BeforeUpdate event to minimize the chance that the database engine will throw an error and not sve the record, AND I would generate the ID and save it as the last step in the BeforeUpdate event to minimize the possibility of a gap.

I'm not saying what method is best. All three have their issues so you have to decide what you prefer. The first method won't leave gaps but rather than checking before the save, I would capture the error and loop back to the save after generating another ID.

That's what I do, Pat. The user clicks a button saying Accept order, or whatever, the order number gets read, and the table incremented. The only issue might be an unexpected run time error preventing the record saving.

 

[Isaac]

Just agreeing with anyone else who has, intentionally or non, implied that you should not use Autonumber datatype, which is specifically intended as a surrogate key, as if it were something OTHER than a surrogate key.

Natural keys can have some business meaning (although I still wouldn't recommend using that for the business-facing, customer-facing Number that they crave or want), but surrogate keys should and do not.

 

[Isaac]

If you were on SQL Server you can definitely specify the seed start, but ...