Using a form to select criteria for a report

[aselm01]

Hi all,

I need help with forms and reports. I am a beginner when it comes to most of access features. I have created a database for generating checklists. These checklists need to be generated based on two categories (Compliance level and phase). I have created some queries based on the main table (TABLE_Checklist) and reports that show the results of each query. This does not get the job done though and is cumbersome. What I want to do is have a form or report that lets me select from the two categories and then generate the report based on those selections.

The categories have multiple choices for each so there can be several combinations. The compliance level category has these three options: 1) Compliance, 2) Non-Compliance, and 3) Not Applicable while the other category, Phase, has these choices" 1) All Phases, 2) Design Phase, 3) Code Phase, and 4) Test Phase. I have these setup as check boxes, but can change them to other types if needed.

I need to be able to generate a report that will show only those records that have one of the three compliance levels or a combination of the three and also either the all phases selection or any combination of the other phases (i.e. Design and code phases).

So far I have tried a form that was based on a query that was based on the main table and just a bunch of queries that were filtered for each selection. I ended up with a bunch of queries and reports that just did not cover it all. I have searched the forums and the internet, but have not had any luck. I have found some solutions that may work if I was only filtering on the compliance category, but nothing that will work for it all. So now I am turning to you for help in the hope that somebody here will know how to do what I want and will be able to show me the way.

I am attaching a copy of the database that has been stripped of the many reports and queries, but still has the core info and a form and report that seemed to work. The problem with this current setup is that when I run it in access 2007 it works sort of, but inserts blank records in my main table and does not let me pick a combination of compliance levels. When I run it in access 2010 it shows a blank form so I don't know what is up there.

Please help and thanks for taking the time to read this.



Safety Checklist.zip

[ssanfu]

I played around with frm_Checklist.

Things I did:
- Changed the Form record source to a query. Easier to filter and sort records. See http://access.mvps.org/access/tencommandments.htm ..... #3
- Renamed some of the fields in "TABLE_Checklist". There were spaces in names and special characters.
- I changed the radio buttons to check boxes for "Phases". Radio buttons imply "select only one", whereas check boxes indicate there can be multiple selections.
- Rearranged some of the controls. Moved the record navigation buttons to the footer. Close, Add and Delete buttons to the header.
- Added a method to filter the records. It is an "And" filter; as in "This AND That AND Another". All have to be TRUE to return the record.

If you don't like the automatic filtering, comment the lines that read " Call btnSetFilter_Click" and use the "Set Filter" button

[aselm01]

I played around with frm_Checklist.

Things I did:
- Changed the Form record source to a query. Easier to filter and sort records. See http://access.mvps.org/access/tencommandments.htm ..... #3
- Renamed some of the fields in "TABLE_Checklist". There were spaces in names and special characters.
- I changed the radio buttons to check boxes for "Phases". Radio buttons imply "select only one", whereas check boxes indicate there can be multiple selections.
- Rearranged some of the controls. Moved the record navigation buttons to the footer. Close, Add and Delete buttons to the header.
- Added a method to filter the records. It is an "And" filter; as in "This AND That AND Another". All have to be TRUE to return the record.

If you don't like the automatic filtering, comment the lines that read " Call btnSetFilter_Click" and use the "Set Filter" button

What you did is really great and I like the way you have it setup, but how can I use that to generate a report based on that filter? That is my big issue, I need to select whatever combo of the choices and then have a report I can print. That report becomes my checklist for my internal customers.

Again thanks for what you have done, it is great.

[ssanfu]

Will this work for you? (See attached mdb)

[aselm01]

Steve,

This is awsome! It is pretty much what I had in mind thank you very much. Now I just have to review the code and try to figure out how you did it. I find I learn best by doing so I hope I will understand it all.

One question about the filter. Is there a way to set it so that if I select only the Design Phase it only returns records with that? I mean right now if design is selected it returns all records with design and also those records that have design and any other selections (i.e. design and code). I don't want to push my luck because you have been so helpful, but I need that feature too.

Also do you have any guidance on where I can learn the cool things that you have done here? A specific book or something? I find I am doing more and more in Access and the more I can learn the better.

Again thank you for everything you have done.

Tony

[ssanfu]

Tony,
Just read and try, read and try. Ask question here at the forum. And time.... I look back a code I wrote a year ago and I cringe. Just keep at it.

How is this???

[aselm01]

Tony,
Just read and try, read and try. Ask question here at the forum. And time.... I look back a code I wrote a year ago and I cringe. Just keep at it.

How is this???

Steve,

It still returns all records that contain design as one of the selections. So the results are design records and design+code records and design+test records. It is the same if you select only code or test as the phase you get the same type of result.

I read the code and I think I understand what you were saying earlier about it being "And this And that And another", but could that be why it returns all of the records that contain a selected phase? I see how the And statements work if you want more than just one set of records, but I don't understand where in the code it would limit the records to only what is selected or I am just not understanding it enough?

