filter-combo box-forms

[Andrew.Champion]

I have built a database for work dealing with students by class numbers. I want to filter the students by the class number they fall into by using a combo box to choose the class number to view students in that class. I am having issues on how to build the combo box value list to add new class numbers as the database expands, and also to choose these values to filter by. I am a beginner with access and know just the basic coding. Any help would be beneficial thank you.

 

[Trevor G]

Andrew welcome to the Forum,

What type of a form are you using? Which version of Access are you using?

 

[Andrew.Champion]

Andrew welcome to the Forum,

What type of a form are you using? Which version of Access are you using?

I am using access 2007, and on the form I am not understanding the question sorry, if it helps i created the form with form design and filled with fields from two tables

 

[Trevor G]

Create a combo box that will find a record based on the form using the class number, then open the properties of the combo and select the Row Source and select the elipsee button to go into the query builder, then group on the field, this will then only show the class number once, then preview the result in the query window to make sure it is only showing them once, then save and close, once back in the form select the Combo Box After Update Event and change what is shown in the code to the following.

Private Sub Combo9_AfterUpdate()
' Filter based on class number

Me.Filter = "(ClassNo) = '" & Me.Combo9 & "'"
Me.FilterOn = True

End Sub

This will then filter the records to the selected class number. Chnage the combo name and field name to match your field and combo box name.

 

[Andrew.Champion]

Create a combo box that will find a record based on the form using the class number, then open the properties of the combo and select the Row Source and select the elipsee button to go into the query builder, then group on the field, this will then only show the class number once, then preview the result in the query window to make sure it is only showing them once, then save and close, once back in the form select the Combo Box After Update Event and change what is shown in the code to the following.



This will then filter the records to the selected class number. Chnage the combo name and field name to match your field and combo box name.

How do i group on the field?

 

[Trevor G]

As per my instructions

open the properties of the combo and select the Row Source and select the elipsee button to go into the query builder, then group on the field, this will then only show the class number once, then preview the result in the query window to make sure it is only showing them once, then save and close

 

[Andrew.Champion]

As per my instructions

It is showing class number for every record, instead of just once

 

[Trevor G]

Again read my instructions.

You need to open the porperties of the combo, select the row source, then go into the query builder and group the field so you only see 1 class number.