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 :)

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.

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

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.

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

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.

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

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.

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

A Crosstab may have been better. A Pivot is interactive. Unless you needed that interactivity a Crosstab is more suitable.

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

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

Bingoo!! Thanks a lot Scott.. :)

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

Yes you can use =Forms!formname!controlname anywhere. No you can't reference a value from a query that way. Use a DLookup instead.

The DLookup, could you provide the formula please?

Thanks

DLookup("[fieldname]","table or query name", criteria)

You can find examples in Access Help.

Will try.

Thanks