All I know is you are doing great things and it makes my head hurt trying to figure it out.

Tony

[ssanfu]

Maybe I misunderstood..... Were you talking about limiting records to just one phase in the reports or wanted to limit the records in the form??

[aselm01]

Maybe I misunderstood..... Were you talking about limiting records to just one phase in the reports or wanted to limit the records in the form??

Steve,

Sorry for the confusion. What I want my internal customers to be able to do is select one of four the compliances (Compliance, Non-Compliance, NA, or Compliance and Non-Compliance together) and to be able to do the same for the phases. I have a need to have a report or reports that show only the records that have been marked as compliant, non-compliant, NA, and then both compliant and non-compliant together. Then there are the phases that need to be the same way. Using the 4 compliance choices I then need to be able to further divide them up by phase. So If I select compliant and all phases I should end up with a report that only contains those records that have been marked compliant and have been marked as belonging to all phases only no others. The same holds true for the other combinations. If I select compliant and non-compliant and then select design and code I should only get those records that are marked as both compliant and non-compliant and design+code and no others. So that report would not contain any records that are just compliant and design or just non-comliant and code.

There are many combinations and I thought it would be much easier if there was a way to make the selections via form and then generate the filtered report.

What you have provided is really great and does most of what I want, but being able to get only the selected compliance(s) and phase(s) would be ultimatley what I need.

Tony

[ssanfu]

Still working on it... Got slammed at work... stay tuned

[ssanfu]

I think I am really confused by what options you want available.
I opened the ver 3 in Access 2007 () and can't get it back to Access 2000.

Here is ver 4 .... and I still don't know whether you want the FORM or Report.... or FORM AND Report.

So look at the FORM. You can select any compliances with any phases. How close is this ????

[aselm01]

I think I am really confused by what options you want available.
I opened the ver 3 in Access 2007 () and can't get it back to Access 2000.

Here is ver 4 .... and I still don't know whether you want the FORM or Report.... or FORM AND Report.

So look at the FORM. You can select any compliances with any phases. How close is this ????

Steve,

Sorry I did not reply earlier, I too got slammed at work and then there was the holiday. I hope you enjoyed the 4^th. Rev 4 is close but not all the way there.

I need to be able to create a report that allows me to show only the records I select via the selection form. So far I think you are on the right track with “frm_selectreport” and what you have just needs a little bit of modification (I tried, but just couldn’t get it).

Here is an attempt to clarify. I need to be able to create a report that shows the following combination(s) of records:
Note: Keep in mind that a record will only be marked as being in “Compliance”, “Non-Compliance”, or “Not Applicable”.
I need to be able to create a report that shows only the records that have been marked as being in “Compliance”.
I need to be able to create a report that shows only those records that have been marked as being in “Non-Compliance”.
I need to be able to create a report that shows only those records that have been marked as being “Not-Applicable”.
I need to be able to create a report that is just a combination of the above reports for “Compliance” and “Non-Compliance”. So it has all records that are marked only as “Compliance” and all records that have been marked only as “Non-Compliance”. By doing this I will have four main reports.

Now to further complicate all of this I also need to be able to do all of the above plus, on an as needed basis, be able to have these reports be filtered by their phase(s). This means that the report being created will only contain those records that are marked as being in one of the following combinations of phase:
All Phases (this will always be the only phase if selected) or one of the following other combinations: Design, Code, Test, Design and Code, Design and Test, or Code and Test

To bring it all together I need to be able to create a report that, for example, only contains those records that have been marked as being in “Compliance” (or one of the other two categories). Also I need to be able, as needed, to create one of those same reports that has been filtered by phase too. The phase will be either “All” phases or one of the combinations of the phases except for All. An example of this could be a report that has only those records that have been marked as being in "Compliance" and having the "Design" and "Code" phases selected. So if there are only two records that have both "Design" and "Code" phases selected and they both have been marked as being in "Compliance" then I would have a report created that only has those two records in it.

There are many combinations so I need a very flexible selection form that will allow me to create whatever report needed.

I hope that this has helped if not then I am not sure what I am missing in my explanation.

Tony

[ssanfu]

Here is an attempt to clarify. I need to be able to create a report that shows the following combination(s) of records:

The filter is for the REPORT only or the report AND form?

[aselm01]

The filter is for the REPORT only or the report AND form?

Just for the reports only.

What you have works for just about everything, but the option to create a report that shows all records marked as Compliance and all records marked as Non-Compliance together. For example if I had 20 records that were marked as compliance and 10 records marked as Non-Compliance then the the reports would be like this: 1) shows only the 20 Compliance records, 2) one shows only the 10 Non-Compliance records, and 3) one Shows both the 20 Compliance and 10 Non-Compliance records.

[ssanfu]

Is there ever a time that you would NOT select Compliance, Non-Compliance, Not-Applicable or (Compliance and Non-Compliance)? ie all three check boxes would be unchecked?