IDENTITY field increments on invalid record entry also

[Guest]

I use ACCESS 2003 which has a linked SQL server table . The front end is a
form that displays the table in a tabular fashion and allows editing,
insertion of new records. One of the fields in the table has an IDENTITY
property . Now when the user does not enter the required values, and tries to
save, ACCESS displays an error message but increments the autonumber value of
the field anyway. Thus when I enter a valid record, there can be a number
hole since the previous entry was an error.
Is there anyway to avoid this ? (One way that I know of ,is to create a form
where I validate with VBA coding). Can I avoid VBA and still be able to
prevent the the autonumber getting incremented for invalid record entries.
Any help would be appreciated

 

[Rick B]

Autonumbers should not be used for any meaningful data (like client number).
An autonumber will always be unique, but CAN contain gaps as you mention.

If you are concerned with that, you should create code to increment the
field your self in the form. This is typically done by looking at the table
and finding the highest number for your field, then adding one to it just
prior to inserting the record in the table. This is done in code typically.

For more information, do a search and read the hundreds of previous posts on
this topic. This is a very common issue and is documented pretty
extensively in these newsgroups.