Learning to work with recordsets, and failing miserably

[Joseph Ellis]

Hello all,

I'm delving into working with recordsets for the first time, and
having a heck of a time getting anything to work properly.

I'm trying to use FindFirst to jump to a record in a main form
(frmHouseholds) which has just been selected from a popup "search
results" form. After selecting the desired record in
frmSearchResults, the user clicks a "Done" command button, which hides
frmSearchResults, allowing focus to return to frmHouseholds.

At this point I'd like to jump to the selected record in
frmHouseholds, using hhID as the uniqe value to find:

Dim rst As Recordset
Set rst = forms!frmHouseholds.RecordsetClone
rstDesiredRec.FindFirst "[hhid] = " & Forms!frmSearchResults.hhID

Once I get to the desired record, I'll close frmSearchResults, which
is still hidden at this point.

The problem is that when I click the "Done" button in
frmSearchResults, I get an error: "Run-time error '13': Type
mismatch", and the debugger highlights:

Set rst = forms!frmHouseholds.RecordsetClone

This also happens if I try:

Set rst = Me.RecordsetClone

What am I doing wrong? This is my first time working with recordsets;
I think I must be missing some basic concept.

Thanks,
Joseph

 

[Dirk Goldgar]

Hello all,

I'm delving into working with recordsets for the first time, and
having a heck of a time getting anything to work properly.

I'm trying to use FindFirst to jump to a record in a main form
(frmHouseholds) which has just been selected from a popup "search
results" form. After selecting the desired record in
frmSearchResults, the user clicks a "Done" command button, which hides
frmSearchResults, allowing focus to return to frmHouseholds.

At this point I'd like to jump to the selected record in
frmHouseholds, using hhID as the uniqe value to find:

Dim rst As Recordset
Set rst = forms!frmHouseholds.RecordsetClone
rstDesiredRec.FindFirst "[hhid] = " & Forms!frmSearchResults.hhID

Once I get to the desired record, I'll close frmSearchResults, which
is still hidden at this point.

The problem is that when I click the "Done" button in
frmSearchResults, I get an error: "Run-time error '13': Type
mismatch", and the debugger highlights:

Set rst = forms!frmHouseholds.RecordsetClone

This also happens if I try:

Set rst = Me.RecordsetClone

What am I doing wrong? This is my first time working with recordsets;
I think I must be missing some basic concept.

Thanks,
Joseph

You're not doing so badly. In all probability you're using Access 2000
or 2002, which by default do not include a reference to the DAO object
library, but do include a reference to the ADO library. ADO and DAO
both define a Recordset object, but they aren't compatible. It's the
DAO Recordset you want here, so you need to do two things:

1. In the VB Editor environment, click Tools -> References..., locate
Microsoft DAO 3.6 Object Library in the list, and put a check mark in
the box next to it.

2. Either remove (uncheck) the reference to ActiveX Data Objects 2.x
Library or else declare your recordset with the DAO qualifier:

Dim rst As DAO.Recordset

There are other objects that defined in both libraries, and should best
be "disambiguated" by adding the qualifier. They are:

Connection, Error, Errors, Field, Fields,
Parameter, Parameters, Property, Properties and Recordset

 

[Joseph Ellis]

Thanks for your help, Dirk...

I followed your instructions, and am no longer getting the error.

My new problem is that I can't get FindFirst to work. I'm not getting
an error message...it's just that nothing happens. The current record
in frmHouseholds is not changing to the record selected in
frmSearchResults.

Here's what I've got:

Dim strToFind
Dim rst As DAO.Recordset
Set rst = Forms!frmHouseholds.RecordsetClone
strToFind = "[hhID] = " & Forms!frmSearchResults.hhID
rst.FindFirst strToFind

'Close the form
DoCmd.Close acForm, "frmSearchResults", acSaveNo

Hello all,

I'm delving into working with recordsets for the first time, and
having a heck of a time getting anything to work properly.

I'm trying to use FindFirst to jump to a record in a main form
(frmHouseholds) which has just been selected from a popup "search
results" form. After selecting the desired record in
frmSearchResults, the user clicks a "Done" command button, which hides
frmSearchResults, allowing focus to return to frmHouseholds.

