468,133 Members | 1,206 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,133 developers. It's quick & easy.

Retrieve Filter value

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
Nov 9 '07 #1
5 1759
ADezii
8,799 Expert 8TB
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.
    Expand|Select|Wrap|Line Numbers
    1. Public gstrTimeFrame As String
  2. For each Command Button, assign an appropriate value to this Global Variable, for instance the Monthly Time Frame.
    Expand|Select|Wrap|Line Numbers
    1. gstrTimeFrame = "Monthly"
    2. DoCmd.OpenReport "rptWhatever", acViewPreview
    3.  
  3. In your Report's Open() Event, assign the Caption of the Report based on the value of gstrTimeFrame.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Report_Open(Cancel As Integer)
    2.   DoCmd.Maximize
    3.   Me.Caption = "Time Frame (" & gstrTimeFrame & ")"
    4. End Sub
    5.  
    Sample Caption:
    Expand|Select|Wrap|Line Numbers
    1. Microsoft Access - [Time Frame - (Monthly)]
  4. Any questions, feel free to ask.
Nov 10 '07 #2
It is definitely possible, and actually very simple:
  1. Create a 'Global' Variable to hold the String Value for the Time Frame.
    Expand|Select|Wrap|Line Numbers
    1. Public gstrTimeFrame As String
  2. For each Command Button, assign an appropriate value to this Global Variable, for instance the Monthly Time Frame.
    Expand|Select|Wrap|Line Numbers
    1. gstrTimeFrame = "Monthly"
    2. DoCmd.OpenReport "rptWhatever", acViewPreview
    3.  
  3. In your Report's Open() Event, assign the Caption of the Report based on the value of gstrTimeFrame.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Report_Open(Cancel As Integer)
    2.   DoCmd.Maximize
    3.   Me.Caption = "Time Frame (" & gstrTimeFrame & ")"
    4. End Sub
    5.  
    Sample Caption:
    Expand|Select|Wrap|Line Numbers
    1. 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?
Nov 13 '07 #3
ADezii
8,799 Expert 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2.   'will work fine
  3.   Me![PrimaryKey_Label].Caption = gGlobalvar
  4.   Me![tableheader].Caption = gGlobalvar      'Label Control
  5.  
  6.   'will generate the can't assign a value to the Object Error
  7.   Me![FirstName] = gGlobalvar
  8. End Sub
Nov 13 '07 #4
fantastic, works like a charm! thanks a lot for your help
Nov 14 '07 #5
ADezii
8,799 Expert 8TB
fantastic, works like a charm! thanks a lot for your help
Always glad to be of assistance here at TheScripts.
Nov 14 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by Robin Cull | last post: by
34 posts views Thread by kevin.watters | last post: by
4 posts views Thread by Rémy Samulski | last post: by
7 posts views Thread by Peter | last post: by
27 posts views Thread by didacticone | last post: by
1 post views Thread by gcdp | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.