Default View of a Query

[MDAO]

Hi all,

I know similar questions have been asked, but I guess I am having a somewhat different one.
I am trying to open a query in the pivot table view by default, so I went to the property table of the query in the design view and changed the default view to pivot table. When I open it on the column on the left, it works perfectly fine.

So now what I am trying to do is that I created a form, and put a button on the form. I would like to open the query from this form when I click on that button. However, when I open the query this way, it opens in its ordinary view.

Does anyone of a solution to the issue above, please?

Thanks a lot!



Mike

[June7]

Open form in Pivot View instead of query.

[MDAO]

Open form in Pivot View instead of query.

Thank you! But how do you do that?

[June7]

Bind form to data. Set the form DefaultView property or use the PivotView option from ribbon.

[MDAO]

Thank you. I have tried it. But the same problem arose. When I opened it in the list on the left, it works. But when I open it through a form, it goes back to the form view.
PS: I ve tried the defaultview property method. I don't know what it means by "from ribbon".
Sorry, I know so little.

[June7]

Access 2007 introduced the control 'ribbon', no longer a menu bar. The Home tab on the ribbon has a Views section. Can manually choose the view you want the form to display in.

What is the code when you 'open it through a form'? Macro or VBA? It can override the DefaultView setting.

[MDAO]

Access 2007 introduced the control 'ribbon', no longer a menu bar. The Home tab on the ribbon has a Views section. Can manually choose the view you want the form to display in.

What is the code when you 'open it through a form'? Macro or VBA? It can override the DefaultView setting.

Oh, that's called "ribbon". Yea, I knew I could change the view using that button in the ribbon, but I would like to open it by clicking a button in a form, so that users who know nothing about Access can see the pivot table once they open it.

I created a form, and put a button on it. When I right-click the button, and in the "build event" window is the code:

Private Sub PCFNAT_OPENQUERY_Click()
On Error GoTo Err_PCFNAT_OPENQUERY_Click


Dim stDocName As String


stDocName = ChrW(80) & ChrW(67) & ChrW(70) & ChrW(95) & ChrW(22283) & ChrW(-31287) & ChrW(20998) & ChrW(20296)
DoCmd.OpenQuery stDocName, acNormal, acEdit


Exit_PCFNAT_OPENQUERY_Click:
Exit Sub


Err_PCFNAT_OPENQUERY_Click:
MsgBox Err.Description
Resume Exit_PCFNAT_OPENQUERY_Click

End Sub



I guess you are right about the default view setting being overridden. Is there a way to fix it?

Thanks a lot.
I greatly appreciate it.

[June7]

Instead of acNormal, try acPivotTable.

You are still opening a query and not form or report. Usually best not to have users interact with tables and queries.

Why are you using ChrW() to construct name? Just type the name within quote marks.

[MDAO]

Instead of acNormal, try acPivotTable.

You are still opening a query and not form or report. Usually best not to have users interact with tables and queries.

Why are you using ChrW() to construct name? Just type the name within quote marks.

I have tried replacing "acNormal" with "acPivotTable," but nothing seems to have changed. Did I do anything wrong?
I didn't type any of the code there because I don't know how to. I am constructing the whole thing simply using the built-in function. For example, when you put a button there, a window shows up and asks you what you want to do with that button.
I understand a form is preferred to a query so that users won't mess up with the data. I just didn't know you could make a pivot-table from a form until you told me. Now, I have many databases to work on. So I am thinking maybe I learn the way of opening a query in a pivot-table by default first. After that, I will change the queries to forms one by one.

CODE:
Private Sub PCFNAT_OPENQUERY_Click()
On Error GoTo Err_PCFNAT_OPENQUERY_Click


Dim stDocName As String


stDocName = ChrW(80) & ChrW(67) & ChrW(70) & ChrW(95) & ChrW(22283) & ChrW(-31287) & ChrW(20998) & ChrW(20296)
DoCmd.OpenQuery stDocName, acPivotTable, acEdit


Exit_PCFNAT_OPENQUERY_Click:
Exit Sub


Err_PCFNAT_OPENQUERY_Click:
MsgBox Err.Description
Resume Exit_PCFNAT_OPENQUERY_Click

End Sub

Thanks!

[June7]

Sorry, it is acFormPivotTable. Just pick from the intellisense list when typing the command.

[MDAO]

Sorry, it is acFormPivotTable. Just pick from the intellisense list when typing the command.

It WORKS!!! HAHA thanks a lot.
Now the query opens in pivot chart by default. Is there a way I can make it open in pivot table?

[June7]

Are you opening a query or form?

Did you see the list of options that intellisense presents when typing the command? They will be slightly different for query and form.

[MDAO]

Are you opening a query or form?

Did you see the list of options that intellisense presents when typing the command? They will be slightly different for query and form.

I am opening a query. No, I don't see the intellisense list.

[June7]

As you type the DoCmd code, the VBA intellisense popups should show. Do you ever see intellisense popups?

For form:
acFormPivotChart
acFormPivotTable

For query:
acViewPivotChart
acViewPivotTable

[MDAO]

As you type the DoCmd code, the VBA intellisense popups should show. Do you ever see intellisense popups?

For form:
acFormPivotChart
acFormPivotTable

For query:
acViewPivotChart
acViewPivotTable

I don't know why. Maybe, I didn't do everything correctly, so the list didn't show.
BUT THANKS!
Now it is working as how I hoped it to be!~