At this point I'd like to jump to the selected record in
frmHouseholds, using hhID as the uniqe value to find:

Dim rst As Recordset
Set rst = forms!frmHouseholds.RecordsetClone
rstDesiredRec.FindFirst "[hhid] = " & Forms!frmSearchResults.hhID

Once I get to the desired record, I'll close frmSearchResults, which
is still hidden at this point.

The problem is that when I click the "Done" button in
frmSearchResults, I get an error: "Run-time error '13': Type
mismatch", and the debugger highlights:

Set rst = forms!frmHouseholds.RecordsetClone

This also happens if I try:

Set rst = Me.RecordsetClone

What am I doing wrong? This is my first time working with recordsets;
I think I must be missing some basic concept.

Thanks,
Joseph

You're not doing so badly. In all probability you're using Access 2000
or 2002, which by default do not include a reference to the DAO object
library, but do include a reference to the ADO library. ADO and DAO
both define a Recordset object, but they aren't compatible. It's the
DAO Recordset you want here, so you need to do two things:

1. In the VB Editor environment, click Tools -> References..., locate
Microsoft DAO 3.6 Object Library in the list, and put a check mark in
the box next to it.

2. Either remove (uncheck) the reference to ActiveX Data Objects 2.x
Library or else declare your recordset with the DAO qualifier:

Dim rst As DAO.Recordset

There are other objects that defined in both libraries, and should best
be "disambiguated" by adding the qualifier. They are:

Connection, Error, Errors, Field, Fields,
Parameter, Parameters, Property, Properties and Recordset

 

[Dirk Goldgar]

Thanks for your help, Dirk...

I followed your instructions, and am no longer getting the error.

My new problem is that I can't get FindFirst to work. I'm not getting
an error message...it's just that nothing happens. The current record
in frmHouseholds is not changing to the record selected in
frmSearchResults.

Here's what I've got:

Dim strToFind
Dim rst As DAO.Recordset
Set rst = Forms!frmHouseholds.RecordsetClone
strToFind = "[hhID] = " & Forms!frmSearchResults.hhID
rst.FindFirst strToFind

'Close the form
DoCmd.Close acForm, "frmSearchResults", acSaveNo

That's because you've left out the final, essential part of the process:
synchronizing the form to the position of the recordsetclone. Do this:

Dim rst As DAO.Recordset

With Forms!frmHouseholds
Set rst = .RecordsetClone
rst.FindFirst "[hhID] = " & Forms!frmSearchResults.hhID
If Not rst.NoMatch Then
.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End With

'Close the form
DoCmd.Close acForm, "frmSearchResults", acSaveNo

If this code is running on frmSearchResults, as I guess, you can
simplify it a trifle more:

Dim rst As DAO.Recordset

With Forms!frmHouseholds
Set rst = .RecordsetClone
rst.FindFirst "[hhID] = " & Me.hhID
If Not rst.NoMatch Then
.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End With

'Close the form
DoCmd.Close acForm, Me.Name, acSaveNo

 

[Joseph Ellis]

That's because you've left out the final, essential part of the process:
synchronizing the form to the position of the recordsetclone. Do this:

Dim rst As DAO.Recordset

With Forms!frmHouseholds
Set rst = .RecordsetClone
rst.FindFirst "[hhID] = " & Forms!frmSearchResults.hhID
If Not rst.NoMatch Then
.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End With

'Close the form
DoCmd.Close acForm, "frmSearchResults", acSaveNo

Aye, there's the rub. That did the trick. Thank you so much.

But man, I read all through the VB help files about recordsets and
FindFirst and everything else I could think of, and never once came
across anything about bookmarks or synchronizing. There was a little
blurb about checking NoMatch, but that was it.

Is there some book or other resource that everyone but me has? <g>

Thanks again,
Joseph

 

[Dirk Goldgar]

Is there some book or other resource that everyone but me has? <g>

Experience? <g> Actually, there are some good books out there, and you
can do a Google Groups search of these newsgroups if you'd like to see
what various people have recommended.

 

[Joseph Ellis]

Experience? <g> Actually, there are some good books out there, and you
can do a Google Groups search of these newsgroups if you'd like to see
what various people have recommended.

Aha, yes, I knew there was something!