Determine the query that feeds the form

[vbaInet]

What you have there is an UNBOUND ole object. It's an object linked to an Excel spreadsheet that produces a pivot table based on the Orders table.

You can try:

Msgbox Me.[COLOR=Red]OleObjectName[/COLOR].SourceDoc

where OleObjectName should be substituted for the name of the object.

By the way, in your sample db you called the object PivotTable. That's not a good name because the word "PivotTable" already exists in Access and is reserved for Access and VBA so it will cause a "clash" when used. Call it something else before using the code.

 

[Victor70]

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 message for me

for
Msgbox (Forms![SDH - (m) Monthly Rpt].SourceDoc)

which gets me an error message "Run-time error #2465: Application-defined or object-defined error".

Am I doing something wrong?

 

[vbaInet]

The form's RecordSource has nothing to with it in this case. You should be calling the name of the embedded object.

Msgbox Forms!FormName!NameOfOLEObject.SourceDoc

Plus note what I mentioned in my last post about the name of the OLE object.

 

[Victor70]

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.

 

[vbaInet]

Yes, change that name from PivotTable to olePivot and use:

Msgbox Forms![SDH - (m) Monthly Rpt]!olePivot.SourceDoc

If that returns nothing then it's not traceable.

 

[Victor70]

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

 

[vbaInet]

You haven't renamed the object.

 

[Victor70]

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.

 

[vbaInet]

It must have been moved or removed at some point.

 

[Victor70]

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

 

[vbaInet]

Actually, my bad. It's Unbound so it has no source file. Everything was done directly on the control. It's an Unbound OLE that is a spreadsheet object.

 

[Victor70]

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 changes without breaking the links among forms, queries and tables (that I might not be aware of) to keep using the initial form.

If that "was done directly on the control", does it mean that there is no way to get to the source of the object?

 

[vbaInet]

The object is simply feeding off from your Orders table in your database and displaying it in a Pivot table in the spreadsheet object. It's not "linked" to anything per se.

If you double click the control in design view, that's how you edit it.

 

[Victor70]

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 that feeds my pivot table, I have to go directly to that particular query to do that. The databases that I, sort of, inherited have lots and lots of tables and queries. Often I cannot figure out which table or query to go to to make the necessary changes.

Even for pivot tables embedded into forms by myself I cannot get the name of the query that feeds them by using the code provided. I just know which query the form feeds off, and that's how I modify the pivot table.

Is there a way to find out which query (when query name is not the same as the form name) is used for the pivot table?

 

[vbaInet]

If you renamed the object as I previously mentioned then something like this should work:

    Dim xlWkBk As Object
    
    Me.olePivotTable.Action = acOLEActivate
    
    Set xlWkBk = Me.olePivotTable.Object.Application.Workbooks(1)

    MsgBox xlWkBk.PivotCaches(1).CommandText
    Debug.Print xlWkBk.PivotCaches(1).CommandText

A msgbox will pop-up and it will print to the Immediate Window.

 

[Victor70]

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.

When I replace ME with the name of the form (I copy the name right from the Forms Sheet)

 Forms![DVS - YTD - UCR Part I by PSA (3YRS,M)].olePivotTable.Action = acOLEActivate

Access says that it can't find the form referred to in the code.

 

[vbaInet]

So the form needs to be open.

 

[Victor70]

It was open in Design Mode

 

[vbaInet]

Not in Design Mode, Form View.

 

[Victor70]

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