Full Version: RecordsetClone Problem On Northwind's Customer Phone List
UtterAccess Forums > Microsoft Access > Access Forms
clocker
When attempting to select a letter in a form copied from the Northwind's Customer Phone List Form, I am now getting an error message that says "The object doesn't contain the Automation object "RecordsetClone'".
I never had that problem before when I was using Access 2003, but ever since I upgraded to 2007 I keep getting this error.
It does not appear to be a problem in bringing up the sought after page after I close the warning message.
Any help would be greatly appreciated.
Jeff
RuralGuy
It sounds like a problem between DAO and ADO. Are your variables disambiguated? Meaning do you specify a library when you define your RecordSet variables (Dim rs As DAO.RecordSet)?
rsindle
The way RuralGuy says to disambiguate is the right way to go, however, to quickly TEST this theory, go to a module, and under Tools/References, see if ADO is listed ABOVE DAO. If so, just move the DAO reference above the ADO reference and all these errors should go away.
HOWEVER, it would be good to go in and be explicit in your code the way RuralGuy mentions.
Rob
clocker
I do not see anywhere where the RecordSet variables have been defined.
On the Properties Sheet the Recordset Type is set to Dynaset.
Jeff
clocker
The DOA 3.6 library is installed, but there is not any DOA Library installed.
will try and attach a copy of the error.
Jeff
clocker
Sorry, it appears this is the error message I received after converting the Macro to VB code.
eff
clocker
This is the error message I get when trying to run it with the macro.
eff
RuralGuy
Can you post the code generated when you converted the Macro to code please?
clocker
Here it is;
ption Compare Database
'------------------------------------------------------------
' Customer_Phone_List_Alpha_Buttons
'
'------------------------------------------------------------
Function Customer_Phone_List_Alpha_Buttons()
On Error GoTo Customer_Phone_List_Alpha_Buttons_Err
With CodeContextObject
' Attached to the Customer Phone List form.
' Attached to AfterUpdate event of ContactLastNameFilters option group.
If (.ContactLastNameFilters = 1) Then
' Filter for contact last names that start with A, , , , , or .
DoCmd.ApplyFilter "", "[ContactLastName] Like ""[A]*"""
End If
If (.ContactLastNameFilters = 2) Then
' B
DoCmd.ApplyFilter "", "[ContactLastName] Like ""B*"""
End If
If (.ContactLastNameFilters = 3) Then
' C or
DoCmd.ApplyFilter "", "[ContactLastName] Like ""[C]*"""
End If
If (.ContactLastNameFilters = 4) Then
' D
DoCmd.ApplyFilter "", "[ContactLastName] Like ""D*"""
End If
If (.ContactLastNameFilters = 5) Then
' E, , , , or
DoCmd.ApplyFilter "", "[ContactLastName] Like ""[E]*"""
End If
If (.ContactLastNameFilters = 6) Then
' F
DoCmd.ApplyFilter "", "[ContactLastName] Like ""F*"""
End If
If (.ContactLastNameFilters = 7) Then
' G
DoCmd.ApplyFilter "", "[ContactLastName] Like ""G*"""
End If
If (.ContactLastNameFilters = 8) Then
' H
DoCmd.ApplyFilter "", "[ContactLastName] Like ""H*"""
End If
If (.ContactLastNameFilters = 9) Then
' I, , , , or
DoCmd.ApplyFilter "", "[ContactLastName] Like ""[I]*"""
End If
If (.ContactLastNameFilters = 10) Then
' J
DoCmd.ApplyFilter "", "[ContactLastName] Like ""J*"""
End If
If (.ContactLastNameFilters = 11) Then
' K
DoCmd.ApplyFilter "", "[ContactLastName] Like ""K*"""
End If
If (.ContactLastNameFilters = 12) Then
' L
DoCmd.ApplyFilter "", "[ContactLastName] Like ""L*"""
End If
If (.ContactLastNameFilters = 13) Then
' M
DoCmd.ApplyFilter "", "[ContactLastName] Like ""M*"""
End If
If (.ContactLastNameFilters = 14) Then
' N, or
DoCmd.ApplyFilter "", "[ContactLastName] Like ""[N]*"""
End If
If (.ContactLastNameFilters = 15) Then
' O, , , , , or
DoCmd.ApplyFilter "", "[ContactLastName] Like ""[O]*"""
End If
If (.ContactLastNameFilters = 16) Then
' P
DoCmd.ApplyFilter "", "[ContactLastName] Like ""P*"""
End If
If (.ContactLastNameFilters = 17) Then
' Q
DoCmd.ApplyFilter "", "[ContactLastName] Like ""Q*"""
End If
If (.ContactLastNameFilters = 18) Then
' R
DoCmd.ApplyFilter "", "[ContactLastName] Like ""R*"""
End If
If (.ContactLastNameFilters = 19) Then
' S or (S hacek)
DoCmd.ApplyFilter "", "[ContactLastName] Like ""[S]*"""
End If
If (.ContactLastNameFilters = 20) Then
' T
DoCmd.ApplyFilter "", "[ContactLastName] Like ""T*"""
End If
If (.ContactLastNameFilters = 21) Then
' U, , , , or
DoCmd.ApplyFilter "", "[ContactLastName] Like ""[U]*"""
End If
If (.ContactLastNameFilters = 22) Then
' V
DoCmd.ApplyFilter "", "[ContactLastName] Like ""V*"""
End If
If (.ContactLastNameFilters = 23) Then
' W
DoCmd.ApplyFilter "", "[ContactLastName] Like ""W*"""
End If
If (.ContactLastNameFilters = 24) Then
' X
DoCmd.ApplyFilter "", "[ContactLastName] Like ""X*"""
End If
If (.ContactLastNameFilters = 25) Then
' Y, , or
DoCmd.ApplyFilter "", "[ContactLastName] Like ""[Y]*"""
End If
If (.ContactLastNameFilters = 26) Then
' Z, , , or
DoCmd.ApplyFilter "", "[ContactLastName] Like ""[Z]*"""
End If
If (.ContactLastNameFilters = 27) Then
' Show all records.
DoCmd.ShowAllRecords
End If
If (.RecordsetClone.RecordCount > 0) Then
' If records are returned for the selected letter, go to the ContactLastName control.
DoCmd.GoToControl "ContactLastName"
' Stop the macro.
Exit Function
End If
If (.RecordsetClone.RecordCount = 0) Then
' If no records are returned for the selected letter, display a message.
Beep
MsgBox "There are no records for that letter.", vbInformation, "No Records Returned"
' Show all records.
DoCmd.ShowAllRecords
' Press in the All button.
.ContactLastNameFilters = 27
End If
End With
Customer_Phone_List_Alpha_Buttons_Exit:
Exit Function
Customer_Phone_List_Alpha_Buttons_Err:
MsgBox Error$
Resume Customer_Phone_List_Alpha_Buttons_Exit
End Function
'------------------------------------------------------------
' Customer_Phone_List_Print
'
'------------------------------------------------------------
Function Customer_Phone_List_Print()
On Error GoTo Customer_Phone_List_Print_Err
' Run by the Sample Autokeys.^p macro when Ctrl+P is pressed.
' Print the currently shown records.
DoCmd.PrintOut acPrintAll, , , acHigh, 1, True
Customer_Phone_List_Print_Exit:
Exit Function
Customer_Phone_List_Print_Err:
MsgBox Error$
Resume Customer_Phone_List_Print_Exit
End Function
datAdrenaline
If the code is behind the form .. then change CodeContextObject to Me ....
.. Gotta run ... kid crying!! ...
RuralGuy
Try Brent's suggestion and let us know the results.
clocker
Can not change
unction Customer_Phone_List_Alpha_Buttons()
On Error GoTo Customer_Phone_List_Alpha_Buttons_Err
With CodeContextObject
' Attached to the Customer Phone List form.
To:
Function Customer_Phone_List_Alpha_Buttons()
On Error GoTo Customer_Phone_List_Alpha_Buttons_Err
With Me
' Attached to the Customer Phone List form.
' Attached to AfterUpdate event of ContactLastNameFilters option group.
What am I missing?
Jeff
datAdrenaline
Is the code in a standard module? ... or in the code behind a form? ... either is OK, it just makes a difference on how you reference what you are referencing....
clocker
The code is in a standard module.
When I change the code to With Me it says that it is an invalid use of Me keyword.
Jeff
datAdrenaline
Ok ... in a standard module is fine ...
ypically, if code is not going to be used by more than one object (ie: a form or report) I just keep the code in the form that will use the code ...
So ... in your case, what does the OnClick event property look like? does it look like a function call?
=Customer_Phone_List_Alpha_Buttons()
FOr ... does is look like this:
[Event Procedure]
Then the code behind the form looks like this:
CODE
Private Sub btnSomeButton_OnClick()
    ContactLastNameFilters = <some number>
    Customer_Phone_List_Alpha_Buttons
