Running a Temp Query

[Guest]

I'm trying to have a command button run the query in the following code. Note
the query starts after the question marked out line. I want to use the code
to delete a set of records.

Private Sub cmdDeleteServiceDate_Click()
Dim iResponseToDeleteService As Integer

filComplete = "dtmDate =#" & filDate & "# AND strTime ='" & filTime & "'"
iResponseToDeleteService = MsgBox("Do you want to delete the service for" &
vbCrLf & " " & _
Format$(filDate, "dddd, mmmm dd, yyyy") & vbCrLf & " and all of the
related records", vbOKCancel, "Confirm Deletion")
If iResponseToDeleteService = 2 Then
Else:
'MsgBox "are you sure" & filComplete
?????????? Below this line is the query setup?????????????????
Dim db As Database, qdf As QueryDef, strSQL As String

strSQL = "DELETE tblDateTable.dtmDate, tblDateTable.strNotes,
tblDateTable.strTime, tblDateTable.numRated"
strSQL = strSQL & " FROM tblDateTable"
strSQL = strSQL & " WHERE (((tblDateTable.dtmDate)=#" & filDate & "#)
AND ((tblDateTable.strTime)='" & filTime & "'));"
MsgBox strSQL
Set db = CurrentDb
Set qdf = db.CreateQueryDef("")
qdf.SQL = strSQL
RefreshDatabaseWindow

End If
End Sub

 

[Guest]

Unless you are going to save it for later, you don't need to create the
querydef. Just build the strSQL as you are doing, then:

CurrentDb.Execute(strSQL)

 

[Douglas J Steele]

You're not executing the query:

qdf.Execute dbFailOnError

(adding the dbFailOnError flag allows you to trap any errors that may arise
in running the query)

However, as Klatuu points out, there's no real advantage to using a
temporary query.

You could just as easily replace the following 3 lines of code:

Set db = CurrentDb
Set qdf = db.CreateQueryDef("")
qdf.SQL = strSQL

with

CurrentDb.Execute strSQL, dbFailOnError

 

[Guest]

Are so awesome thanks I had the button working in about a minute after I saw
your post. Then I added the dbFailOnError to the code.