using QueryDef and SQL DELETE

[ckib]

Hi, I'm attempting to delete a record from table tblSegment when the value of a field on a form is set to "N". I think there is a problem with my strSQL, I get the error: Run time error '91': Object variable or With block variable not set. Thanks for any help you can provide.

Here is my code:

If Me!List_active_status.Value = "N" And Not IsNull(Me!cont_projectID) Then
strText = "Are you sure you want to remove this contact from the project?"
theAns = MsgBox(strText, vbOKCancel)

If theAns = 2 Then 'response = cancel ... abort process
Undo
Else 'response = Okay
'remove this project/contact from tblSegment
Dim myDeleteQry As QueryDef, myDeleteRS As Recordset

' Define SQL statement for delete query

strSQL = "DELETE tblSegment.seg_projectID, tblSegment.seg_contactID, tblSegment.seg_num ) " & _
"FROM tblSegment " & _
"WHERE (((tblSegment.seg_projectID)='" & strProjectID & "')" & _
"AND ((tblSegment.seg_contactID)='" & strContactID & "'));"

Set myDeleteQry = myDB.CreateQueryDef("", strSQL) ' create temp delete query

' Run temporary QueryDef

myDeleteQry.Execute
myDeleteQry.Close

End If
End If

 

[Norie]

Take a look at the SetWarnings method.