Link to home
Start Free TrialLog in
Avatar of rjahrman
rjahrman

asked on

Error 7951 (Recordset Problem)

I've been asked to fix a problem in an Access Database that was done by someone else.  Anyway, it has:

Private Sub cboParentSelect_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
    Set rs = Me.RecordsetClone
    rs.FindFirst "tblcontacts.[contactID] = " & Str(Me![cboParentSelect])
    Me.Bookmark = rs.Bookmark
End Sub

An error occurrs on the "Set rs = Me.RecordsetClone" line, error 7951 (something about an invalid call to recordset).  I tried changing the line to "Set rs = Forms![frmParentSelect].RecordsetClone" but it didn't make a difference.  I also tried changing "Dim rs As Object" to "Dim rs As DAO.Recordset".

Any ideas?
Avatar of ysageev
ysageev

To me it seems that me does not have a valid recordset at the time of instantiation.  Put a breakpoint at the "set rs" statement and check to see that Me.RecordSet isn't null.
Avatar of rjahrman

ASKER

Sorry, I'm new to Access, though I am a programmer (I mainly do Perl, and some C++ . . . I'm trying to remember this stuff from my VB days).

I added a breakpoint, but then do I go into the Object Browser?
After you've added the breakpoint click the stop button at the top of VB.  Then go back to your form and close it, open it again, and click the button (or what have you) that generates the event.  IT will stop at the breakpoint.
I added a watch to "Me".

After the breakpoint, I looked at the watch, expanded "Me", and looked at the objects within it.  It went from Recordselectors to RecordsetClone . . . didn't even have a Recordset object.

Is this what you meant?  What should I do?

The odd thing is that they said that everything used to work . . . but for whatever reason it hasn't worked in the last month or so.
Avatar of Rey Obrero (Capricorn1)
rjahrman,

What are you trying to achieve here?

combo boxes are use to select field values from tables/queries or list or
to add records if it is not on the list of the row source for the combo box.
A breakpoint is a red dot on the left side of the window next to the line of code in question.  Open up the VB code, look at the grey bar on the left side, and click once on it.  A red dot will appear.

<<The odd thing is that they said that everything used to work . . . but for whatever reason it hasn't worked in the last month or so.>>

You should tell us more about the problem if this is the case.  
Cap,

what is a bookmark?  I've looked it up in the help and am not sure what it is supposed to do.  ;(

"You can use the Bookmark property with forms to set a bookmark that uniquely identifies a particular record in the form's underlying table, query, or SQL statement. Read/write Variant."


I thought all records related to the form were uniquely identified by the very nature of the database....
like what it is called, it function as a boomark as in placing something in between pages of a book to
know or remember where you stop reading, so anytime you continue your reading you know where to begin.
So cap, how does a bookmark differ from a form's recordsourse?
Recordsource of a form can be the records from a table or selected records based on a query.
Bookmark pertains to a single record.
to better explain this.
A,B,C,D,E  this are the records that comprise the RecordSource of a form coming from a Table or result of Query.

Bookmarking record A is the same as me putting a RedMarker to A, so if you put them in a container
and mixed them, it will be easy for me to find A.
No cap, but the record is A.   A form doesn't have more that one record as its source.  

If what you say a bookmark is then a bookmark is a "pause" between fields when iterating through a record, but even that is weird.
The declaration:

Dim rs As Object

is suspect. It should be:

Dim rs As Recordset

I suppose the error is due to this.
>>   A form doesn't have more that one record as its source.

This may or may not be true, depending on your form's underlying recordsource. If your form's source is "SELECT * FROM tblCustomers", then the form's recordsource will contain every record in tblCustomers. If you qualify that statements - "SELECT * FROM tblCustomers WHERE lngCustID=2" - then your form would contain only 1 record (assuming lngCustID is unique to tblCustomers, of course).

Online help re: Bookmarks gives a pretty clear picture of what a bookmark is and what it does.

To the OP: Try checking your references to make sure you have a reference set to the DAO library ("Microsoft DAO xx Library" where xx is the appropriate version).
Correct. Or a

Dim rs As DAO.Recordset

would be safer.
Combining above comments use:

