It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.
Filter report from combo-boxes on form
Hey,
I wanted to filter a report rtp_ReportProjects from the form frm_FilterReport. On this form there are 5 combo boxes and I wanted that once the user chooses a value in one or a few / all combo boxes the report generates only those records which match the selected values in fields City, CourtType, Intervention, ProjectStatus, Work (the 5 combo boxes).
For example, if I choose a City: Belgrade I would want only that record on the report which matches Belgrade.
I inserted a button OPEN REPORT; so on the event “on click” I wanted to run the Filter of Report.
Any ideas for the CODE?
Thanks!
27043271Filter-report-17.05.11.accdb
I wanted to filter a report rtp_ReportProjects from the form frm_FilterReport. On this form there are 5 combo boxes and I wanted that once the user chooses a value in one or a few / all combo boxes the report generates only those records which match the selected values in fields City, CourtType, Intervention, ProjectStatus, Work (the 5 combo boxes).
For example, if I choose a City: Belgrade I would want only that record on the report which matches Belgrade.
I inserted a button OPEN REPORT; so on the event “on click” I wanted to run the Filter of Report.
Any ideas for the CODE?
Thanks!
27043271Filter-report-17.05.11.accdb
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I would use the combos as selection criteria in the query that the report is based on.
You can then write each selection criteria cell as ..
Forms!frm_FilterReport!com
You can then write each selection criteria cell as ..
Forms!frm_FilterReport!com
DraganaRadic,
This is by far the most common question here, it as been addressed countless times.
;-)
Do a search here of:
Microsoft Access Filter Report Combobox Form
...for many examples of how this can be done.
;-)
JeffCoachman
This is by far the most common question here, it as been addressed countless times.
;-)
Do a search here of:
Microsoft Access Filter Report Combobox Form
...for many examples of how this can be done.
;-)
JeffCoachman
Hey,
Peter > I ll try to look into your suggesstion. My report s not based on a query, jet these 5 combo boxes are taken from 4 different tables.
Jeff > I suppose the reason why there are some many similar question is that most users of this website find their problem/database to be special and have dificulties in shaping offered solutins to function on their databse. I have the same problem. I am a bit confused with all the one-to many relationships I have and these fields I always need for filtering therefore the solutions I found during my search in questions and answers didn t really work for me.
Peter > I ll try to look into your suggesstion. My report s not based on a query, jet these 5 combo boxes are taken from 4 different tables.
Jeff > I suppose the reason why there are some many similar question is that most users of this website find their problem/database to be special and have dificulties in shaping offered solutins to function on their databse. I have the same problem. I am a bit confused with all the one-to many relationships I have and these fields I always need for filtering therefore the solutions I found during my search in questions and answers didn t really work for me.
A report cannot be based on a form. Both forms and reports have to be based on a table or a query. If it's currently a table then you can just build a query from that table and then change the report's recordsource.
It doesn't matter what the sources of the combo boxes are.
It doesn't matter what the sources of the combo boxes are.
The thing is I have tables which are connected (and they are all a part of my report) with relatioinships and so I don t know on which one to make the query so that I can filter my report?
tbl_INTER_CourtsProjects
tbl_INTER_CourtInterventio
tbl_INTER_InterventionsWor
tbl_SUB_Cities
tbl_SUB_TypesOfWorks
The report is currently based on a table.
tbl_INTER_CourtsProjects
tbl_INTER_CourtInterventio
tbl_INTER_InterventionsWor
tbl_SUB_Cities
tbl_SUB_TypesOfWorks
The report is currently based on a table.
Open the report in design view.
Click into the box in the top left cornere,it should have a black blob in it. Then diplay the report properties by clicking the Properties option on the ribbon.
In the Data properties, look at the Recordsource property. This is the table used by your report. Click the Build button (...) to the right and thgis will convert the source into a query.
You now need to drag all the fields into the query grid, and you set the criteria for each relevant column as I indicated in my first post.. Us eht correct combo box for each relevant column.
Forms!frm_FilterReport!com
Close the query and say Yes you want to update the report when you are asked.
Click into the box in the top left cornere,it should have a black blob in it. Then diplay the report properties by clicking the Properties option on the ribbon.
In the Data properties, look at the Recordsource property. This is the table used by your report. Click the Build button (...) to the right and thgis will convert the source into a query.
You now need to drag all the fields into the query grid, and you set the criteria for each relevant column as I indicated in my first post.. Us eht correct combo box for each relevant column.
Forms!frm_FilterReport!com
Close the query and say Yes you want to update the report when you are asked.
Peter> I made the query, i figured how to enter all the tables I needed. (tnx for the patience, i m soo new to this, everything s confusing to me)
So in the relevant fields of the query, for example City and CourtType in criteria I entered
[forms].[frm_FilterReport]
and
[forms].[frm_FilterReport]
but smtg strange happens: when I only insert the criteria for the City field the report gets filtered. But when also add criteria for the CourtType, neither work. And vice versa, when I only enter criteria for CourtType it work and when I add for City neither work.
Do you maybe have an idea why this happenes?
and can you tell me what is this or Isnull(Forms!frm_FilterRep
Is it suppose to print all records if no value in the combo box is selected? Because if it is, it s not working. If I select nothing, the report is empty.
Tnx a lot!
So in the relevant fields of the query, for example City and CourtType in criteria I entered
[forms].[frm_FilterReport]
and
[forms].[frm_FilterReport]
but smtg strange happens: when I only insert the criteria for the City field the report gets filtered. But when also add criteria for the CourtType, neither work. And vice versa, when I only enter criteria for CourtType it work and when I add for City neither work.
Do you maybe have an idea why this happenes?
and can you tell me what is this or Isnull(Forms!frm_FilterRep
Is it suppose to print all records if no value in the combo box is selected? Because if it is, it s not working. If I select nothing, the report is empty.
Tnx a lot!
Peter >
I am attaching you my Database again so you can see that I (hopefully) inserted criteria right, but it doesn t work.
Look at the qryReport and frm_FilterReport
you will see that I entered the criteria in 4 fields of the query.
When you try to filter from combo boxes on the frm_FilterReport you will see that only empty report appears.
Try to filter City with Belgrade, CourtType with BasicCourt, Intervention with Adaptation (I know these entrys exist in thedatabse) so if it all works they should be on the report.
27043271Filter-report-18.05.11-Q.accdb
I am attaching you my Database again so you can see that I (hopefully) inserted criteria right, but it doesn t work.
Look at the qryReport and frm_FilterReport
you will see that I entered the criteria in 4 fields of the query.
When you try to filter from combo boxes on the frm_FilterReport you will see that only empty report appears.
Try to filter City with Belgrade, CourtType with BasicCourt, Intervention with Adaptation (I know these entrys exist in thedatabse) so if it all works they should be on the report.
27043271Filter-report-18.05.11-Q.accdb
Yes the second part deals with no selection, so no selection in all 4 columns should result in all records being returned.
Can you post the sql view of the query.
Can you post the sql view of the query.
Here it is:
SELECT tbl_INTER_CourtsProjects.C
FROM tbl_SUB_TypesOfWorks INNER JOIN (tbl_SUB_TypesOfInterventi
WHERE (((tbl_INTER_CourtsProject
SELECT tbl_INTER_CourtsProjects.C
FROM tbl_SUB_TypesOfWorks INNER JOIN (tbl_SUB_TypesOfInterventi
WHERE (((tbl_INTER_CourtsProject
If you delete the whole 'wher'e clause (remove all selection criteria) do you get any records returned?
If I delete the WHERE part, then there is no criteria for filtering.
If I go to frm_FilterReport and leave all combo boxes empty I still get empty report.
If I go to frm_FilterReport and leave all combo boxes empty I still get empty report.
I understand that if your remove the where clause then you cannot specify any criteria.
The point is, if you get no results when there is no Where clause then the problem is in the basic structure of your query not in the way you are setting the criteria.
You originally said that your report was based on a single table, and yet you have shown a quite complex query as the sql, using several tables. At no point has anyone suggested that you change from a single table to a multi-table query. So I think we need to backtrack and establish what the starting point was.
The point is, if you get no results when there is no Where clause then the problem is in the basic structure of your query not in the way you are setting the criteria.
You originally said that your report was based on a single table, and yet you have shown a quite complex query as the sql, using several tables. At no point has anyone suggested that you change from a single table to a multi-table query. So I think we need to backtrack and establish what the starting point was.
The report is based on 3 tables.
The relatioinships can be seen in the database I attached.
So I use tbl_INTER_CourtsProjects, tbl_INTER_CourtInterventio
I mentioned that I am using fields to filter from different tables. In one of the posts I said that these one-to-many relationships I have many times, make my database kind of complicated.
I said table, meaning not form nor query, but I didn t say single table. :)
The relatioinships can be seen in the database I attached.
So I use tbl_INTER_CourtsProjects, tbl_INTER_CourtInterventio
I mentioned that I am using fields to filter from different tables. In one of the posts I said that these one-to-many relationships I have many times, make my database kind of complicated.
I said table, meaning not form nor query, but I didn t say single table. :)
Premium Content
You need an Expert Office subscription to comment.Start Free Trial