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?
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.
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.
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?
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].[cmbCity]
and
[forms].[frm_FilterReport].[cmbCourtType] as you suggested
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_FilterReport!cmbCity part supposed to do?
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.
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.
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 report is based on 3 tables.
The relatioinships can be seen in the database I attached.
So I use tbl_INTER_CourtsProjects, tbl_INTER_CourtInterventions and tbl_INTER_InterventionsWorks, plus tbl_SUB_Cities, tbl_SUB_TypeOfCourt, tbl_SUB_TypesOfIntervention and tbl_SUB_TypesOfWork
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. :)
I figured it out ....
thanks for the help! you gave me the start!
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
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