Access Multivalue Lookup field - query works... until called from a Form

[webbeacon]

I have a query that searches 3 multivalue lookup fields (Impact, Implementation and Owners) based on input from 3 combo boxes in a form.

When I run the form, it doesn't recognize that the 3 lookup fields exist and asks me to Enter Parameter Values (once for each of the 3 lookup fields). If I click through those and then choose an option from one of the combo boxes and resubmit the query, the datasheet in my form returns 0 results (after asking me for all three parameters again, twice).

HOWEVER... if I just open the query in datasheet view while the form is open and has one of the combo box options selected, the search works flawlessly.

My intuition is that being inside a form, somehow the lookup field values are being loaded... later. After the SQL is read. I'm not sure this is related to the search not working in the form, but it's plausible since if the field values don't exist yet, then how could the form pull any results based on those values?

Since the query is working outside the form, I'm pretty confident the issue isn't in my SQL but I'm including it here anyway for reference:

SELECT ideas.idea_id, ideas.display_name, ideas.idea, tblNotes.cost, ideas.created_at, ideas.labels, tblNotes.Impact, tblNotes.Implementation, tblNotes.Owners, Challenge.Challenge, ideas.official_response_type, ideas.department AS Peoplesoft, tblEmpGroups.DEPTS AS Dept, tblEmpGroups.TEAMS AS Team
FROM (Challenge RIGHT JOIN (tblNotes RIGHT JOIN ideas ON tblNotes.idea_ref = ideas.idea_id) ON Challenge.ID = ideas.challenge) LEFT JOIN tblEmpGroups ON ideas.employee_id = tblEmpGroups.EmpID
 
WHERE IIf(Forms![IMT]!blanks=True,tblNotes.cost Between Forms![IMT]!LowCost And Forms![IMT]!HighCost Or tblNotes.cost Is Null,tblNotes.cost Between Forms![IMT]!LowCost And Forms![IMT]!HighCost) 
 
And ((IIf(Forms![IMT]!cboImpact Is Null,ideas.idea_id Is Not Null, IIF(Forms![IMT]!cboImpact LIKE "All*",tblNotes.Impact.Value IS NOT NULL, Nz(tblNotes.Impact.Value) = Forms![IMT]!cboImpact)))) 
 
And ((IIf(Forms![IMT]!cboImplementation Is Null,ideas.idea_id Is Not Null, IIF(Forms![IMT]!cboImplementation LIKE "All*",tblNotes.Implementation.Value IS NOT NULL, nZ(tblNotes.Implementation.Value) = Forms![IMT]!cboImplementation)))) 
 
And ((IIf(Forms![IMT]!cboOwners Is Null,ideas.idea_id Is Not Null, IIF(Forms![IMT]!cboOwners LIKE "All*",tblNotes.Owners.Value IS NOT NULL, nZ(tblNotes.Owners.Value) = Forms![IMT]!cboOwners)))) 
 
And ((IIf(Forms![IMT]!Challenge Is Null,ideas.idea_id Is Not Null,Challenge.Challenge=Forms![IMT]!Challenge))<>False)
 
ORDER BY ideas.created_at DESC;
 

The main form contains a navigation form, and a subform that references the query. The query is nested 4 times; the above SQL is where I'm pulling in those three lookups from the original table and setting up the WHERE clauses to search them. You'll note that the lookup field reference (e.g. "tblNotes.Owners.Value") appears twice in each AND IIF clause for each of the three lookup fields, which I think explains why when I search in the form I get the popup about Parameter values twice for each field. That was my clue that it maybe has something to do with what data is getting loaded first. It should also be noted that with no combo options selected for those three fields, the form displays all records correctly (including the values in all three lookups).

I would love any pointers you can offer... I'm pretty sure I need to be looking at the form setup (or maybe do something to the query that will work around the default load order?) but so far my Googling has yielded very little progress.

Thanks in advance,
Jen

[webbeacon]

Well... I don't understand why this worked, but it did so I'm going to share.

I was tracing the problem through each subquery (there are four that perform the search function), starting with the bottom level and working my way up to subform and finally the main navigation form. I knew it was working in idea_query4 but wasn't sure exactly where it was broken. Until adding this mutli-value field, everything was working fine... and the only modifications I'd made to the other subqueries was to include the new field in the SELECT.

Long story short, by getting rid of the table name in each of the SELECT statements, all of a sudden it's working in the form. Example:

Old SELECT (not working):

SELECT idea_query2.idea_id, idea_query2.display_name, idea_query2.idea, idea_query2.cost, idea_query2.created_at, idea_query2.labels, idea_query2.Impact, idea_query2.Implementation, idea_query2.Owners, idea_query2.challenge, idea_query2.official_response_type, idea_query2.Peoplesoft, idea_query2.Dept, idea_query2.Team
FROM idea_query2...

New SELECT (working):

SELECT idea_id, display_name, idea, cost, created_at, labels, Impact, Implementation, Owners, challenge, official_response_type, Peoplesoft, Dept, Team
FROM idea_query2...

I would love to understand why this caused an issue.