I have a subform that has 1 field. I have found that, for example, I enter in 3 records and then decide I don't need the last record so I erase the data, when I leave the subform (or go back a record), the blank record is left(still shows 3 records). Is it possible to check if the field is blank (OnExit or AfterUpdate) and then delete the record?(I know how to check for the null field but I can't seem to find an easy way to delete the record. Also I don't want it to confirm the delete. Need it to be nice and quiet like!)

Any help is appreciated.

[This message has been edited by SomeGuy (edited 11-25-2000).]

You can put code in the BeforeUpdate event of the form to check the relevant field. If it is null, cancel the update and undo the edits. You must use the BeforeUpdate event, the AfterUpdate event is not fired until AFTER the record is saved. Then you really would need to delete it. By putting the code in the BeforeUpdate event, you prevent the erroneous record from ever being saved.

If IsNull(YourField) Then
Cancel = True
Undo = Yes
Exit Sub
End if

Hi Pat. Thanks for your response.

Access did not like the line "Undo = yes"

Just to let you know, I am using Access97

Try DoCmd.RunCommand acCmdUndo

The problem that I am finding with the Undo method is that if someone enters data in the field and then removes it before leaving the form, the undo puts the text that they just erased back in the field!

Very Confusing to say the least. I have made a delete query that will take care of these anomalies if they ever occur but I don't seem to be able to run them quietly in the background because the "WARNING you are about to DELETE a record" message pops up.

You can get past the warning when you run the query by adding the following lines:

Add this line before the query executes:
DoCmd.SetWarning False

Then add this line as the next line of code after you query executes:
DoCmd.SetWarnings True

Both of these lines should be in the included in the same procedure where the query is executed.

Make sure you set the Warnings back to True or you will not receive any warnings from that point.

HTH
RDH

Sorry,
The Undo command is
Me.Undo

You can't delete the record in the BeforeUpdate event because it hasn't been added yet.

Thanks for the help guys. I really appreciate it.

PS-- R. Hicks: You forgot an "s" on one of the SetWarnings! I ended up getting a Warning!