End Sub

....
By the way ... you made the comment earlier ...
>> I do not see anywhere where the RecordSet variables have been defined <<
Recordset and RecordsetClone are properties of a Form, so you would not have those declared, however, if you were to create a recordset object, then you should always explicitly declare what type of recordset object you want ....
Dim rstA As ADODB.Recordset
Dim rstD As DAO.Recordset
.....
Is there any chance at all that you can Compact and Repair your db, then ZIP it up and attach it to a post? ...
clocker
My database is based on the Northwind database. The customer phone list form and macro were imported for use in my database. This copy still uses the macro and has not yet been converted to VB code.
Ocreated my database program a few years back and everything was going great until I upgraded to Access 2007.
Tried to attach a zip file but was unsuccessful.
Jeff
jmcwk
Jeff,
More than likely you are aware of the procedure to Attach a DB however if not take a look Here for instructions
clocker
OK, here is another try.

I guess the file was too large so I uploaded it to my website
Database link
Edited by: clocker on Tue Jul 29 19:39:05 EDT 2008.
datAdrenaline
Well .. the code works fine for me (as it is listed) .... in A2007 and A2003 ...
My suspicion is that you are calling the code incorrectly. You see the CodeContextObject is intended to return the object that has called the code in question, so when I used the AfterUpdate event of the Option Group named "CompanyNamedFilters" with a procedure like this:
CODE
Private Sub CompanyNameFilters_AfterUpdate()
    Customer_Phone_List_Alpha_Buttons
