Using MS Access/ListBox RecordSource


Hi Scott,

I want to be able to use three separate Combo boxes, each obviously with their own criteria, but on AfterUpdate of any one of them, the Listbox [ListStkCode] on the form shows different results.  

Example, [CboCategory] AfterUpdate (clicking on "Sundry") would show records in [ListStkCode] of any and all records in TblProducts, where the category is "SUNDRY";
or [CboSupplierID] AfterUpdate would then list all products from TblProducts, supplied by a selected SupplierID - and supersede any choice made in [CboCategory].

ListStkCode = select all from TblProducts where CboCategory = sundry


ListStkCode = Select all from TblProducts where CboSupplierID = JoeCitizen.

If you can help me with the syntax for one [Cbowhatever], then I am sure I can get the other ones to sync too:)

thanks in anticipation

This is a standard technique referred to as cascading or synchronized combos. A search on either of those terms should find plenty of documentation on how to do it. Essentially it means  setting the RowSource of one list control after a selection is made in a different one.

So your After Update event would look something like this:

Me.ListStkCode.RowSource = "SELECT ProductID, ProductName FROM TblProducts WHERE Category = " & Me.cboCategory & ";"


Remember to adjust for the correct datatypes. I'm assuming that cboCategory is returning a numeric category code not the text description.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2016 All rights reserved.