Autonumber skipping numbers after record delete.

[postmanphat]

Hi,

I have a table that uses Autonumber to generate the ID number (PK) for the table. The data is entered via a form and I have 2 buttons on there: 'Save' and 'Don't Save'. If a user mistakenly enters a record and wants to exit the input form without saving it, they obviously cick on 'Don't Save'.

The problem is that whenever they go to put on the next record, Access misses out the ID of the record it hasn't saved. E.g I have 123 records. I'm inputting record no 124 but realise half way through that I don't need it, so I click on Don't save. The next time I wish to add a record, Autonumber will give it ID 125, but I want it to be number 124 so that there is no gaps.

Is this possible at all?

Ta

Dave

 

[Remou]

If you want a number that means something and / or is sequential, you will have to generate it yourself. Autonumber is intended to be unique, and nothing else. You cannot depend on it to be sequntial or even a positive number.

See commandment number seven

http://www.mvps.org/access/tencommandments.htm

 

[postmanphat]

bugger...

 

[nim180]

you could always delete that ID column from the table and re-insert it but Remou is right.

 

[RivetHed]

If the form's only to be used for data entry you could always unbind it and only write data to the table when the user has clicked save.

 

[SeeThru]

I had a little one that used unique numbers for invoicing.

This ran in the OnCurrent of the form

If Me.NewRecord Then
    InvoiceNo.DefaultValue = DMax("InvoiceNo", "Invoice") + 1
end if

So it was always the next highest.

Note that the InvoiceNo was NOT autonumber - just a long integer set to primary key and required.

This has a few problems - if you delete an earlier invoice after creating the next one, you end up with a gap, but it is not bad for a quick fix.

SeeThru
Synergy Connections Ltd - Telemarketing Services

http://www.synergyconnections.co.uk