How can I cascade a Multi-selection List Box and Combo Box on my form?

[DStev]

Hello,



Newbie Access User here. I've attached two files to this message for reference. The first is a partial screenshot of my data entry form and the second is a text file with the VBA code that controls this form.

I'm working on a form for a database that I recently set up. I don't know VBA and had a consultant help me with the setup of one of the data entry forms since there was some customization that I needed help with. It's been a couple weeks and there is one change I want to make to the form. Currently, when the users come into the App (we have a split database setup for multi-users), they have to make a selection from the list box at the top of the form to select the areas with the records that they need to update. The list box allows multiple selections. (The sub routine lstHomeDivision_AfterUpdate() controls this list box and the info in my subforms). After making selection(s), the user can navigate through the records in the set by using the command buttons at the bottom of the screen. I would like to add the functionality of also being able to select records using a combo box. There is currently a text box in my form for FTE number (highlighted in red in the attached file). I would like to change the "FTE No" text box to a combo box that only contains the FTE numbers that relate to the selections in the list box. In the file you'll see that "G&A Finance" is the current selection in the list box, and there are 19 records in this group (one record per FTE). The combo box should only have the FTE numbers for the 19 FTEs in Finance. When I've tried to add the Combo Box the dropdown contains all 560 FTE numbers from across the organization, so I've set it back to just being a text box for now. I know I need to somehow have the list box tell the combo box which records to include, but I don't know how to do this. I've attached a text file with all of the VBA code that controls this form. Any suggestions would be greatly appreciated! Thanks in advance for your help!

DStev

Form Screenshot.pdfForm VBA.txt

[pbaldy]

Here are the basics:

Baldy-Cascading Combos

I'd guess you can use your strWhere in the row source.

[DStev]

Pbaldy, Thanks very much for these examples. Problem solved!

[pbaldy]

Happy to help and welcome to the site by the way!