Trouble with Find in ADO


G

Guest

The following setup works for reading and writing to records...
but I tried the same thing for a search and

I'm getting an "Improper use of object" error..
Is it because I am trying to search within a recordset with criteria set?


=================================
With cmdAIC_New
.ActiveConnection = cn
.CommandText = "SELECT * FROM tbl_AIC_LOC WHERE [Environment]= '" &
sNewEnv & "' AND [ActualDateTime] = #" & dtNewDate & "#;"
.CommandType = adCmdText
End With

With rsAIC_New
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmdAIC_New
End With

rsAIC_New.Find Criteria:="[CustomizationRec]= '" & sCustRec & "' AND
[Location] = " & lLoc, _
SearchDirection:=adSearchForward
 
Ad

Advertisements

B

Brendan Reynolds

Re the 'improper use of object' error - what line of code is raising the
error?

Regardless of that, though, this code isn't going to work, because the
Criteria argument is not valid. From the Find topic in the ADO help file ...

<quote>
Only a single-column name may be specified in criteria. This method does not
support multi-column searches.
</quote>

You may be able to use the Filter property instead - that does support
multi-column searches.
 
G

Guest

it doesn't appear that there is a "filter" property for find (ADO)
help:
Find (Criteria, SkipRows, SearchDirection, Start)

The line that is giving me that row is the
Find - line itself.

My only way around this - previously was to use DAO, but now I'm stuck
because the criteria I need is more complex - Any other suggestions?


Brendan Reynolds said:
Re the 'improper use of object' error - what line of code is raising the
error?

Regardless of that, though, this code isn't going to work, because the
Criteria argument is not valid. From the Find topic in the ADO help file ...

<quote>
Only a single-column name may be specified in criteria. This method does not
support multi-column searches.
</quote>

You may be able to use the Filter property instead - that does support
multi-column searches.

--
Brendan Reynolds

jonefer said:
The following setup works for reading and writing to records...
but I tried the same thing for a search and

I'm getting an "Improper use of object" error..
Is it because I am trying to search within a recordset with criteria set?


=================================
With cmdAIC_New
.ActiveConnection = cn
.CommandText = "SELECT * FROM tbl_AIC_LOC WHERE [Environment]= '" &
sNewEnv & "' AND [ActualDateTime] = #" & dtNewDate & "#;"
.CommandType = adCmdText
End With

With rsAIC_New
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmdAIC_New
End With

rsAIC_New.Find Criteria:="[CustomizationRec]= '" & sCustRec & "' AND
[Location] = " & lLoc, _
SearchDirection:=adSearchForward
 
B

Brendan Reynolds

Filter is a property of the ADO Recordset object ...

http://msdn.microsoft.com/library/en-us/ado270/htm/mdprofilter.asp

If you still want to try to track down the original error, my next question
would be how are sCustRec and lLoc declared?

BTW: I'm not sure what you mean about DAO and being stuck because the
criteria are more complex - the fact that your criteria are more complex
would seem to be an argument in favour of using DAO, as the DAO FindFirst,
FindNext etc. methods don't suffer from this limitation - they can handle
the multi-column criteria.

Here are two examples of searches using multi-column criteria, one using
ADO, the other using DAO ...

Public Sub TestFindFilter()

Dim db As DAO.Database
Dim rstd As DAO.Recordset
Dim rsta As ADODB.Recordset

Set db = CurrentDb
Set rstd = db.OpenRecordset("SELECT * FROM Employees")
With rstd
.FindFirst "LastName = 'Davolio' AND FirstName = 'Nancy'"
If .NoMatch Then
Debug.Print "DAO: No match found"
Else
Debug.Print "DAO: " & .Fields("FirstName") & " " &
..Fields("LastName")
End If
.Close
End With
Set rsta = New ADODB.Recordset
With rsta
Set .ActiveConnection = CurrentProject.Connection
.Source = "SELECT * FROM Employees"
.Open
.Filter = "LastName = 'Davolio' AND FirstName = 'Nancy'"
If .BOF And .EOF Then
Debug.Print "ADO: No match found"
Else
Debug.Print "ADO: " & .Fields("FirstName") & " " &
..Fields("LastName")
End If
.Close
End With

End Sub

BTW: If you are *only* interested in the records that meet your criteria,
the most efficient method is to specify the criteria in the SQL statement
when opening the recordset. Using FindFirst or Filter may make sense,
though, if you are going to do something with this subset of records then
change the criteria and process a different subset of the records returned
by the original SQL statement.

--
Brendan Reynolds

jonefer said:
it doesn't appear that there is a "filter" property for find (ADO)
help:
Find (Criteria, SkipRows, SearchDirection, Start)

The line that is giving me that row is the
Find - line itself.

My only way around this - previously was to use DAO, but now I'm stuck
because the criteria I need is more complex - Any other suggestions?


