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
