using variable as criteria in select statement in vba

[belovedcej]

Following gives me an error at Call_Attorney_ID. It is a variable that has been declared earlier in the sub.

Is there another way I should not it since it is a variable and not a column in the sql statement?


Dim rst2 As ADODB.Recordset
Set rst2 = New ADODB.Recordset
rst2.ActiveConnection = CurrentProject.Connection
rst2.Open "SELECT Deleted_B FROM dbo.MED_Attorneys_T WHERE Attorney_ID = Call_Attorney_ID"


If "rst2" = "0" Then
MsgBox "Deletion failed. Record in use.", vbOKOnly

Else

Me.Requery
MsgBox "Deletion has been completed", vbOKOnly
End If

 

[mp9]

Try this:

rst2.Open "SELECT Deleted_B FROM dbo.MED_Attorneys_T WHERE Attorney_ID = " & Call_Attorney_ID

http://www22.brinkster.com/accessory/

 

[belovedcej]

That seems to work - in debug mode I can see that it is pulling in the variable. Thanks!

However, now it always runs on the "Else" of the statement. Somehow rst2 does not equal 0, even though I know it does.

 

[belovedcej]

Got it!!

I changed the IF to rst!fieldname = 0


Thanks for the help!