Filtering a subform with a listbox

[Robert C]

I want to base a subform on selections made in a multi select listbox on the main form.

Currently I have created a hidden field on the main form to hold an index of each of the values of the selected records from the list box and I am using this hidden field as the criteria on the query which is the recordsource of the subform.

So a user selects one or more Product Categories form the listbox and the various Products which fall into those categories should then appear in the subform. However this does not work at present. It only works when a single Product Category is selected – and even then the subform does not update immediately when the list box is updated, despite having this code in the AfterUpdate event:

DoCmd.RunCommand acCmdSaveRecord
sbfProductsList.Requery


Please can someone help me sort this out. Thank you all very much in advance

 

[AlanS]

I'd need to see the setting for the list box's ControlSource property to analyze this further, but my hunch is that the problem lies there. If you're only using a single control to indicate which product categories are selected, this cannot work properly (unless you're using a complex bit-slicing scheme like certain Attribute properties do).

When multiple items are selected in a list box, the list box's value is Null. In such a case, you need to use the list box's Selected property or ItemsSelected collection to determine the items that are selected. Your query must refer (directly or indirectly) to Selected or ItemsSelected to return the desired records.

Although I could find no mention of this in Microsoft's KnowledgeBase, it appears that the ItemsSelected collection (including its Count property) is "unreliable" - see http://www.rogersaccesslibrary.com/download.asp?SampleName='MultiSelectProblem.mdb' for examples of both what works and what does not. So, you may want to stick with Selected.

 

[Robert C]

Thanks AlanS

The list box is currently unbound. What should the control source be?

The Row source for the list box is the table which contains the ProductCategoryID and the ProductCategory. Is this what you mean by the selected property?

Sorry if these are basic questions but my knowledge of theis stuff is at best a bit rusty.

Thanks agan for your help

Robert C

 

[AlanS]

Assuming that the list box is being used only to control which records are displayed in the subform (which appears to be the case), the list box should remain unbound (i.e., nothing should be specificied for its ControlSource.

The Selected property (note capitalization) is a property of each list box control. It is visible only at runtime, so if you go into design view and look at the Properties window you will not see it listed; however, you can get or set the property's values at runtime via VBA code. It is a zero-based array, each of whose elements indicates whether or not the corresponding list box item is currently selected.

Check the help screens for the Selected property and the ItemsSelected collection for more information. As indicated in my previous post, I suggest you use the Selected property.