Learning to work with recordsets, and failing miserably

  • Thread starter Thread starter Joseph Ellis
  • Start date Start date
J

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
 
Joseph Ellis said:
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
Click to expand...

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
 
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

Joseph Ellis said:
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
Click to expand...

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
Click to expand...
 
Joseph Ellis said:
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
Click to expand...

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
 
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
Click to expand...

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
 
Joseph Ellis said:
Is there some book or other resource that everyone but me has? <g>
Click to expand...

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.
 
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.
Click to expand...

Aha, yes, I knew there was something!
 
Back
Top