option group

[deb]

I have a form with 2 buttons. One displays a Pivot table and the other displays a pivot chart.

I would like to have an option group that gives the option of displaying the "totalsUSD" field or the "totalsEuro" field or both in the pivot table or the pivot chart.

The query has both fields in the query that supports both pivots

So, you can choose the currency type and then click the pivot Table or pivot chart button to display the data with the chosen currency.

can anyone help?

Thanks,

 

[Andrew Fergus]

Hi Deb

This can be done with VBA and an option group on your form. Create an option group with 3 choices : first = USD, 2nd = Euro and 3rd = Both. Call the option group 'Currency' (under properties), make sure that USD = option 1, EUR = 2 and both = 3. Choose a default for the option Group (3 perhaps?)

For the purposes of this example, I have assumed the USD field in the report is called 'totalsUSD' and the Euro field is called 'totalsEuro' and the report is called MyReport. Be sure to use the actual field and report names. Change the code behind the print preview button by adding the following before your existing commands :

        DoCmd.OpenReport "MyReport", acViewDesign, , , acHidden
        Select Case Me.Currency
            Case 1
                Reports![MyReport].totalsUSD.Visible = True
                Reports![MyReport].totalsEuro.Visible = False
            Case 2
                Reports![MyReport].totalsUSD.Visible = False
                Reports![MyReport].totalsEuro.Visible = True
            Case 3
                Reports![MyReport].totalsUSD.Visible = True
                Reports![MyReport].totalsEuro.Visible = True
        End Select
        DoCmd.Close acReport, "MyReport", acSaveYes

This code should be inserted somewhere between the lines "Private Sub..." and "DoCmd.OpenReport..." in your existing code.

Repeat for your other report / print button.

HTH, Andrew