Using a listbox to display query information

[SaskiFX]

I'm trying to get a listbox to display results of a query that are only related to the current record being viewed.

I have a form (setup as a continuous form) that displays a vendor table. I have a query setup to reference what parts a vendor supplies, and I would like those results to be visible on the form. I would use a subform to make this happen, but if I try and drop a subform on the main form, it says it won't do it with a continuous form. So, the listbox. I copied my form, dropped in the subform, and took a copy of the SQL statement. I then took that SQL statement and dropped it on the Row Source of the unbound listbox. I do get a return of Parts and Prices, but its the whole list, not just a list for the currently displayed vendor. My SQL is currently thus:

SELECT tblPrice.[Part Number], tblPrice.Cost, tblPrice.[Price Date], tblPrice.[Bulk Discount], tblVendors.[Vendor Name]
FROM tblVendors INNER JOIN tblPrice ON tblVendors.[VendorID] = tblPrice.[VendorID];

I have tried to wrap my head around it, and I'm stuck. GoogleFu has also failed me, as I can't find something quite on point to what I'm doing.

Any suggestions?



Thanks!

[ssanfu]

I do not see a WHERE clause in the query.
Example:

SELECT tblPrice.[Part Number], tblPrice.Cost, tblPrice.[Price Date], tblPrice.[Bulk Discount], tblVendors.[Vendor Name]
FROM tblVendors INNER JOIN tblPrice ON tblVendors.[VendorID] = tblPrice.[VendorID]
WHERE tblVendors.[VendorID] = forms!MyForm.cboVendorID;

Change to your field and control names.......

[SaskiFX]

That worked great! For anyone else looking for the info, I added the WHERE clause and Access changed it to:

SELECT tblPrice.[Part Number], tblPrice.Cost, tblPrice.[Price Date], tblPrice.[Bulk Discount], tblVendors.[Vendor Name]
FROM tblVendors INNER JOIN tblPrice ON tblVendors.[VendorID] = tblPrice.[VendorID]
WHERE (((tblVendors.VendorID)=[forms]![frmVendorBrowser].[VendorID]));

Then on the Form properties, I set the On Current event to lstParts.Requery so it would update the listbox as I clicked around.

Thanks again for the help.

[ssanfu]

Great!

Ready to mark this solved??

[SaskiFX]

Yes please. I actually hunted around to find out how to do so, but I was unsuccessful.

I figured it might be under the Forum Support link up top, but its a dead link.

[ssanfu]

It is in the 2nd green (on my computer) header under TREAD TOOLS dropdown, the last selection.

[SaskiFX]

Perfect, thanks!