Search for records in table via VBA

[hughess7]

Hi all

I have SpecID and ReviewDate in a primary table (Plan) and I want to search
for matching records in a related table (Plan Dates) and delete them. I know
I can use DCount to check if any records exist but since I need to delete
them if any is found, do I need to use the openrecordset and findfirst
method, then delete?

Thanks in advance for any help.
Sue

 

[Douglas J. Steele]

If the intent is simply to delete them, just use a Delete query.

CurrentDb.Execute "DELETE FROM PlanDate " & _
"WHERE SpecID = " & lngSpecID & _
" AND ReviewDate = " Format(dtmReview, "\#yyyy\-mm\-dd\#")

That assumes that the applicable SpecId is stored in variable lngSpecID, the
ReviewDate is stored in dtmReview, and that SpecID is a numeric value. If
SpecId is text, you need to include quotes around it:

CurrentDb.Execute "DELETE FROM PlanDate " & _
"WHERE SpecID = '" & lngSpecID & "' " & _
"AND ReviewDate = " Format(dtmReview, "\#yyyy\-mm\-dd\#")

Exagerated for clarity, that middle line is

"WHERE SpecID = ' " & lngSpecID & " ' " & _

 

[hughess7]

OK slight change of method... will this work?

Code will be run from update of ReviewDays (if > 1) on form based on Table
Plan (primary table)

CurrentDb.Execute "DELETE * from [Plan Dates] where SpecID = " & me![SpecID] _
& "AND ReviewDate = #" & Format(Me!ReviewDate, "mm/dd/yyyy") & "#",
dbFailOnError

If there are no records to delete will this cause a problem?

How could I test this without causing the delete to run in case of errors?

Sorry, novice VB skills!!

Cheers
Sue

 

[hughess7]

Thanks Doug, it was an Integer. I managed to get it to work, I just needed an
extra space after the SpecID before the AND.

This code runs every time though regardless if any records exist, it doesn't
cause any errors but is it more efficient coding to check if any exist before
executing the delete?

Thanks
Sue

 

[Douglas J. Steele]

Personally, I don't think it's any more efficient to check first and only
delete if there's data present. You're running two queries in that case. I
suppose the query that counts is probably a little less work than the query
that does the delete, but unless your data is such that you'll be deleting a
very small percent of the time, I don't think you'll notice any difference.