I could write a VBA module that builds the query dynamically but I cant find anything on how to use a module as the source for a form.
If you can create the SQL code for the query in VBA, you can assign that SQL code to a pre-defined query (the one your Form is based on).
So let's say in your VBA you build the SQL code and assign it to a variable named "mySQL". And your Form is based on a query called "myQuery".
Then, in VBA, you can update the SQL code of myQuery with the SQL code you just built like this:
Code:
CurrentDb.QueryDefs("myQuery").SQL = mySQL
You could then, in turn, open your Form in the same VBA code immediately after it, like this:
Code:
DoCmd.OpenForm "myForm"