Retrieve Filter value

[Whasigga]

Hi guys

I've got a report that I have created. It is based off of a query that I made. I use it to show different combinations of data, but its the same type of information...

It is called from 5 buttons on a form. each of the buttons opens the report to show something different, most are just the time frame of the data, whether it be 1 day, 1 week, 1 month, 1 year... Each one uses the doCmd.OpenReport method, and then passes a Where statement, so that the report opens with it's query based on the conditions passed. This all works fine for me.

What I would like to be able to do is an aesthetic thing. Since the report shows different time frames depending on the button, I would like for the title of the report that shows up to relate to the specific time frame.

From the research I've done, my Access 2000 does not allow me to pass openargs with openReport, so I cant go that route. I saw getOptions, but it doesn't look like I can pull the filter from that.

The only way that I can see to do what I want is to pull the Where clause that is passed (since that is the only thing differentiating one instance of the report from another), and parse that up to find it's contents, which will tell me the time frame. But I can't figure out how to pull the filter.

Does anybody know how to do that, or if it is possible?

thanks

Josh

[ADezii]

Hi guys

I've got a report that I have created. It is based off of a query that I made. I use it to show different combinations of data, but its the same type of information...

It is called from 5 buttons on a form. each of the buttons opens the report to show something different, most are just the time frame of the data, whether it be 1 day, 1 week, 1 month, 1 year... Each one uses the doCmd.OpenReport method, and then passes a Where statement, so that the report opens with it's query based on the conditions passed. This all works fine for me.

What I would like to be able to do is an aesthetic thing. Since the report shows different time frames depending on the button, I would like for the title of the report that shows up to relate to the specific time frame.

From the research I've done, my Access 2000 does not allow me to pass openargs with openReport, so I cant go that route. I saw getOptions, but it doesn't look like I can pull the filter from that.

The only way that I can see to do what I want is to pull the Where clause that is passed (since that is the only thing differentiating one instance of the report from another), and parse that up to find it's contents, which will tell me the time frame. But I can't figure out how to pull the filter.

Does anybody know how to do that, or if it is possible?

thanks

Josh

It is definitely possible, and actually very simple:

1. Create a 'Global' Variable to hold the String Value for the Time Frame.
   

   Public gstrTimeFrame As String

2. For each Command Button, assign an appropriate value to this Global Variable, for instance the Monthly Time Frame.
   

   gstrTimeFrame = "Monthly"
   DoCmd.OpenReport "rptWhatever", acViewPreview
    

3. In your Report's Open() Event, assign the Caption of the Report based on the value of gstrTimeFrame.
   

   Private Sub Report_Open(Cancel As Integer)
     DoCmd.Maximize
     Me.Caption = "Time Frame (" & gstrTimeFrame & ")"
   End Sub
    

   Sample Caption:
   

   Microsoft Access - [Time Frame - (Monthly)]

4. Any questions, feel free to ask.

[Whasigga]

It is definitely possible, and actually very simple:

1. Create a 'Global' Variable to hold the String Value for the Time Frame.
   

   Public gstrTimeFrame As String

2. For each Command Button, assign an appropriate value to this Global Variable, for instance the Monthly Time Frame.
   

   gstrTimeFrame = "Monthly"
   DoCmd.OpenReport "rptWhatever", acViewPreview
    

3. In your Report's Open() Event, assign the Caption of the Report based on the value of gstrTimeFrame.
   

   Private Sub Report_Open(Cancel As Integer)
     DoCmd.Maximize
     Me.Caption = "Time Frame (" & gstrTimeFrame & ")"
   End Sub
    

   Sample Caption:
   

   Microsoft Access - [Time Frame - (Monthly)]

4. Any questions, feel free to ask.

That makes sense. It works, for changing the caption of the form. But is it possible to change a control on the report? I have tried changing labels and text boxes in the report_open function but it always gives an error that I can't assign a value to the object...maybe I can't do that with reports?

[ADezii]

That makes sense. It works, for changing the caption of the form. But is it possible to change a control on the report? I have tried changing labels and text boxes in the report_open function but it always gives an error that I can't assign a value to the object...maybe I can't do that with reports?

You are probably assigning it to the wrong Property of the Control, or to the Control itself. For instance, you can assign the Caption Property of both Labels in a Report to the Global Variable 'global var', but if you try to assign the global var to the [FirstName] Text Box you will generate the Error you describe, as in:

Private Sub Report_Open(Cancel As Integer)
  'will work fine
  Me![PrimaryKey_Label].Caption = gGlobalvar
  Me![tableheader].Caption = gGlobalvar      'Label Control
 
  'will generate the can't assign a value to the Object Error
  Me![FirstName] = gGlobalvar
End Sub

[Whasigga]

fantastic, works like a charm! thanks a lot for your help

[ADezii]

fantastic, works like a charm! thanks a lot for your help

Always glad to be of assistance here at TheScripts.