End Sub

The CodeContextObject is set to the form that holds CompanyNameFilters ... and all works well.
Also ... if I set the AfterUpdate event PROPERTY to ...
=Customer_Phone_List_Alpha_Buttons()
All works fine too! ...
... So ... what I suggest you do, is place the database that causes the issue on your web site ... the one you posted, I went through the process of saving the macro as a module, then setting up the events accordingly ... so ... please give the process a shot again, and if you can't get it to work, then post the db that can reproduce the error.
It may also be worth nothing that the db you have pointed to, does NOT compile .... you reference a control on frmImage that is NOT there, so the compilation will not complete, which can be a problem... Especially when converting files, or using a mixed Access versions for the same db (so I hear at least!!) ...
clocker
Sorry for not getting back sooner. My mother has had to be hospitalized and I have not had much computer time.
will make the changes and get back to you with my findings ASAP.
One thing I want to clarify is that the program that I uploaded to my website worked fine for you in Access 2007 without making any changes, am I correct?
Thanks for the help,
Jeff
datAdrenaline
Hello Jeff,

I am sorry to here of the issues you are facing! ... I am a praying sort of folk, so I will toss a prayer or two your way if you don't mind! ... I have a son with a bum heart and legs, so I understand the crazyness that can occur with health issues!

Anyway ... I looked at your db and you are running in "Sand Box Mode", which does not allow for some expressions ... so ... I changed your "conditions" to:

[CurrentRecord]>0 And [NewRecord]=False For the GotoControl action

And

[CurrentRecord]=0 Or [NewRecord]=True For the MsgBox action

I think that will take care of it ....

If you want, you can turn off Sand Box mode by setting a registry key too ...

http://office.microsoft.com/en-us/access/HA101674291033.aspx

But you will need to turn off Sand Box mode on all PC's using this DB, plus you will have to "Trust" the db in some fashion.
Hope that helps ...
clocker
Brent,
Thanks for all the help and advice. The form is working without a glitch now.
The internet is the best thing that has come about in a long time and people like you and the other members of this forum make it the best.
Thanks again,
Jeff
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.