Full Version: Auto Number Issue
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
katiepaterson
I have used autonumber to kee track of the number of costing quotes I have in a databas, but somehow it has miss counted and skipped number 41... how do I fix that. I do not want to have a number missing.
Peter46
That is the way autonumbers work.
The next autonumber value is allocated as soon as you start to enter a new record.
If you decide not to comp[lete that record then the autonumber value is 'used' and will increase again the next time you start to eneter an new record.

You cannot change this behaviour.

If it is essential that you have a continuous sequence you must create your own number sequence. This is usually done by finding the current max value and adding 1 to it.
katiepaterson
Where would I enter that function?? Would I have to use macros or can I enter in somewhere iin the properties??
freakazeud
Hi,
the autonumber datatype is really just meant as a primary key value for any tables. It should have NO meaning to you or your users. It only ensures that access works internally correct by identifying each record unique. This does not mean that it will be sequential and unique. If you want a meaningful number then add an extra field (not the primary key of the table). You can use a dmax +1 function to create a custom increasing number. Search UA for many discussions/samples on that e.g.:
01
02
...
HTH
Good luck
katiepaterson
OKay I see the dmax function but I still do not understand where i would put the function... Please give some more detail as to the location of where i would type this function??
freakazeud
You can use it on your form's before update event to set a specific control bound to the field to the returned dmax value!
HTH
Good luck
katiepaterson
I put in the expression...

=[Dmax]+1

Put is gives me an error. I have also tried..

=[Dmax("Input Data")]+1

With Input Data being my table of information I want to draw from. But When I use this exőression nothing happens, it does not display the next number when I hit a new record for entry.
freakazeud
You do not put this as an expression. You assign the value in code, which means that you need to add an event procedure to a specific event. You then assign your control value with something like this:

If Me.NewRecord = True Then
Me.YourControl = 'Your Dmax expression...
End If

HTH
Good luck
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.