Delete Rows without lossing autonumber

[markswan20]

Hi Everyone.

I find that when you delete a record from access you lose the number. My question is if you delete record 3 you lose it permantly is there a way you can re enter details in 3 or do you lose it for good. Also can you make your autonumber a specific format i.e WFN100"then auto number"
WFN1001
WFN1002
ETC.

Kind Regards
Mark

 

[lupins46]

No to both questions.
You would have to create code to generate your own numbering sequence.

Autonumbers are just there to provide a unique record number. You should not attach any meaning to the number.

 

[markswan20]

Is it possible to start the autonumber at a specific number?

Cheers

 

[lupins46]

Use an append queey to add a record to the table with a key value of one less than where you want to start.
Delete the record.
DO NOT COMPACT.

Add a new record.....

 

[ReluctantDataGuy]

Try not using AutoNumber. Its not as difficult as you might think to increment them manually, and get the result you're looking for.

When you create a new record, set its key field value to:

Me.KeyFieldName = DMax("[KeyFieldName]","TableName") + 1

If you subsequently delete the new record, you won't lose the id number.

To add and manage a prefix like WFN is a little more work but it can be done. One simple way to handle it is to have two fields for the ID - one that you display (WFN100) and another field that just holds the number (100).

With that method, you'd set the ID_Num for a new record to:

Me.KeyFieldName = "WFN" & _
DMax("[KeyValFieldName]","TableName") + 1

(where KeyValFieldName is the field that just has the number in it)

Of course, in this scenario you'd also need to increment the value of KeyValFieldName the same way as the first example above.

...hope that helps...
ReluctantDataGuy

 

[SilentAiche]

Markswan20,

Welcome to Tek-Tips!

I've seen several discussions on "customized autonumbers" in the last six months or so. I'd recommend searching the forum for additional tips - if I recall, there were a number of different ideas.

Best of Luck!
Tim

[blue]_____________________________________________________
If you need immediate assistance, please raise your hand.
If you are outside of Raleigh, raise your hand and say[/blue] [red]Ooh! Ooh![/red]