Multiple Criteria syntax for Find Method

[kdw]

what is the correct syntax for using mulitple criteria for the Find method?

I tried:

sTemp = "[FirstName] = '" & Me!First & "' AND [LastName] = '" & Me!Last & "'
rst.Find (sTemp)
If rst.EOF = False Then
Me.Undo
Me.Bookmark = rst.Bookmark
End If

and got a run-time error 91 - Object variable not set.

What I want to do is go to the existing record on the form if the user typed
in a name that already exist. Is there a better approach?

Thanks in advance!

 

[Brendan Reynolds]

If this is a DAO recordset, your syntax is correct. (There's a double quote
missing from the end, but I think that's just a typo in the newsgroup
posting - otherwise you'd be getting a different error message). If this is
an ADO recordset, you can't do that, the ADO Find method accepts only a
single condition, you have to use Filter for multiple conditions.

I suspect the error you're seeing has nothing to do with the Find syntax - I
think you haven't instantiated your recordset. If this is a DAO recordset,
do you have an OpenRecordset statement in the code prior to the code you
posted? Or if it is an ADO recordset, do you have a Set rst = New Recordset
statement prior to the code you posted?

 

[kdw]

My understanding was that Find is an ADO method and Findfirst is DAO. I
would like to keep all of my codes as ADO. Is there no other way around
doing a multiple criteria search?

But you are right about the error not relating to the Find method. I
haven't instantiate rst. But "Set rst = New Recordset" wouldn't work either
because if I do that, wouldn't I be trying to do a search on a new recordset
with nothing in it? Should I do a recordset clone first?

If this is a DAO recordset, your syntax is correct. (There's a double quote
missing from the end, but I think that's just a typo in the newsgroup
posting - otherwise you'd be getting a different error message). If this is
an ADO recordset, you can't do that, the ADO Find method accepts only a
single condition, you have to use Filter for multiple conditions.

I suspect the error you're seeing has nothing to do with the Find syntax - I
think you haven't instantiated your recordset. If this is a DAO recordset,
do you have an OpenRecordset statement in the code prior to the code you
posted? Or if it is an ADO recordset, do you have a Set rst = New Recordset
statement prior to the code you posted?

--
Brendan Reynolds (MVP)

what is the correct syntax for using mulitple criteria for the Find
method?

I tried:

sTemp = "[FirstName] = '" & Me!First & "' AND [LastName] = '" & Me!Last &
"'
rst.Find (sTemp)
If rst.EOF = False Then
Me.Undo
Me.Bookmark = rst.Bookmark
End If

and got a run-time error 91 - Object variable not set.

What I want to do is go to the existing record on the form if the user
typed
in a name that already exist. Is there a better approach?

Thanks in advance!

 

[Douglas J. Steele]

The ADO Find method only accepts a single argument: you cannot use it for
multi-column searches.

One option is to set the Filter property of the recordset.

On the other hand, is there a reason you're not using DAO?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

My understanding was that Find is an ADO method and Findfirst is DAO. I
would like to keep all of my codes as ADO. Is there no other way around
doing a multiple criteria search?

But you are right about the error not relating to the Find method. I
haven't instantiate rst. But "Set rst = New Recordset" wouldn't work
either
because if I do that, wouldn't I be trying to do a search on a new
recordset
with nothing in it? Should I do a recordset clone first?

If this is a DAO recordset, your syntax is correct. (There's a double
quote
missing from the end, but I think that's just a typo in the newsgroup
posting - otherwise you'd be getting a different error message). If this
is
an ADO recordset, you can't do that, the ADO Find method accepts only a
single condition, you have to use Filter for multiple conditions.

I suspect the error you're seeing has nothing to do with the Find
syntax - I
think you haven't instantiated your recordset. If this is a DAO
recordset,
do you have an OpenRecordset statement in the code prior to the code you
posted? Or if it is an ADO recordset, do you have a Set rst = New
Recordset
statement prior to the code you posted?

--
Brendan Reynolds (MVP)

what is the correct syntax for using mulitple criteria for the Find
method?

I tried:

sTemp = "[FirstName] = '" & Me!First & "' AND [LastName] = '" & Me!Last
&
"'
rst.Find (sTemp)
If rst.EOF = False Then
Me.Undo
Me.Bookmark = rst.Bookmark
End If

and got a run-time error 91 - Object variable not set.

What I want to do is go to the existing record on the form if the user
typed
in a name that already exist. Is there a better approach?

Thanks in advance!

 

[Brendan Reynolds]

You're right - I should have realised that if it has a Find method, rather
than FindFirst, FindNext etc. methods, it has to be an ADO recordset.

The other way to do a multiple criteria search is to specify the criteria in
the SQL statement when opening the recordset, but if your recordset is a
clone you never open it, so that option isn't available to you. Filtering
the recordset is no great chore, though.

If your recordset is a clone, it is instantiated when you clone it. Perhaps
an example might help to clarify ...

Private Sub Command18_Click()

Dim rst As ADODB.Recordset

'Recordset instantiated here ...
Set rst = Me.Recordset.Clone

'Don't ask ...
rst.Filter = "Address1 = 'Gelipemanu' AND Address2 = 'Xidutogo'"
If rst.EOF Then
MsgBox "No matching record"
Else
Me.Bookmark = rst.Bookmark
End If

End Sub

--
Brendan Reynolds (MVP)

My understanding was that Find is an ADO method and Findfirst is DAO. I
would like to keep all of my codes as ADO. Is there no other way around
doing a multiple criteria search?

But you are right about the error not relating to the Find method. I
haven't instantiate rst. But "Set rst = New Recordset" wouldn't work
either
because if I do that, wouldn't I be trying to do a search on a new
recordset
with nothing in it? Should I do a recordset clone first?

If this is a DAO recordset, your syntax is correct. (There's a double
quote
missing from the end, but I think that's just a typo in the newsgroup
posting - otherwise you'd be getting a different error message). If this
is
an ADO recordset, you can't do that, the ADO Find method accepts only a
single condition, you have to use Filter for multiple conditions.

I suspect the error you're seeing has nothing to do with the Find
syntax - I
think you haven't instantiated your recordset. If this is a DAO
recordset,
do you have an OpenRecordset statement in the code prior to the code you
posted? Or if it is an ADO recordset, do you have a Set rst = New
Recordset
statement prior to the code you posted?

--
Brendan Reynolds (MVP)

what is the correct syntax for using mulitple criteria for the Find
method?

I tried:

sTemp = "[FirstName] = '" & Me!First & "' AND [LastName] = '" & Me!Last
&
"'
rst.Find (sTemp)
If rst.EOF = False Then
Me.Undo
Me.Bookmark = rst.Bookmark
End If

and got a run-time error 91 - Object variable not set.

What I want to do is go to the existing record on the form if the user
typed
in a name that already exist. Is there a better approach?

Thanks in advance!

 

[Larry Linson]

IIRC, ADO's Find does not support multiple criteria. You can get around that
by creating SQL with the other criteria included, or create an SQL statement
that you execute.

On the other hand, DAO works very well, so I don't see a compelling need to
use ADO. And, DAO's FindFirst, FindNext, FindPrevious, and FindLast do
accept multiple fields for criteria.

But I do understand that, whichever you choose to use, it makes for a more
consistent application if you don't mix the two methods.

Larry Linson
Microsoft Access MVP

My understanding was that Find is an ADO method and Findfirst is DAO. I
would like to keep all of my codes as ADO. Is there no other way around
doing a multiple criteria search?

But you are right about the error not relating to the Find method. I
haven't instantiate rst. But "Set rst = New Recordset" wouldn't work either
because if I do that, wouldn't I be trying to do a search on a new recordset
with nothing in it? Should I do a recordset clone first?

If this is a DAO recordset, your syntax is correct. (There's a double quote
missing from the end, but I think that's just a typo in the newsgroup
posting - otherwise you'd be getting a different error message). If this is
an ADO recordset, you can't do that, the ADO Find method accepts only a
single condition, you have to use Filter for multiple conditions.

I suspect the error you're seeing has nothing to do with the Find syntax - I
think you haven't instantiated your recordset. If this is a DAO recordset,
do you have an OpenRecordset statement in the code prior to the code you
posted? Or if it is an ADO recordset, do you have a Set rst = New Recordset
statement prior to the code you posted?

--
Brendan Reynolds (MVP)

what is the correct syntax for using mulitple criteria for the Find
method?

I tried:

sTemp = "[FirstName] = '" & Me!First & "' AND [LastName] = '" & Me!Last &
"'
rst.Find (sTemp)
If rst.EOF = False Then
Me.Undo
Me.Bookmark = rst.Bookmark
End If

and got a run-time error 91 - Object variable not set.

What I want to do is go to the existing record on the form if the user
typed
in a name that already exist. Is there a better approach?

Thanks in advance!

 

[Brendan Reynolds]

I should probably point out, for the benefit of those who may read this
later in the archives, that the example will only work if the form's
Recordset property returns an ADO recordset. By default, this is the
behaviour in an ADP but not in an MDB. See the following URL for further
explanation ...

http://www.trigeminal.com/usenet/usenet022.asp?1033

--
Brendan Reynolds (MVP)

You're right - I should have realised that if it has a Find method, rather
than FindFirst, FindNext etc. methods, it has to be an ADO recordset.

The other way to do a multiple criteria search is to specify the criteria
in the SQL statement when opening the recordset, but if your recordset is
a clone you never open it, so that option isn't available to you.
Filtering the recordset is no great chore, though.

If your recordset is a clone, it is instantiated when you clone it.
Perhaps an example might help to clarify ...

Private Sub Command18_Click()

Dim rst As ADODB.Recordset

'Recordset instantiated here ...
Set rst = Me.Recordset.Clone

'Don't ask ...
rst.Filter = "Address1 = 'Gelipemanu' AND Address2 = 'Xidutogo'"
If rst.EOF Then
MsgBox "No matching record"
Else
Me.Bookmark = rst.Bookmark
End If

End Sub

--
Brendan Reynolds (MVP)

My understanding was that Find is an ADO method and Findfirst is DAO. I
would like to keep all of my codes as ADO. Is there no other way around
doing a multiple criteria search?

But you are right about the error not relating to the Find method. I
haven't instantiate rst. But "Set rst = New Recordset" wouldn't work
either
because if I do that, wouldn't I be trying to do a search on a new
recordset
with nothing in it? Should I do a recordset clone first?

If this is a DAO recordset, your syntax is correct. (There's a double
quote
missing from the end, but I think that's just a typo in the newsgroup
posting - otherwise you'd be getting a different error message). If this
is
an ADO recordset, you can't do that, the ADO Find method accepts only a
single condition, you have to use Filter for multiple conditions.

I suspect the error you're seeing has nothing to do with the Find
syntax - I
think you haven't instantiated your recordset. If this is a DAO
recordset,
do you have an OpenRecordset statement in the code prior to the code you
posted? Or if it is an ADO recordset, do you have a Set rst = New
Recordset
statement prior to the code you posted?

--
Brendan Reynolds (MVP)


what is the correct syntax for using mulitple criteria for the Find
method?

I tried:

sTemp = "[FirstName] = '" & Me!First & "' AND [LastName] = '" &
Me!Last &
"'
rst.Find (sTemp)
If rst.EOF = False Then
Me.Undo
Me.Bookmark = rst.Bookmark
End If

and got a run-time error 91 - Object variable not set.

What I want to do is go to the existing record on the form if the user
typed
in a name that already exist. Is there a better approach?

Thanks in advance!

 

[kdw]

Brendan, I tried your ADO example but I am getting a Type Mistmatch on this
line:
Set rst = Me.Recordset.Clone

After reading the article you suggested, could it be that the recordset of
the form is a DAO recordset (it's an MDB) and I am trying to set it to ADO?
If this is the case, how do I set the form's recordset type? The article
didn't say how.

I didn't think this was going to be so complicated but I am still learning
all the subtleties of ADO and DAO. So, if possible I would prefer not mix
the two methods in my app, thus my hesitation in using the DAO method in this
case even though it would probably solve my problem easier.

Should I try a different approach all together? I just would like a clean
way of having the form to go to a specific record based on multiple criteria.

 

[Brendan Reynolds]

The article does explain that, but I'll attempt to clarify a little ...

In an MDB, the form's Recordset and RecordsetClone property will return a
DAO recordset unless you have previously assigned a recordset to the form's
Recordset property. If you have assigned a recordset, both the Recordset and
RecordsetClone properties will return the same type of recordset that you
assigned - if you assign a DAO recordset, they'll return a DAO recordset, if
you assigned an ADO recordset, that's what they'll return. In earlier
versions of Access, assigning an ADO recordset to a form's Recordset made
the form read-only, as the article says. This is no longer the case in
Access 2003, the result can be a read/write form, but you have to be careful
to get all the properties of the Recordset correct. For example ...

Option Compare Database
Option Explicit

Private mrst As ADODB.Recordset

Private Sub Form_Close()

If Not mrst Is Nothing Then
If mrst.State <> adStateClosed Then
mrst.Close
End If
End If

End Sub

Private Sub Form_Open(Cancel As Integer)

Set mrst = New ADODB.Recordset
With mrst
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = "SELECT * FROM tblTest"
.Open
End With
Set Me.Recordset = mrst

End Sub

There's an MSDN article on this subject at ...

http://msdn.microsoft.com/library/d...us/vbaac11/html/acproRecordset_HV05251093.asp

I can not recommend trying to develop any non-trivial MDB application
without using DAO. That's like trying to work with one hand tied behind your
back.