Solved Problem with delete Qry (1 Viewer)

[HillTJ]

All, The 'PartyID' passed to the where statement is causing me grief. the value is not being passed, rather the text 'PartyID' is being passed. I suspect its simple syntax issue but I haven't figured it.

Appreciate some help.

Public Function DeleteRecord(PartyID As Long)
    On Error GoTo Error_Handler
    Dim db                    As DAO.Database
    Dim sSQL                  As String


    Set db = CurrentDb
    MsgBox PartyID
  
    sSQL = "DELETE tblParty.PartyID, tblParty.DateOrigination " & vbCrLf & _
    "FROM tblParty " & vbCrLf & _
    "WHERE (((tblParty.PartyID)=PartyID));"
    
    Debug.Print sSQL
    db.Execute sSQL, dbFailOnError


    If db.RecordsAffected = 0 Then
        MsgBox "No New records were created by the above query?"
    End If
    
Error_Handler_Exit:
    On Error Resume Next
    If Not db Is Nothing Then Set db = Nothing
    Exit Function


Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: cmd_AddRec_Click" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

 

[Josef P.]

sSQL = "DELETE tblParty.PartyID, tblParty.DateOrigination " & vbCrLf & _
    "FROM tblParty " & vbCrLf & _
    "WHERE (((tblParty.PartyID)=" & PartyID & "));"
                                ^

or

sSQL = "DELETE FROM tblParty WHERE PartyID=" & PartyID

 

[cheekybuddha]

Remember, a DELETE query deletes the whole record (row) from the table.

You are specifying specific fields. Are you just trying to clear the values from those two fields and leave alone the values in the other fields in the record?

If so, then you would actually need an UPDATE query.

(Unlikely, since you appear to be wanting to delete the PartyID which we might guess is the Primary Key field of tblParty)

 

[HillTJ]

Thanks. I appreciate it. Trying to learn a bit more sql.