Recordsetclone problem

[gurumum]

Hi ,

My need in the form I'm talking about is very simple. I say this
because I've seen posts that say recordset clone can be used in a snap
to find a record.

I have not used access before. The little db work I did used oracle 8i
or sql server. So the "user friendliness" of Access just doesnt work
for me.

Scenario:

I have a form with 6 fields. These are 6 fields that are bound to each
of the 6 fields in a database. In order for the user to find a
particular record, I use a combobox (called combo_Find_Conf) that lists
the primary key field - conf_id of all the records.
When the user chooses one of the conf_id from the dropdown, the 6
fields in the form need to be populated with the corresponding data.

This is the code I used:

Private Sub combo_Find_Conf_AfterUpdate()

Dim rsConf As Recordset

If Not IsNull(Me.combo_Find_Conf) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rsConf = Me.RecordsetClone
rsConf.FindFirst "[conf_id] = " & Me.combo_Find_Conf.Value
If rsConf.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rsConf.Bookmark
End If
Set rsConf = Nothing

Next

End If

End Sub

I get the following error message:
"Compile error:
method or data member not found"
and the function "FindFirst" is where the cursor goes to after "OK"ing
the error.

Why does it say FindFirst is not found?

And if someone has a little extra time, I'd really appreciate how this
cloning happens in the background.

Subsequently, I tried using Find instead of FindFirst- didnt work.
It errored out at Set rsConf = Me.RecordsetClone
with the error "Type Mismatch"

This happened (i think) because Recordsetclone returns DAO's by
default.

so i tried using DAO.
I'm working on Access 2003. I was of the assumption that DAO would work
in this as well.

"Compiler error : user defined type not defined"

Its a simple "find a record" operation and man! its such a struggle.

any suggestions?

Thanks
Madhu

Looking to hear from you.

Madhu

 

[Brendan Reynolds]

Make sure you have a reference (in the VBA editor, select References from
the Tools menu) to the Microsoft DAO 3.6 Object Library. If you also have a
reference to the Microsoft ActiveX Data Objects 2.x Library, it may help to
move the DAO reference up above the ADO reference in the list. Change 'Find'
back to 'FindFirst' when using DAO.

See the following URL for some of the background to all this ...
http://www.mvps.org/access/bugs/bugs0031.htm

 

[Robert Morley]

Actually, as of Access 2000, the RecordsetClone object is of undefined type
(i.e., late bound), if I remember correctly. Typically, it'll return an ADO
(not DAO) recordset, and I suspect that's the source of your problems. I've
strictly been using ADP's for the last year, so I don't remember under what
circumstances it returns a DAO object...maybe it's ADO in ADPs and DAO in
MDBs? (Can someone else comment on this...I'm a little out-of-date for 2003
in any event.)

Assuming I'm correct in thinking that the problem is that it's returning an
ADO recordset, if you change your code as follows, I *believe* it should
work, but I'm typing off-the-cuff here, so no promises. Oh, and make
sure you have a reference to ADODB in your references, of course.

Private Sub combo_Find_Conf_AfterUpdate()

'Added ADODB. below
Dim rsConf As ADODB.Recordset

If Not IsNull(Me.combo_Find_Conf) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rsConf = Me.RecordsetClone
'Added rsConf.MoveFirst
rsConf.MoveFirst
'Changed FindFirst to Find
rsConf.Find "[conf_id] = " & Me.combo_Find_Conf.Value
'Changed NoMatch to EOF
If rsConf.EOF Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rsConf.Bookmark
End If
Set rsConf = Nothing

Next

End If

End Sub

 

[Ken Snell [MVP]]

And would be good to disambiguate the Dim of the Recordset:

Dim rsConf As DAO.Recordset

--

Ken Snell
<MS ACCESS MVP>

Make sure you have a reference (in the VBA editor, select References from
the Tools menu) to the Microsoft DAO 3.6 Object Library. If you also have
a reference to the Microsoft ActiveX Data Objects 2.x Library, it may help
to move the DAO reference up above the ADO reference in the list. Change
'Find' back to 'FindFirst' when using DAO.

See the following URL for some of the background to all this ...
http://www.mvps.org/access/bugs/bugs0031.htm

--
Brendan Reynolds

Hi ,

My need in the form I'm talking about is very simple. I say this
because I've seen posts that say recordset clone can be used in a snap
to find a record.

I have not used access before. The little db work I did used oracle 8i
or sql server. So the "user friendliness" of Access just doesnt work
for me.

Scenario:

I have a form with 6 fields. These are 6 fields that are bound to each
of the 6 fields in a database. In order for the user to find a
particular record, I use a combobox (called combo_Find_Conf) that lists
the primary key field - conf_id of all the records.
When the user chooses one of the conf_id from the dropdown, the 6
fields in the form need to be populated with the corresponding data.

This is the code I used:

Private Sub combo_Find_Conf_AfterUpdate()

Dim rsConf As Recordset

If Not IsNull(Me.combo_Find_Conf) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rsConf = Me.RecordsetClone
rsConf.FindFirst "[conf_id] = " & Me.combo_Find_Conf.Value
If rsConf.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rsConf.Bookmark
End If
Set rsConf = Nothing

Next

End If

End Sub

I get the following error message:
"Compile error:
method or data member not found"
and the function "FindFirst" is where the cursor goes to after "OK"ing
the error.

Why does it say FindFirst is not found?

And if someone has a little extra time, I'd really appreciate how this
cloning happens in the background.

Subsequently, I tried using Find instead of FindFirst- didnt work.
It errored out at Set rsConf = Me.RecordsetClone
with the error "Type Mismatch"

This happened (i think) because Recordsetclone returns DAO's by
default.

so i tried using DAO.
I'm working on Access 2003. I was of the assumption that DAO would work
in this as well.

"Compiler error : user defined type not defined"

Its a simple "find a record" operation and man! its such a struggle.

any suggestions?

Thanks
Madhu

Looking to hear from you.

Madhu

 

[Brendan Reynolds]

In an MDB, it will return a DAO recordset *unless* you have previously
assigned an ADO recordset to the Recordset property of the form.

A useful article on the subject is at the following URL. Things have changed
a little since the article was written, in that, in later versions of
Access, assigning an ADO recordset to the Recordset property of a form does
not necessarily result in a read-only form, as it did in Access 2000. Other
than that, though, I believe the article is still relevant ...

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

 

[gurumum]

Thanks people!

I learned from one of the forums that RecordsetClone returned DAOs and
I experimented with moving the reference to DAO 3.6 to the top of the
library. This is exactly what Brendan has suggested and it worked.

Robert : thanks a lot for your effort. But I did try to get it work
with ADO but it so turns out that no matter what, recordsetclone
returns DAO. So I had to go the DAO way. Thanks anyway!

So if you're usin ADO in your work but need to use recordsetclone for
some purpose, this is a sample of the working code:


Private Sub combo_Find_Conf_AfterUpdate()

Dim rsConf As DAO.Recordset

If Not IsNull(Me.combo_Find_Conf) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rsConf = Me.RecordsetClone
rsConf.FindFirst "[conf_id] = " & Me.combo_Find_Conf.Value
If (rsConf.RecordCount = 0) Then
MsgBox "Not found"
Else
'Display the found record in the form.
Me.Bookmark = rsConf.Bookmark
End If
Set rsConf = Nothing

End If
End Sub

Thanks to all of you. I really appreciate your timely help.

Madhu