Brendan Reynolds said:
Re the 'improper use of object' error - what line of code is raising the
error?

Regardless of that, though, this code isn't going to work, because the
Criteria argument is not valid. From the Find topic in the ADO help file
...

<quote>
Only a single-column name may be specified in criteria. This method does
not
support multi-column searches.
</quote>

You may be able to use the Filter property instead - that does support
multi-column searches.

--
Brendan Reynolds

jonefer said:
The following setup works for reading and writing to records...
but I tried the same thing for a search and

I'm getting an "Improper use of object" error..
Is it because I am trying to search within a recordset with criteria
set?


=================================
With cmdAIC_New
.ActiveConnection = cn
.CommandText = "SELECT * FROM tbl_AIC_LOC WHERE [Environment]= '" &
sNewEnv & "' AND [ActualDateTime] = #" & dtNewDate & "#;"
.CommandType = adCmdText
End With

With rsAIC_New
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmdAIC_New
End With

rsAIC_New.Find Criteria:="[CustomizationRec]= '" & sCustRec & "' AND
[Location] = " & lLoc, _
SearchDirection:=adSearchForward
 
Ad

Advertisements

G

Guest

This was very helpful. Thank you.
I did want to use DAO in this manner, it's just that I'd been programming
for awhile outside Access so I started to mix the code.

Your examples are something that I can keep to set me straight about this.
thanks again!

Brendan Reynolds said:
Filter is a property of the ADO Recordset object ...

http://msdn.microsoft.com/library/en-us/ado270/htm/mdprofilter.asp

If you still want to try to track down the original error, my next question
would be how are sCustRec and lLoc declared?

BTW: I'm not sure what you mean about DAO and being stuck because the
criteria are more complex - the fact that your criteria are more complex
would seem to be an argument in favour of using DAO, as the DAO FindFirst,
FindNext etc. methods don't suffer from this limitation - they can handle
the multi-column criteria.

Here are two examples of searches using multi-column criteria, one using
ADO, the other using DAO ...

Public Sub TestFindFilter()

Dim db As DAO.Database
Dim rstd As DAO.Recordset
Dim rsta As ADODB.Recordset

Set db = CurrentDb
Set rstd = db.OpenRecordset("SELECT * FROM Employees")
With rstd
.FindFirst "LastName = 'Davolio' AND FirstName = 'Nancy'"
If .NoMatch Then
Debug.Print "DAO: No match found"
Else
Debug.Print "DAO: " & .Fields("FirstName") & " " &
..Fields("LastName")
End If
.Close
End With
Set rsta = New ADODB.Recordset
With rsta
Set .ActiveConnection = CurrentProject.Connection
.Source = "SELECT * FROM Employees"
.Open
.Filter = "LastName = 'Davolio' AND FirstName = 'Nancy'"
If .BOF And .EOF Then
Debug.Print "ADO: No match found"
Else
Debug.Print "ADO: " & .Fields("FirstName") & " " &
..Fields("LastName")
End If
.Close
End With

End Sub

BTW: If you are *only* interested in the records that meet your criteria,
the most efficient method is to specify the criteria in the SQL statement
when opening the recordset. Using FindFirst or Filter may make sense,
though, if you are going to do something with this subset of records then
change the criteria and process a different subset of the records returned
by the original SQL statement.

--
Brendan Reynolds

jonefer said:
it doesn't appear that there is a "filter" property for find (ADO)
help:
Find (Criteria, SkipRows, SearchDirection, Start)

The line that is giving me that row is the
Find - line itself.

My only way around this - previously was to use DAO, but now I'm stuck
because the criteria I need is more complex - Any other suggestions?


Brendan Reynolds said:
Re the 'improper use of object' error - what line of code is raising the
error?

Regardless of that, though, this code isn't going to work, because the
Criteria argument is not valid. From the Find topic in the ADO help file
...

<quote>
Only a single-column name may be specified in criteria. This method does
not
support multi-column searches.
</quote>

You may be able to use the Filter property instead - that does support
multi-column searches.

--
Brendan Reynolds

The following setup works for reading and writing to records...
but I tried the same thing for a search and

I'm getting an "Improper use of object" error..
Is it because I am trying to search within a recordset with criteria
set?


=================================
With cmdAIC_New
.ActiveConnection = cn
.CommandText = "SELECT * FROM tbl_AIC_LOC WHERE [Environment]= '" &
sNewEnv & "' AND [ActualDateTime] = #" & dtNewDate & "#;"
.CommandType = adCmdText
End With

With rsAIC_New
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmdAIC_New
End With

rsAIC_New.Find Criteria:="[CustomizationRec]= '" & sCustRec & "' AND
[Location] = " & lLoc, _
SearchDirection:=adSearchForward
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top