Change view to Pivot Table, Please Help

[tmdrake]

I am using the following code to open a form from a subform on the double
click (this works), however the Access becomes unresponsive when I use this
code.

Private Sub ProjectID_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = Me.ProjectID
stDocName = "pivtblHours_Worked"
DoCmd.OpenForm stDocName, acFormPivotTable, _
"ProjectID = """ & stLinkCriteria & """"

DoCmd.OutputTo acOutputForm, "pivtblHours_Worked", "(*.xls)", _
"G:\Project Staffing Spreadsheets\pivtblHours_Worked_template.xls", True, ""

Debug.Print stLinkCriteria

End Sub

However, when I have the form open in Datasheet view, it works just fine.

Please help, I really need to open in the Form Pivot Table view.

Thanks

 

[Jeanette Cunningham]

Hi,
I answered your question about exporting a pivot table to excel, just a
minute ago, in the other newsgroup.
I have never tried to export a form to excel, but I think that is the error
with this piece of code.

If you comment out these 2 lines

DoCmd.OutputTo acOutputForm, "pivtblHours_Worked", "(*.xls)", _
"G:\Project Staffing Spreadsheets\pivtblHours_Worked_template.xls", True,
""

and then try to open your form, you may have better luck.

If you change these lines to use acOutputTable instead of acOutputForm, I
suspect it will work.

DoCmd.OutputTo acOutputTable, "pivtblHours_Worked", "(*.xls)", _
"G:\Project Staffing Spreadsheets\pivtblHours_Worked_template.xls", True,
""

Jeanette Cunningham

 

[tmdrake]

Thank you for your reply to my question in the Coding form. However, the
bases of this question has nothing to do with exporting to Excel. I am
asking the question when I double click on a specific record in a subform,
another form is displayed, this form should open as a Pivot Table form view,
however, it doesn't; It opens in a Datasheet View. If I hit the design
button in the toolbar, then hit the form button in the toolbar, the form this
becomes a Pivot Table form.

My question is when this Pivot Table form is displayed, how do I keep from
having to hit the design button, form button in the tool bar for the form to
automatically opens in the correct form (Pivot Table).

 

[Jeanette Cunningham]

From the VBA help:

AllowPivotTableView Property

Returns or sets a Boolean indicating whether the specified form may be
viewed in PivotTable View. True if PivotTable View is allowed. Read/write.

Use the AllowDatasheetView , AllowFormView , AllowPivotChartView , or
AllowPivotTableView properties to control which views are allowed for a
form.

Example
The following example makes PivotTable View valid for the specified form and
then opens the form in PivotTable View.

Forms(0).AllowPivotTableView = True
DoCmd.OpenForm FormName:=Forms(0).Name, View:=acFormPivotTable

Jeanette Cunningham