Using MS Access/ListBox RecordSource

Advertisement


Question
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

or

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
Pete

Answer
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 & ";"

Me.ListStkCode.Requery

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,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

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

Experience

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

Organizations
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

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.