Detecting when AutoNumber field = NULL

[gtwannabe]

I'm having a problem with a form that uses AutoNumber as the primary
key. I have an Abort button to delete the current record and close the
form. If AutoNumber is assigned, the code executes a SQL statement
that deletes the current record. I need to be able to detect when
AutoNumber is unassigned (a new blank record) so that I can simply
close the form without running the SQL delete statement.

Unfortunately, no tests I can think of can identify when the AutoNumber
field is blank or NULL. Any ideas?

[Larry Linson]

<gt*******@gmail.comwrote

I'm having a problem with a form that uses AutoNumber as the primary
key. I have an Abort button to delete the current record and close the
form. If AutoNumber is assigned, the code executes a SQL statement
that deletes the current record. I need to be able to detect when
AutoNumber is unassigned (a new blank record) so that I can simply
close the form without running the SQL delete statement.

Unfortunately, no tests I can think of can identify when the AutoNumber
field is blank or NULL. Any ideas?

Fortunately, you explained _why_ you wanted to determine a Null AutoNumber.
In fact, if you have begun to enter any data in the Record, a number will be
displayed in a Control bound to Autonumber, even though the Record has not
been written.

But, you can accomplish your purpose with the test:

If Me.NewRecord = True Then

Be sure to put code in the BeforeUpdate event to accomplish the
cancellation, because (unless you have other required Fields) closing the
Form will write the dirtied Record.

Caution: Once displayed, that value for the AutoNumber is "used", so
cancelling the Record will not make it available for re-use and avoid the
"gap" in AutoNumbers of a cancelled Record, if that was another purpose of
what you wanted to do.

Larry Linson
Microsoft Access MVP