Best Method To Find & Delete Record In Table According To Field Criteria?

[Heatshiver]

I am wondering what is the best method in VBA to find a specific record, or records if need be, and then delete it?

More or less, I need to find records that do not have a date in their date field and have them deleted.

I was trying some recordset ideas, but none worked. I also was thinking of using DLookup and then a RunCommand, but I believe this would only apply to the current record, not the record found...

Thank you for the help!

 

[Trevor G]

Create a query and set the criteria for the field to is null and then view it to see how many records it shows you, then back in desgin view change the query to a Delete query, you can then look at the SQL code if you want that or just run the query as and when you need it.

 

[Heatshiver]

Thanks, I was able to use SQL and execute it in VBA. It has created another issue, but I'll try to figure out.

 

[Trevor G]

What is the other issue ?

 

[Heatshiver]

I have the code on a back button for my form so that when the person leaves the form any record without a date they created will be deleted. However, on that same form I have Allen Browne's method to copy values of a main and subform to a new record (http://allenbrowne.com/ser-57.html). It seems when I use the back button it does delete any empty records it finds, but if I have used the checkbox to copy and create a new record, it deletes that record as well...

Any idea why this would happen? Thanks for the help!

 

[Trevor G]

Have you added a Save command for the record into your back button before it runs the Delete SQL Statement