Handling #DELETED messages on records that have been deleted while a form is active

There may be a better way to do this, but currently, if the user enters i value that is not allowed (in this case, a project that has been closed), then it notifies them. However, because it's on a sub-form linked 开发者_如何学运维to the table, it automatically created a record anyway. This is how it's currently handled (if a bad input was entered):

1. Clear the fields of that record
2. Delete the record from the table
3. Requery / Refresh

Currently it works fine, except it leaves #DELETED in all the controls associated with the records. However, if I hit F5, it disappears. Is there a way to make it so the deleted record disappears (like how it does when F5 is hit) programmatically?

I think you have a couple options to handle this. The main thing is that you want to avoid deleting the record and try to handle it before it ever becomes a saved record. You should be able to do this using one of several different form events (not control events): Dirty, BeforeUpdate or BeforeInsert.

If you use the Dirty event, check to see if the project is closed and check to see if the record they are working on is a NewRecord. If both are true then try using Cancel = True and/or Me.Undo.

If you use the BeforeUpdate event instead, it works much the same way. You'll probably need to cancel the edits and undo the edits as I mentiond directly above, in which order I'm not sure.

I don't have a good way to test the BeforeInsert event at the moment but I think you can prevent new records from coming into existence inside this event by using Cancel = True.

If you cannot handle the record before it gets saved, and you must truly clear #DELETED from your form, you'll probably need to use a Me.Requery statement. Exactly how this will work depends on whether or not the form is bound and what it is bound to but it sounds like you are using a fairly standard Access form that is bound to a table or SQL statement so I believe the Requery statement should work.