Open a report based on a filtered list box

[irish634]

Background: I am working on a database for a club membership. I have a main member table which holds contact info, etc., I have a linked table (one to many) which holds dues payments over the years.

I have a report based on a query that yields my desired results of the last paid year for each member in the database. With no filtering, the report opens and shows every member and the last year they paid.



I have a form with a list box. I filter and requery the list box row source with a combo box. This lists either 'all members' or only those members paid for a certain year.

I would like to show the report based on all members in the list box. I basically want to show only the members that are in the list box, in the report.

I know how to open based on a single selection or multi select, but for the life of me, I can't seem to make this work to open the report based on the contents of the list box.

Any ideas?

[aytee111]

The list box row source is a query - you can use that query for your report.

Or use the combo box on the form to create your report query in the same manner that you create your list box row source.

[June7]

You are using combobox to select specific members that you want to report? If not, what is the combobox selecting?

Why not a criteria that is along the lines of 'show only members who haven't paid this year'

DoCmd.OpenReport "report name", , , "fieldname=" & criteria

Really need to know more about data structure and report RecordSource.

[irish634]

The combo box selects: "ALL" or a particular year. The combo box list would look similar to this:

• All
• 2012
• 2011
• 2010

If I select "all", then every member is shown, with the last paid year similar to this:

• Member1, 2011
• Member2, 2012
• Member3, 2012

When I select a given year, only those members paid for a given year show up.


The report is basically a copy of the members shown in the list box, with a little more detail.


I think the criteria solution is giving me fits because of the way my tables and relationships are. The member table is where "Member1" is stored, but the paid year is stored in the sub table and the query can show the same member with multiple years.
So my query would wind up like this:

• Member1, 2011
• Member2, 2012
• Member2, 2011
• Member3, 2012
• Member3, 2011

Both answers pointed me in the right direction, Aytee's response made a light bulb go on for me and for the time being I am changing the underlying query with the QueryDef property and it works fine. It's been one of those days where you think so hard you overlook some easy things....



But the "tinkerer" in me needs to figure out the "criteria" method with the way I have my tables set up.



Thanks!

Craig

[June7]

So when you select 'All' you really mean 'all members and their last year paid'?

[irish634]

So when you select 'All' you really mean 'all members and their last year paid'?

Yes. That is correct.

My data structure is here:
Attachment 6167

My underlying query for 'ALL Members' returns the MAX of the lng_MembershipYear field. This avoids me getting a list of every member along with every year they paid.

When I want to show the report for a certain year only, the above query does not work if I choose to see 2011 when there is also an entry for 2012 (since I am returning the max).

I'll try to upload a sample DB a little later today. I know that's usually easier to see the method to someone's madness. Either way, I have it working by changing the query def, but I'm still interested in figuring this out.