Minor problem using a query to filter a report

[nemo700]

Hi guys,

I hope this is a really easy one to solve, and that I'm just missing something trivial that'll make everything snap into place...

Here's the cast -

Tables -
people
bth_modules
bth_results (joined to people (by person) and to bth_modules (by module))

Query -
photosheet_generator_by_bth_code

SELECT people.firstname, people.surname, people.photopath
FROM people INNER JOIN bth_results ON people.id = bth_results.person
WHERE ((bth_results.module)=[Forms]![photosheet_generator]![perbthselect]);

Form -
photosheet_generator

Report -
photosheet

photosheet_generator provides the user with an unbound combo box ("perbthselect") from which they can select items sourced from bth_modules. When they then click on a button on the form, the following vba is called

DoCmd.OpenReport "photosheet", acViewPreview, "photosheet_generator_by_bth_code", , acWindowNormal

I want photosheet_generator_by_bth_code to make photosheet only show those people where they have a row in bth_results that matches perbthselect, rather than everyone from people.

If I use VBA to open the query instead of the report, all is well... but if I go for the gold and try to open the report using the above code, I'm prompted for bth_results.module, and whatever I enter seems to have no effect; photosheet shows everything.

I think the problem is that photosheet is only based on people, with no mention of bth_results in its record source, but if I start trying to change the report's record source, I start to lose the versatility of the form; for example, I eventually want to be able to pass photosheet other queries that have nothing to do with bth_results.

Does anyone have any suggestions for how I can tell photosheet to ignore the fact that it can't directly talk to bth_results, and just use the output of the query instead (which are all fields from people)?