Running a Temp Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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)
 
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
 
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.
 
Back
Top