Using a query to fill combo-box with only results related to the selected record.

[IncidentalProgrammer]

I am still very new to all of this, and not so good with queries. I have been trying this for days, and can't get it to work.



I have an unbound combo box on a form meant to view insurance policies. The combo box is meant to allow users to select which producer's info they want to view in a subform (multiple producers per policy). The control between the combo box and the subform works, but I can't get the combo box to list only the producers that belong to the policy being displayed; it either just lists all the dummy producers from the table, or (as of my latest attempt) it gives me an "Enter Parameter Value" popup.

Here's what I'm trying right now:

SELECT tblPolProd.PolNum, tblPolProd.EntID, tblPolProd.PolPPosition
FROM tblPolProd
WHERE (((tblPolProd.PolNum)=[frmPolicy3rd].[ctlPolNum]))
ORDER BY tblPolProd.PolPPosition;

The "WHERE" is when the policy number viewed on the form matches the policy number associated with that producer.

I've tried switching the order in the "WHERE" as well, and it still keeps giving me the "Enter Parameter Value" popup asking for "frmPolicy3rd.ctlPolNum" (that's the policy view form and the control that displays the policy number).

Where am I going wrong?

[ranman256]

Use the BUILDER, when you have to reference form objects in a query. (The magic wand icon with elipsis) That way there wont be typos.

You have to reference the WHOLE path of the form object.: forms!frmPolicy3rd!ctlPolNum

[IncidentalProgrammer]

You're going to laugh. I had found another guide to try, and it showed the form path like that, I used it, and now it filters correctly!

But it shows each result TWICE. Why would it do that?

This project is going to drive me to drinking.

[IncidentalProgrammer]

Nevermind! Figured it out.

It just had an extra chromosome...