Search results

But the code refers to olePivotTable in Set xlApp = frm.olePivotTable.Object.Application so I have to change the OLE object name anyway (I guess). Again thank you for your time.

Wow. That's amazing. Thank you very much. I ran the code on other forms by changing the name of the OLE object and referring to different form in the code. Works like magic. My only concern (since I am not that sophisticated) is whether changing the name of the OLE object affects in any way...

My bad. This is where I get the error. MsgBox xlWkBk.PivotCaches(1).CommandText

Being stupid is really bad. :confused: I am attaching the screenshot of where I changed the name. Now I am getting "Subscript out of Range" error after I put that name in and ran the code again.

As per renaming, I hope, I did what you meant - right click the form in the Design View, select Properties, change the name of Unbound Object Frame back to PivotTable from olePivotTable. Is that it??? The code is in the standard module named Module1. Now I am getting run-time error '2465' -...

Thank you for your time. I renamed the form to olePivotTable by going to Sheet property / Name. Sub PivotSource () Dim xlWkBk As Object Forms![DVS - YTD - UCR Part I by PSA (3YRS,M)].olePivotTable.Action = acOLEActivate Set xlWkBk = Forms![DVS - YTD - UCR Part I by PSA...

Sure. It would not let me to do that. I ran it from a separate module I created.

Sorry for the pause... Hope you have had a great Thanksgiving. I ran the code (with the name of the form instead of ME) from the module with the form in the Form View. The VB gives a ""subscript out of range" error message. Just can't seem to get it to work...

It was open in Design Mode

Thank you very much. Could you please help me make it work? When I put this code (surrounded by Sub PivotData() and End Sub) into a separate module, I get the message "Invalid use of of Me keyword". When put into MS Access Class Object page of that particular form, it would not run at all...

I must be using wrong terminology. May be "linked" is not the correct term. The problem is that after I double click the control I can only modify the layout of the pivot table embedded in the form. I cannot change the range, etc. If I need to modify a field or add another field to the query...

My initial intent was to modify the pivot table by changing the range of cells the pivot table (object) was using. However, there were limited options for changing the pivot table available, and data range modification was not among them. I was hoping that I would be able to make slight...

The form with this object works just fine. Can this be true if the sourcedoc has been removed?

Oh, I see. Silly me. After I changed that actual name of the object, I received a blank message again. Not traceable, I guess. Thank you very much for your help.

This returns an error message - "... can't find the field olePivot referred to...."

Sorry for being silly. How do I get the NameOfOLEObject? The name that I found in the Property Sheet for the object was PivotTable. Putting that into the code Msgbox Forms![SDH - (m) Monthly Rpt]!PivotTable.SourceDoc gave me a blank message again. :confused:

Thank you very much for your response! In my case SDH - (m) Monthly Rpt is the name of the form that, apparently, has an UNBOUND object (linked to some table) and produces a pivot table for me. I substituted the Msgbox (Forms![SDH - (m) Monthly Rpt].Recordsource) which produced a blank...

Here it goes. The code msgbox(Forms![Form1].RecordSource) returns blank message for the Form1, which is a pivot table based upon Orders table. Thanks.

Yes, I did. I have Access 2000 on the machine with the database. The only way I could extract the data source was through the code (could not find the necessary property sheet). However, most of the forms contain, I guess, as you put it, embedded objects or are based upon several queries/tables...

Thank you.