View Full Version : Pivot table directly in FORM for Ms. Access 2003
nurmala
Nov 14, 2011, 01:33 AM
Hey pals, need your hand on this one.
I have a FORM Code:
Private Sub RUN_Click()
On Error Go to Err_CHSWB_Final1_Click
Dim stDocName As String
Dim stLinkCriteria As String
DoCmd.OpenQuery "CHSWB_Final1", acViewNormal, acReadOnly
Exit_CHSWB_Final1_Click:
Exit Sub
Err_CHSWB_Final1_Click:
MsgBox Err.Description
Resume Exit_CHSWB_Final1_Click
End Sub
Can I pivot the resulted table directly in that FORM Code (by adding more lines), or should I pivot the table manually? And if I can do that in that FORM Code, could you guys give me the pivot syntax?
Do I get myself clear on this? Need your response...
Thanks indeed :)
ScottGem
Nov 14, 2011, 04:48 AM
I'm not sure what you want here. In Access a Pivot table is a view of the form. Once you define the pivot (which has to be done manually), you can switch to the view in code, by setting the DefaultView property.
If you want to display the results of a query as a pivot form, then you would not use DoCmd.OpenQuery, but OpenForm and set the view to Pivot (again predefine the pivot). Just bind the form to the query.
nurmala
Nov 14, 2011, 06:26 PM
Yes, indeed I want to display the result of a query as a pivot form. Where can I find the DefaultView property? And how I bind the form to the query? Could you give any syntax for it?
Thanks
ScottGem
Nov 14, 2011, 07:57 PM
You do it manually. Use the Pivot Form wizard to create the form. Then just open the form. It will default to Pivot Table view.
nurmala
Nov 14, 2011, 08:31 PM
I already created Pivot Form and when I open it, it default to Pivot Table view. But when I bind it with query and using OpenForm, it is not like the one that showed in Pivot Form, but it showed only one row...
What happened?
I still can not find why the pivot turned into just one row (after binding it to query), instead of a view as it looks in pivot view.
Another question about pivot table : can we re calculate from pivot table, or pivot is just a view? Recalculate I mean, I want to use "SUMPRODUCT" function...
ScottGem
Nov 15, 2011, 04:01 AM
When you originally created the Pivot Form, what did you use as the Recordsource? Are you checking the query to see if that only returns one row?
A pivot form does some internal calculations. It will display the current real time values in its Recordsource. But you can't do calculations in the controls of a Pivot Form Any calculations need to be done in the source.
SumProduct is not an Access function, I believe its an Excel function. Some Excel functions can be used in Access though.
Can you attach a screenshot of what the Pivot form is supposed to show. The advantage of a Pivot, is that the user can manipulate the way the data is displayed by moving the grouping values in both Columns and Rows without having to create a new form.
nurmala
Nov 16, 2011, 12:46 AM
I created AutoForm : Pivot Table and used query as RecordSource. And saved the From. When I run the Form, the pivot turned normal. But, when I bind the Pivot Form to other Form, and changed DoCmd.OpenQuery to OpenForm, and viewPivot, it turned in to one raw...
I attached the pivot table as it turned into one raw...
ScottGem
Nov 16, 2011, 04:10 AM
That does not look like an applicable use of a pivot Form. Not all data lends itself to use as a Pivot form. Why do you want to use a Pivot Form here?
Also you can't bind a form to another form.
Did you run the query to see if the query only returned one row?
You might want to zip up a copy of your database and attach it here so I can take a look. Because its not making a lot of sense.
nurmala
Nov 16, 2011, 11:49 PM
Hey ScottGem, I got a better Idea. I created Crosstab Query instead of using pivot, and I got what I wanted.
Still can not figure our problem yet.
But thanks anyway Pal.. :)
ScottGem
Nov 17, 2011, 04:13 AM
A Crosstab may have been better. A Pivot is interactive. Unless you needed that interactivity a Crosstab is more suitable.
nurmala
Nov 18, 2011, 08:00 PM
Hey guys, I want to simplify my form. I have a form just like this : (attached)
What I want is, when I click the "RUN" button, it shows only parameter from those two combo boxes.
What I have now for code in "RUN" button is :
Private Sub RUN_Click()
Dim stDocName As String
Dim stLinkCriteria As String
DoCmd.OpenQuery "Que_Drop_Down", acViewNormal, acReadOnly
Exit_Que_Drop_Down_Click:
Exit Sub
Err_Que_Drop_Down_Click:
MsgBox Err.Description
Resume Exit_Que_Drop_Down_Click
End Sub
Could any one give me a hand on this?
Thank you very much
ScottGem
Nov 19, 2011, 09:08 AM
This is very simple. You can reference a value in any control on an open form using the syntax:
Forms!formname!controlname
where formname is the name of YOUR form and controlname the name of the control.
So you can enter the criteria row of the query in the appropriate column:
=Forms!formname!controlname
nurmala
Nov 19, 2011, 06:58 PM
Bingoo!! Thanks a lot Scott.. :)
nurmala
Nov 23, 2011, 06:36 PM
Hi Scott,
Another question. Can we use "=Forms!formname!controlname" in textbox for Report in ms. Access? And can we use the same formula for query, so it is like "=Queries!Queryname!field" and use it in textbox for Report?
Thanks a lot
ScottGem
Nov 23, 2011, 07:09 PM
Yes you can use =Forms!formname!controlname anywhere. No you can't reference a value from a query that way. Use a DLookup instead.
nurmala
Nov 23, 2011, 07:15 PM
The DLookup, could you provide the formula please?
Thanks
ScottGem
Nov 23, 2011, 08:21 PM
DLookup("[fieldname]","table or query name", criteria)
You can find examples in Access Help.
nurmala
Nov 23, 2011, 08:38 PM
Will try.
Thanks