By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,667 IT Pros & Developers. It's quick & easy.

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

P: 30
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:

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
  4. 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) 
  6. 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)))) 
  8. 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)))) 
  10. 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)))) 
  12. And ((IIf(Forms![IMT]!Challenge Is Null,ideas.idea_id Is Not Null,Challenge.Challenge=Forms![IMT]!Challenge))<>False)
  14. 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,
Mar 23 '16 #1
Share this Question
Share on Google+
1 Reply

P: 30
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):
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM idea_query2...
New SELECT (working):
Expand|Select|Wrap|Line Numbers
  1. SELECT idea_id, display_name, idea, cost, created_at, labels, Impact, Implementation, Owners, challenge, official_response_type, Peoplesoft, Dept, Team
  2. FROM idea_query2...
I would love to understand why this caused an issue.
Mar 29 '16 #2

Post your reply

Sign in to post your reply or Sign up for a free account.