ADO Basic 101

[Cleis]

Okay This is simple . . .but not for me. Teaching myself ADO and making progress but slowely. Can someone tell me whats wrong with this code. It only works 50% of the time?

I'm opening a Query to see if two criteria exist.
One If the Vendor# exists in one table
Two If the Year Exists in another table
Thus Why I'm using a Query.

The code:

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strSQL1 As String
Set rst = New ADODB.Recordset

rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.Open "Select * FROM qryExistingAgrmntForYear"

strSQL = "[Vendor#] = " & txtVendor
strSQL1 = "[Year] = " & txtYear

rst.Find strSQL
rst.Find strSQL1
If rst.EOF Then

MsgBox "Not FOund"

Else

MsgBox "Found"

End If

'Debug.Print rst.GetString
rst.Close
Set rst = Nothing

I originally tried to filter the query from a field in on the form called txtVendor and just use the strSQL1 = "[Year] = " & txtYear.

Any help would be appriciated!!

Rich

 

[cmmrfrds]

More efficient to put the criteria in the where clause and only bring back the record(s) that meet the criteria. Text fields need to be surrounded by quotes. chr(34) = double quotes.

Dim rst As ADODB.Recordset
Dim strSQL As String
Set rst = New ADODB.Recordset

rst.ActiveConnection = CurrentProject.Connection
strSQL = "Select * FROM qryExistingAgrmntForYear " & _
"Where " & _
"[Vendor#] = " & chr(34) txtVendor & chr(34) & _
" and [Year] = " & chr(34) & txtYear & chr(34)

Set rst = CurrentProject.Connection.Execute(strSQL)

If rst.EOF Then

MsgBox "Not FOund"

Else

MsgBox "Found"

End If

'Debug.Print rst.GetString
rst.Close
Set rst = Nothing