Change to Delete Query 1

[lars7]

Could anyone suggest a way for me to turn this Query into a Delete Query rather than a Select one. When I change it manually it just reverts back when the query is run.

Here is the code with PHV'S addition:


Private Sub cmdOK_Click()
' Pointer to error handler
On Error GoTo cmdOK_Click_err
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strwhere As String
Dim strtrust As String
Dim strpaynumber As String
Dim strDateprocessed As String
Dim strSQL As String
' Identify the database and assign it to the variable
Set db = CurrentDb
' Check for the existence of the query, create it if not found,
' and assign it to the variable
If Not QueryExists("qryStaffListQuery") Then
Set qdf = db.CreateQueryDef("qryStaffListQuery")
Else
Set qdf = db.QueryDefs("qryStaffListQuery")
End If

' Get the values from the combo boxes
strwhere = ""
If Trim(Me!cbotrust & "") <> "" Then
strwhere = strwhere & "AND trust='" & Me!cbotrust & "' "
End If
If Trim(Me!Cbopaynumber & "") <> "" Then
strwhere = strwhere & "AND paynumber='" & Me!Cbopaynumber & "' "
End If
If IsDate(Me!CboDate) Then
strwhere = strwhere & "AND Dateprocessed=#" & Format(Me!CboDate, "yyyy-mm-dd") & "# "
End If
If Len(strwhere) Then strwhere = "WHERE" & Mid(strwhere, 4)
' Build the SQL string
strSQL = "SELECT * FROM tbltrustStaff " & _
strwhere & " ORDER BY trust, Dateprocessed"
' Pass the SQL string to the query
qdf.SQL = strSQL
' Turn off screen updating
DoCmd.Echo False
' Check the state of the query and close it if it is open
If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qryStaffListQuery") = acObjStateOpen Then
DoCmd.Close acQuery, "qryStaffListQuery"
End If
' Open the query
DoCmd.OpenQuery "qryStaffListQuery"
cmdOK_Click_exit:
' Turn on screen updating
DoCmd.Echo True
' Clear the object variables
Set qdf = Nothing
Set db = Nothing
Exit Sub
cmdOK_Click_err:
' Handle errors
MsgBox "An unexpected error has occurred." & _
vbCrLf & "Please note of the following details:" & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_exit
End Sub

 

[Remou]

Here:
strSQL = "SELECT * FROM tbltrustStaff " & _
strwhere & " ORDER BY trust, Dateprocessed"
You are making the query a select query, you must say:
[tt]strSQL = "[blue]DELETE[/blue] * FROM tbltrustStaff " & _
strwhere"[/tt]

 

[lars7]

Hi Ramou,
I did that but i'm getting an error message saying:

Missing semicolon ( at end of SQL statement.

 

[Remou]

What version of Access is that? How about:
[tt]strSQL = "DELETE * FROM tbltrustStaff " & _
strwhere[/tt]
(I just noticed I missed deleting a quotation mark.)

If you still get an error, how about:
[tt]strSQL = "DELETE * FROM tbltrustStaff " & _
strwhere & ";"[/tt]

 

[lars7]

Hi Remou,
Thanks, that worked, I had to remove:

"ORDER BY trust, Dateprocessed" but that's ok.

Thanks again.