Populate list box with query on continous form

[steve.earle]

Hi all,


I am having an issue with populating a list box on my continous form from a query.

I have the query below:

which gives the following results:

All OK so far. I have created a continous form from this query which works as I want. I then added a list box to form which is where the problem is arising:

I have a query called CPs as follows:

which returns the following results (which in real life terms tells you who is the competent person for an inspection, of which there could be more than one - inspection 10 for instance):

On my form, I have set the row source of the listbox to

SELECT qryCPs.fldInspectionID, qryCPs.fldPersonnelName FROM qryCPs WHERE (((qryCPs.fldInspectionID)=[Forms]![frmInspections]![fldInspectionID]));

which should only return the results of the query where the inspectionId matches that displayed on the form.

What I am getting is as per the screen shot below: The list box values are correct for the first entry (it pulls the names from the query for inspection number 10)

however, for inspections 11 and 12 (and all the rest) it is pulling the names for inspection 10.

Where am I going wrong?

[ItsMe]

Your WHERE clause is only going to consider one record at a time.
WHERE (((qryCPs.fldInspectionID)=[Forms]![frmInspections]![fldInspectionID]));

What ever record you have focus on is the record that is selected and the record that will be considered.

Perhaps what you want is to create an inner join with your two query objects and use that as the Row Source for your list box. You can use the ellipses next to the Row Source property of your list box to launch the query builder. Add both of your queries and see if the results are closer to what you are after.

[steve.earle]

Your WHERE clause is only going to consider one record at a time.
WHERE (((qryCPs.fldInspectionID)=[Forms]![frmInspections]![fldInspectionID]));

What ever record you have focus on is the record that is selected and the record that will be considered.

Perhaps what you want is to create an inner join with your two query objects and use that as the Row Source for your list box. You can use the ellipses next to the Row Source property of your list box to launch the query builder. Add both of your queries and see if the results are closer to what you are after.

I am unsure how this would help. Wouldn't it take the first record only again because it is relying on info from the form?

[ItsMe]

How what would help? I am suggesting that your WHERE clause is at the root of your problem. Get rid of it. Create a new query.

[steve.earle]

Thanks. Getting rid of the where clause now gives every competent person for every inspection, for all records (screenshot attached) which is why I put the where clause there in the first place

[ItsMe]

Perhaps what you want is to create an inner join with your two query objects and use that as the Row Source for your list box. You can use the ellipses next to the Row Source property of your list box to launch the query builder. Add both of your queries and see if the results are closer to what you are after.