I am trying to apply a filter to a subform but don’t know how to go about it.
I have three tables as follows:
ITEM (primary key Item ID) (AutoNumber)
CREATOR (Primary key Creator ID) (AutoNumber)
ITEM CREATOR (junction table) (primary key Item ID and Creator ID)
ITEM has a one to many relationship with ITEM CREATOR.
CREATOR has a one to many relationship with ITEM CREATOR.
I have added a subform to the data entry form for ITEM. The fields in the subform are the fields in the ITEM CREATOR table, i.e. Item ID and Creator ID. The purpose of the subform is to link an Item to a Creator and populate the ITEM CREATOR table.
These are the subform properties:
Record Source: Item Creator subform
Link Master Fields: Item ID
Link Child Fields: Item ID
The subform contains a combo box for Creator ID. These are the properties.
Control Source: Creator ID
Row Source: Item Creator Subform query
Bound Column: 1
Column Count: 9
When a user clicks on the combo box drop-down, the first 9 fields in the CREATOR table are displayed. The purpose of this is to give the user more information when linking ITEM to CREATOR, so that the correct Creator ID is chosen and the Item is linked to the correct Creator. Two of these fields are Creator Surname and Corporate Creator.
This works well.
The issue is that CREATOR now contains over 2500 records. When trying to link an Item to a Creator the drop-down list that appears when the Creator ID combo box is clicked is very long and the user is potentially faced with a long time spent scrolling the list. This is not very user-friendly.
I would like to filter the drop-down list by Creator Surname so that the user enters a surname and only the Creator IDs for the Creators that have that surname appear in the list.
In addition, I would like to filter the list by Corporate Creator.
I hope that the above makes sense. I would much appreciate any help.
Add a text box above your drop down list for Surname, and another combobox for corporate creator with it's datasource being something like "SELECT DISTINCT CorporateCreator FROM Creator ORDER BY CorporateCreator ASC".
Add a command button that creates your filter from the values in both the surname text box and the corporate creator drop down when you click it.
Then apply that filter as a WHERE clause to primary query using the RecordSource property of your primary dropdown with the nine fields in it and do a requery on it.
Something like the code below:
Assume here that you have a query defined that pulls the nine fields from the creator database without any filtering. Lets say for example that its name is qryCreatorItemsForLink.
Let's say that your combobox that shows this list is called cbxCreatorsList.
Let's further say that the combobox for the set of corporate creators is named cbxCorpCreators
Let's also assume that the surname filter field is called txtSurname.
And finally, assume we "have a perform the filtering" command button called btnFilterIt.
I am also making a couple assumptions here about the names of the fields in your Creators table. Change them to the real things.
Anyway, something similar to this ought to get the job done.
Private Sub btnFilterIt_Click()
Dim strCreatorQuery As String
'* do some appropriate processing here to ensure that they have provided a surname and selected a corporate creator
'* you write this code if you want it to check those things, and you should!
'* ok, now assuming those things have been validated, build the filtered query
strCreatorQuery = "[Creator].[Surname] LIKE '" & Me.txtSurName & "*' " ' using like here will still return filtered results on just a partial surname
strCreatorQuery = strCreatorQuery & " AND [Creator].[CorporateCreator] = '" & Me.cbxCorpCreators.Value & "'"
Me.cbxCreatorsList.RowSource = "SELECT * FROM qryCreatorItemsForLink WHERE " & strCreatorQuery & ";" '* Add an ORDER BY clause on here before the ; if one is desired