Private Sub cboParentSelect_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As DAO.recordset
    Set rs = Me.RecordsetClone
    rs.FindFirst "contactID = " & Str(Me![cboParentSelect])
    Me.Bookmark = rs.Bookmark
End Sub

For this to work you need to check or the DAO library is checked in the references.
To do so open Tools/References and when you see no checked line with "Microsoft DAO version #.##" scroll down and check it.

Nic;o)
> I also tried changing "Dim rs As Object" to "Dim rs As DAO.Recordset".

So is the problem that DAO might not be in my references?
When you have typed DAO. then after the dot a list with "recordset" as option will appear when the DAO library is in your references, when there's no list just follow my instructions above.

Nic;o)
Have a look at this one: http://www.utteraccess.com/forums/access/access171659.html

And try the same: Change 'Me.RecordsetClone' with the appropriate:

Forms![YourFormName].RecordsetClone

This is if you want the recordset of a main form. For a subform, try:

Forms![YourFormName]![YourSubFormName].RecordsetClone
The error 7951:
You entered an expression that has an invalid reference to the RecordsetClone property.@For example, you may have used the RecordsetClone property with a form or report that isn't based on a table or query.

So it looks like you 1. Do not have your foem bound to a recordset or 2. You are calling this event from the Form's Open event and the recordset has not been loaded yet, if so try changing it to be caled from the Load event which happens AFTER the open event.

...

These next items do not directly relate to the problem but can simplify things a bit. You also do not need the DAO reference.

You don't need the recordset clone at all, you can use the recordset object itself.
And you don't need the conversion of Str(Me![cboParentSelect]) is just making more work.

Private Sub cboParentSelect_AfterUpdate()
    Me.Recordset.FindFirst "contactID = " & Me.cboParentSelect.Value
End Sub

Steve
Sorry, Access isn't my language of choice . . . I've just been asked to fix this problem because the original developer isn't around anymore.

How do I bind a Recordset to a form?  Also, how do I find out if I am working with a subform (and would need to do Forms![YourFormName]![YourSubFormName].RecordsetClone)?

Thanks for all of the help.
Hmm, first try to check the references.

Nic;o)
How do I find out if I am working with a subform: The easiest is to try the 'Form' syntax first. If it works, you win. If it doesn't, you get another error message... I don't see a better solution, since you don't know which table is the form based... Otherwise you have to research too deeply...

Orni the lazy developper.
Orni,
you are not alone!!!

<lol>
rey;-)
"How do I bind a Recordset to a form? "
You enter a SQL statement or the name of a saved query or the name of a table.

Steve
UPDATE: The reference _was_ checked.

As suggested, I changed the code to:
Me.Recordset.FindFirst "contactID = " & Me.cboParentSelect.Value

...But now I get a message that the Recordset object doesn't exist (or something like that).  Where should I go from here?
is there anything in the recordsource property of the form?
are there any records being returned before the cboParentSelect_AfterUpdate() event?

Steve
"are there any records being returned before the cboParentSelect_AfterUpdate() event?"

How would I know?

Sorry, as I said before I'm not an Access developer at all (I'm more towards Perl and C++, though I have done a little VB in the past); I've just been helping a non-profit with some things and they _really_ need this fixed.
<unsubscribing>
I've raised the point value . . .
stevbe:
1) I'm not there now, but I'll check when I'm in their office next.  But I vaguely remember it being -1 or null (or the like).
2) If I understand you right, there aren't . . . this is the first event in the form that has code written for it.
OK ... Open the form in design mode and look at the Recordsource property of the form ... is there anything there?

Steve
For the form itself, the entire Data tab in its properties is blank.

In the combo box with the data, though, (which is what the event is happening on, and which has the actualy data in it that's being selected) the Data tab shows Control Source as blank, Row Source Type as "Table/Query," and Row Source as qryCboEditParent.

Note that this is just a subform.
Hmm . . . actually, I think that's just the "Detail" properties where the Data tab was blank.  I just right-clicked on the form . . . don't know how else I would do it, though.
Also, BTW, when I run the code using As DAO.Recordset (instead of As Object) I now get a "Method or data member not found" error on "Set rs = Me.Recordset".
ASKER CERTIFIED SOLUTION
Avatar of stevbe
stevbe

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
    Accept: stevbe {http:#11326879}

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

routinet
EE Cleanup Volunteer