You want to read this?
So, I created a Form in Access, with two drop-down lists and a Submit button.
The drop-down lists are each unbound, but populated by the results of a SQL-Passthrough query to the new database on SQL Server 2000.
Pressing the Submit button drops down to VBA, where a SQL query string is generated based on your selections.
VBA then deletes and rebuilds the stored query I cleverly named "Temporary Recordset", complete with DAO connection string. This gives you a new SQL-Passthrough query in it's place based on the query string based on your selections.
It should be noted that the SQL-Passthrough itself is activating a User Defined Function on the SQL server, thus masking several layers of nested queries which are used to create the result set it returns.
Then, VBA launches a nicely formatted report whose record source is a temporary query linking "temporary recordset" to one of our pre-existing tables.
Very simple front-end. Truly frightening back-end. Perhaps the scariest part being that it actually works. This is held together with duct-tape, at best.
Anyway, that's just the template. When the company knows for sure what reports they're going to need, someone will have to modify this to create them. And it's not gonna be me. I'll just be the guy they curse under their breath as they try to make sense of my documentation.