Tips for separating query conditions from form names in GUI

I have inherited a rather large set of Access front-ends. Nearly all of the queries are created with the graphical QBE tool and have the GUI elements hard-coded. Conceptually a query could be:

  SELECT * FROM myTable
  WHERE [Forms]![frm_Main]![frm_TabLine].[Form]![frm_YetAnotherForm].[Form]![SomeId]

Having the GUI elements tied to the query logic just feels wrong to me, and is a royal pain when I have to change something in the front-end. Do you have any recommendations for separating the GUI from the queries? Especially for large (possibly convoluted) legacy QBE queries.

1. For new queries

No problems. Queries are dynamically created in VBA. Any changes to GUI names can be handled with search and replace. I found this SO thread helpful.

2. Legacy Queries in reports

For a legacy reports I remove the WHERE conditions in the query and specify it in VBA using the optional whereCondition argument

DoCmd.OpenReport "SomeReport", acViewPreview, , "GroupNum = " & Me.comboBox1.Value

3. Legacy Queries

For legacy queries I remove the WHERE condition then dynamically create a query in VBA that looks something like this.

SELECT * FROM LegacyQBEQueryNoConditions
WHERE  groupNum = Me.comboBox1.Value   

I haven't determined if I'm taking a huge performance hit on this, but it still feels like a dirty way to do it.

Does anyone have advice on separating the business logic in the forms from QBE queries that allows GUI elements to be changed with a minimum of pain? Or are hard-coded paths in QBE queries just a liability that I have to live with?

The best approach in such case migrate to typed query parameters like described in MSDN article.
So your query will looks like:

PARAMETERS [Employee Title] CHAR;
SELECT LastName, FirstName, EmployeeID
FROM Employees
WHERE Title =[EmployeeTitle];

And VBA to run it:

Set qdf = dbs.QueryDefs("Find Employees")
qdf![EmployeeTitle] = "Sales Manager"
qfd.OpenRecordset...