Auto Number feild is Missing squential number if Primary key feild is not entered

[pinky]

HI,
My Form has two fields, Number and Index where Number is autonumber Field and Index is set as primary key.

1> By mistaken if anyone goes to next record and fill any field without entering primary key, then Autonumber is incremented but is not saved. If they want to delete all the information without saving, it does not. It does not allow them to close.

2> There are records in the table with missing Numbers(auto numbering). The Records show 10 at the bottom but actually there are only 6 records with 4 missing. How to make the data with sequential number?

Can anyone help me with this?

Thanks,
Pinky

 

[RuralGuy]

It sounds like you are not using an AutoNumber as it was designed. http://www.mvps.org/access/general/gen0025.htm If you need a continuous sequence of numbers then use the DMax() method. Google can help with this method.

 

[pinky]

after googling and found dmax() method. I have written this in Default value property of form Textbox control.

Here is the code:
=nz(DMax("Control_Number","[tbl general]"),"Criteria=n")+1

But it didn't work.

Later I tried, Before Update event procedure:
Private Sub Control_No__BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Control_Number.DefaultValue = DMax("Control_Number", "[tbl general]") + 1
end if
End Sub


Control_Number - Name of the Autonumber field on the form.
[tbl General] - Name of the table without underscores.

But it didn't work.
Any Help?

 

[RuralGuy]

Set the actual value, not the Default Value of that control and do it in the BeforeUpdate event of the FORM and not the control.

 

[RuralGuy]

If it is important for the user to see the value before saving then use the OnDirty event of the form.

 

[pinky]

Thanks for helping me out.......

How to set the Actual value? I can see the Default Value under the options under Data tab of properties, How could we get to actual value and set it.

Thanks,
Pinky

 

[RuralGuy]

Control_Number.Value = DMax("Control_Number", "[tbl general]") + 1