Binding a form to a saved table

[Karen Patalano]

I am trying to use a combo box to find a record but I keep getting the Set rs = Me.Recordset.Clone highlighted in yellow as an error.


Private Sub Combo8_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[residentnumber] = '" & Me![Combo8] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Can anyone help?

 

[boblarson]

Try using

Set rs = Me.RecordsetClone

without the dot between recordset and clone.

 

[gemma-the-husky]

would

dim rs as recordset be more accurate - or is object ok

 

[boblarson]

would

dim rs as recordset be more accurate - or is object ok

I would use

Dim rs As DAO.Recordset

myself, but the code as shown should be fine as object as that is how the wizard outputs it. But as it is possible to have both DAO and ADO checked (depending on what is going on and/or what version you have) it is probably better to use the rs As DAO.Recordset.

But the main error is that it should be Me.RecordsetClone and not Me.Recordset.Clone

 

[Karen Patalano]

I received a runtime error 7951 invalid reference to record set property when I took the . out.

 

[boblarson]

I received a runtime error 7951 invalid reference to record set property when I took the . out.

Hmm, works for me with my test database. In fact it worked with either. Do you have a DAO reference set? Which version of Access is this?

 

[Karen Patalano]

I put the . back and changed the rs as suggested, new error:
User error: user-defined type not defined

 

[Karen Patalano]

The original Access program was written in Access 2003, now using 2007. Not sure about the DAO reference set.

 

[boblarson]

I put the . back and changed the rs as suggested, new error:
User error: user-defined type not defined

That sounds like you do not have a DAO reference set. To set it you would go to TOOLS > REFERENCES in the VBA window and then scroll down until you see Microsoft DAO 3.x (where .x could be 51 or 6) unless you are on 2007 or 2010 and that would be the Access Database Engine.

 

[Karen Patalano]

I tried to use the wizard to remake the combo box to find a record and I was missing the third option....FInd a record on my form based on the value I selected in my combo box. I apparently need to bind the form to a saved Table or Query in order to be able to select the third option....not sure how to do this either.

 

[Karen Patalano]

I put in the DAO reference set and now I am back to the original error
Set rs = Me.Recordset.Clone I removed Dim rs As Object

 

[vbaInet]

Just wondering what the actual original error is? Plus what RecordsetType is your form set to?

 

[Karen Patalano]

I have a drop down: Choose a person which should bring up the chosen person in text boxes lname and fname...but the text boxes contain #Name? instead of the name. Once the names come up I have a choose a date to look at button that will bring up a report for that person on the date chosen. The original error appears when you choose a resident Set rs = Me.Recordset.Clone

 

[vbaInet]

But what is the full error message and code?

 

[boblarson]

I tried to use the wizard to remake the combo box to find a record and I was missing the third option....FInd a record on my form based on the value I selected in my combo box. I apparently need to bind the form to a saved Table or Query in order to be able to select the third option....not sure how to do this either.

Yes, your form needs to be bound to a table or query. To do so open the form in design view and on the data tab of the properties dialog select the table or query from the drop down under recordsource.

 

[gemma-the-husky]

karen - it definitely is not me.recordset.clone

as Bob pointed out

its me.RecordsetClone (all one word)

this usage creates a sort of mirrored copy of the recordset on which your form is based - that you can navigate and search (in code) indepedently of the form itself.

This is part of the DAO object library - hence Bob wanting you to check that DAO (Data Access Objects) was checked as a reference. Design a module, check tools/references and see if it shows DAO (3.6 probably) as a checked reference.

 

[Karen Patalano]

Run time error 91: object variable or With block variable not set

 

[Karen Patalano]

When I use Set rs = Me.RecordsetClone I get Runtime error 7951: invalid reference to the recordset property

 

[vbaInet]

Run time error 91: object variable or With block variable not set

This would normally happen if you don't have error handlers during the time of a trappable error. Compact & Repair your db and try again.

 

[boblarson]

When I use Set rs = Me.RecordsetClone I get Runtime error 7951: invalid reference to the recordset property

You will get errors with any if the form is not bound.