Need to create a selective(filte red) lookup report (thingy?)

[Daedalus]

Ok I'm once again showing my complete (or almost) ineptitude with all things Access.

I'm using Access 2007 and need to do the following:

SETUP:

I have a table of membership information, and a table of dues information. They are linked via a unique ID number so that I can see the data for each entry without opening the corresponding table.

PROBLEM:

I need to create a form (or whatever I need to accomplish my goal) that basically allows me to select the filter parameters by clicking.

Like so maybe:

A form that uses a check box for each month. By selecting the months you want to see and then hitting a "Generate" button. It then generates a report of dues paid in those months, and displays them alongside a number and a name from the other table.

I have no idea how to link each check box to a field in a table and then getting all those selected to be joined in a report.

I'm not even sure it can be done.

Can anyone guide a hapless, gormless, ignorant rookie trying????

Please, with sugar on top?

[NeoPa]

Let's start with the concepts first then. We can sugar coat if you stick with it.

1. You need a report to display the information. Don't worry at this stage about the filtering. Design a report that can show the whole dataset.
2. You need a form to select your criteria from. On the form you need to put whatever controls are required to allow the operator to choose the criteria for selecting only the item(s) they want.
3. When they have selected the selections, a Command Button control should be clicked to trigger your code to run the report.
4. Your code needs to set up a filter string, the complexity of which is determined by your form.
5. Lastly, your code needs to invoke the report, but should include passing the filter string in the invocation.

The effect of this will be that the report (which can show your whole dataset) will just show the filtered data - as you want it to.

[Daedalus]

Damn I was hoping to avoid using codes and I can't write that stuff.

Ok I now have a form that contains twelve check boxes and two command buttons. One button to open the report for the whole year and one that will operate the filter command.

I also have the report created to show the whole year.

[NeoPa]

You'd be surprised what you can manage with a little help ;)

First of all, we need to know what your CheckBoxes are supposed to do. You can start with just a couple of them to give a general idea.

Now look in Example Filtering on a Form to see the concepts explained and even reproduce the example to get a good feel of this subject. When you've done that, come back and we can take you through it as it relates to your situation (if you even need any more help at that stage).

[Daedalus]

I haven't worked with VB code in over 10 years and that was an amateur effort. So help will definently be needed on that.

The checkboxes are all labeled "January, Febuary..." and so forth - one for each month

I also have one command button labeled "Show_Dues_Repor t" to open the report (Called "Dues_Report") showing the whole year.

Lastly I have one more command button labelled "Create_Report" intended to be used for the filtering code to only display those months that have been checked off in the Check Boxes.

[NeoPa]

First of all you don't need to have two separate buttons to open the report.
One button can have code that checks the CheckBoxes and only adds the filter if any are checked (True).

Now, as in my earlier post (#4), I need you to look at the tutorial. This may sound harsh, but I don't want to work with you if you haven't tried it out, as that will be more work for me. If you find that you can't get it to work, let me know. I can work with that. What I can't (read am not prepared to) do is work with people who expect me to do all the donkey work for them. Not that I'm trying to suggest this is you. I'm simply redirecting you to that important bit of the previous post ;)

[Daedalus]

No problem with that, just wanted to stress the point that I am much more familair with Access thatn I am with writing anyything in code.

And that tutorial will be getting some me-time with me shortly.

Nice to know I only need the one button to operate the whole thing.

BUT since the form is set up so that all the code is in one command button, will there be diffucties in tying in the checkboxes to the code, or can it be made to see if functions outside it own object are triggered?

[NeoPa]

...
BUT since the form is set up so that all the code is in one command button, will there be difficulties in tieing in the checkboxes to the code, or can it be made to see if functions outside its own object are triggered?

No. The code behind the button can refer quite easily to all the controls on the form. Checkboxes are particularly easy as you just say :

If [CheckBoxName] Then ...

to see if it's checked.

[Daedalus]

Ok I've been over the tutorial a few times, I think that I'll have to do it again and again cause that was hard reading for me right now.

But I figure that the structure of the code should be along these lines:

IF [Checkbox:Januar y] Then print Table[Dues/Column January] to Report[Dues_Overview]
(obviously not correct formatted code, have no clue yet as to what that should be)
Repeated for other months

And I think I don't need anything special to print the whole thing as I think the structure of the VB code can be set up so that the filter is only activated when one or more checkboxes are used.

If not then I might simply need to start the code with another set of IF [Chechbox] commands to launch the filter code...

Am I on the right track with this...?

[NeoPa]

Ok I've been over the tutorial a few times, I think that I'll have to do it again and again cause that was hard reading for me right now.

But I figure that the structure of the code should be along these lines:

IF [Checkbox:Januar y] Then print Table[Dues/Column January] to Report[Dues_Overview]
(obviously not correct formatted code, have no clue yet as to what that should be)
Repeated for other months

And I think I don't need anything special to print the whole thing as I think the structure of the VB code can be set up so that the filter is only activated when one or more checkboxes are used.

If not then I might simply need to start the code with another set of IF [Chechbox] commands to launch the filter code...

Am I on the right track with this...?

Well, yes and no really.
Yes you do need to check the [CheckBox]es in your code.
No you would not do it in the way you're talking about.

Here's a very simple (as simple as I can make it but my explanations are sometimes considered obscure) explanation of the concepts.

• When running a report, there is an option to Filter the results. IE. A report designed to show everything can be used, without changing the design of it in any way, to show selected items. This is done simply and easily by passing a Filter string when opening it.
• What you need to do (and we can certainly help you here) is to create a string of SQL Filter words from the information contained on the form. The SQL string will contain the whole of the selection requirement before being passed to the DoCmd.OpenRepor t function.
• You can build up the string, one item at a time, in your VBA code connected to the CommandButton that is clicked to run the report.
• The resultant string is simply a list of checks which can be joined logically with AND and/or OR keywords. These checks are to be applied to each record as it is processed to determine whether it should be included in the results (report).