How do you populate a listbox based on a combo box selection in ms access?

[kills]

Hi Is there any way you can populate a listbox based on a combobox selection?

l have 2 tables, Staff table with staffID, staffName, staffTelNumber and a student table with studentID, staffID, studentName, studentTelNumber. Each staff member is assignned a certain number of students. l want to create a combo box with a list of the staff names and for a given staff member l want to see a list of all the students assigned to them. l have been trying to figure this out now for a couple of days but am getting nowhere fast. Any assistance will be greatly appreciated.

 

[mike-strawberry]

Hi Kills

You can use what names you wish but they must match as mine do.
The form with the combo and list box, lets call that frmMain

If you need further help with my instructions just come back.

Place on it your combo cboStaff make it unbound for now

Add list box LstStudents

For both of the above go to the Other (or all) tab in properties and set the names respectively to cboStaff and LstStudents

Also for the list box properties in the format tab, set the Column Count to 2 and the column widths to 0cm;5cm (the latter being whatever measurement you think appropriate).

The combo box will take a very simple two lines of code that will act on the list box the moment you have made your selection in the combo. Properties Event tab - In the blank column immediately next to AfterUpdate click in there to see the dotted button. Click on that and select Code Builder.
Your insert point should now be between the Private Sub…. and End Sub statements. Type in the following two lines pressing enter key after each.

LstStudents = Null
LstStudents.Requery

Then close the properties and the form and save changes.

Create a query especially for the combo to use.

qrycboStaff will just have the staff table and just the staffName field which I suggest you sort ascending. Close and save.

qryLstStudents will have the Staff and the Student tables which will be joined. You need three fields in this and these will be:

studentID, staffName, studentName

I would suggest studentName be ascending.

You need to put a criteria into the staffName field of this query, type in

[Forms]![frmMain]![cboStaff]

Close and save.

Re-open your form with the combo and list box on and return to design mode and again go to properties in order to attach your queries to them. Go to Data, click in there and from the drop down select the qrycboStaff query in the case of the combo and the qryLstStudents in the case of the list box. Close the form. Re-open it and it should now work. I hope I haven’t forgotten anything. If it doesn’t work come back and I’ll follow my own instructions.

Good luck, Mike