Combobox in Report Header for filter? (1 Viewer)

[Holly_Associated]

Hello Experts,

I have a report, rptAllCSCS which is based on a query qryCSCS2...

One of the fields in qryCSCS2 is Status and each record is either "Current" or "Not Current"...

My report is being viewed via a navigation form, so one of the tabs says CSCS and when clicked the user can see the report...

I have added a button in the report header which when clicked opens the report in print preview so the user can print the report. (Done with a macro in the On Click of the button.)

I would like to add a combo box cmbStatus (or another suggestion/method if its more sensible!) which has the values "Current" and "All" in the report header. (Current will show only when the Status field = Current and All shows all records so Current and Not Current together). This will act as a filter for the user to see the corresponding records and they can then press the print button or just view on screen.

I haven't worked with filters before except when you specify the criteria in the query and point it to a control on a form which then opens the report... As this report is already open I'm having trouble, as well as specifying the "path" when something is in a navigation form being a bit tricky...

Is there a different way I should do this? Any help much appreciated!

I've attached a screenshot if that helps to understand how the user views this.

 

[Holly_Associated]

Hi Experts,

OK, after some thought, I maybe could use two buttons in the report header...

The first button uses the ApplyFilter macro set to WHERE [Status]="Current"... all good so far...

Then to get the records back, the second button uses the macro RunMenuCommand - RemoveFilterSort...

This all works brilliantly when I view the report as standalone, but when I am viewing it through my navigation form (which is how my users will see it) it isn't working! It says the report isn't bound to a query. I have tried referencing the report in the Control Name box of the ApplyFilter macro... [Forms]![frmMain]![NavigationSubform]![rptAllCSCS] ... but this tells me the control name is not valid. Does anyone know how to resolve this?

Again, let me know if I'm not making sense I can upload pictures of what I'm doing if needed!

 

[Mihail]

At my understanding level, a report is a piece of paper that can be viewed (or printed).
So, I don't expect a combo box in my paper in order to choose/change what is write in (this paper).
If I wish that my employe (the PC in this case) to write a report (on the paper) I tell he/she what data I need before that he/she (the PC) start to write.

On the other words (and only in my opinion) you should define the filter before start to show/print the report.

 

[Holly_Associated]

Hi Mihail,
I understand what you are saying and thank you for the reply.
The combo box idea was a little "off" of me.
The 2 buttons idea half works, I just need to know how to make it work from the navigation form. I could have the report opening from the navigation form then the preview opening from that, but it just seems a little messy for Access. I know it will be able to do what I want (filter while viewing in the navigation form rather than having to open again) I just don't know how to tell it!

 

[Mihail]

Move that 2 buttons from report to your form. Maybe you like to create one more.
Then open the report from the Click event of this buttons:

To show:

all:
DoCmd.OpenReport "rptAllCSCS", acViewPreview
only current:
DoCmd.OpenReport "rptAllCSCS", acViewPreview, , "Status = ""Current"""
only not current:
DoCmd.OpenReport "rptAllCSCS", acViewPreview, , "Status = ""Not Current"""

Now you can print by using CTRL+P keys, but you can print directly from VBA:

To print:

all:
DoCmd.OpenReport "rptAllCSCS"
only current:
DoCmd.OpenReport "rptAllCSCS", , , "Status = ""Current"""
only not current:
DoCmd.OpenReport "rptAllCSCS", , , "Status = ""Not Current"""

 

[Holly_Associated]

Hi Mihail,

Yes I think I'll have to do it as opening separately for now until I work out how to reference the report inside a navigation form.

Edit: I am going to pose the question in the forms forum as I believe this issue is to do with the Navigation form.

 

[Mihail]

I am going to pose the question in the forms forum as I believe this issue is to do with the Navigation form.

But, in my last post, I advice you to do this and I show you "how to".