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]

Where do you get the error?

The only thing I can see wrong with the SQL string is there doesn't appear to be a space between ) and AND.

 

[ckib]

Error '91' when ...

I get the error after I click OK in the msgbox. I click on debug and the line:

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

gets highlighted in yellow. I have successfully used CreateQueryDef for an UPDATE query, but have never tried in with a DELETE query. I tried adding a space after the ")" and the "AND" but that had no affect. Any other ideas?
Thanks!

 

[Norie]

Where have you set myDB?

 

[ckib]

set myDB

Oops! Okay, I added set myDB but now I get:

Run time error '3464':
Datatype mismatch in criteria expression.

I replaced strContactID with lngContactID because I remembered that seg_contactID is a long integer. But I still get the 3464 message. New code looks like this:

Dim myDB As Database

Dim strProjectID As String
Dim lngContactID As Long

strProjectID = Me!cont_projectID
lngContactID = Me!contact_id

If Me!List_active_status.Value = "Y" And IsNull(Me!cont_projectID) Then
strText = "Active Status may not be 'Y' when Project ID is blank."
theAns = MsgBox(strText, vbExclamation)
Undo
GoTo Exit_List_active_status_AfterUpdate
End If

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
Set myDB = CurrentDb()
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)='" & lngContactID & "'));"

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

' Run temporary QueryDef

myDeleteQry.Execute
myDeleteQry.Close

End If
End If

 

[Norie]

Do you need single quotes around lngContactID?

 

[ckib]

run time error '3464' caused by quotes around integer

I just changed that! Yes, that was it. Now works perfectly.
Thanks!!!

 

[mdmilner]

I realize this works, but, I wanted to ask if you really needed the querydef?
For action queries, you can use:

DoCmd.RunSQL strSQL


And yes, you'd still have had the same syntax problem putting quotes around a non-string as above. This is just simpler and demonstrates a more direct technique.

Mike

 

[ckib]

docmd.runsql

Just spotted your reply. I'm new to Access, so when I find something in my developers guide that looks like it will do the job, I try it. I haven't seen the docmd.runsql before. I'll try that.

 

[ckib]

docmd.runsql

I tried docmd.runsql as you suggested, but it generates a message box which requires confirmation. I don't see a way to turn this off when invoking the runsql method. Any ideas?