Record Doesn't Delete

[aziz rasul]

I have the following code:

strSQL = "DELETE tblAppealCases.[Letter Reference] FROM tblAppealCases WHERE (((tblAppealCases.[Letter Reference])=" & strLetterReference & "));"
    Call CreateActionQuery("qryTemp", strSQL, True)
    
    If Me.Dirty Then Me.Dirty = False

Public Sub CreateActionQuery(strQueryName As String, strSQL As String, Optional blnDeleteQueryAfterwards As Boolean)
'Call CreateActionQuery("qryTemp", strSQL, True)

    On Error GoTo ErrHandler

    Dim qdfTemp As DAO.QueryDef

    Set qdfTemp = CurrentDb.CreateQueryDef()
    qdfTemp.Name = strQueryName
    
    qdfTemp.SQL = strSQL

    CurrentDb.QueryDefs.Append qdfTemp
    
    DoCmd.SetWarnings False
    DoCmd.OpenQuery strQueryName
    DoCmd.SetWarnings True

    Set qdfTemp = Nothing
    
    If blnDeleteQueryAfterwards = True Then
        DeleteQuery strQueryName
    End If
    
ErrHandler:
    If Err.Number = 3012 Then
        DoCmd.DeleteObject acQuery, strQueryName
        CurrentDb.QueryDefs.Append qdfTemp
        Resume Next
    ElseIf Err.Number > 0 Then
        MsgBox Err.Number & " - " & Err.Description
    End If

End Sub

The problem I'm getting is that the record doesn't delete?

Please note I have been using the CreateActionQuery routine for many years so I know that's not where the problem lies.

 

[KenHigg]

Maybe you need some single quotes:

])='" & strLetterReference & "'));"

 

[VilaRestal]

DELETE in SQL doesn't delete a field, it deletes a record, so you don't specify a field to delete.

DELETE tblAppealCases.[Letter Reference] FROM tblAppealCases WHERE

should be

DELETE FROM tblAppealCases WHERE

if indeed you do want to delete that record.

If you want to 'delete' the value of a field set it to null:

UPDATE tblAppealCases SET tblAppealCases.[Letter Reference] = NULL WHERE

And in any case you're surely going to want quotes round the string value as Ken says.

 

[aziz rasul]

Actually strLetterReference started life as a string then the data changed to numerical but I kept the variable name. I will change that.

VilaRestal I copied the SQL string from a delete query and it works so I would expect it to work in the VBA code as well. However my SQL knowledge is very limited. So what do I know.

Tried your suggestion and it worked. Many thanks both for your contribution. This has been a bug bear for some time.

 

[KenHigg]

Cool - Glad you have it working