Add a filter to a subform

[rucknroll]

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.

[June7]

The combobox RowSource could be like:

SELECT CreatorID, LastName & ", " & FirstName AS FullName, ... FROM Creator ORDER BY LastName, FirstName;

Options for data entry:

1. one form bound to ITEM CREATOR with two comboboxes to select CreatorID and ItemID

2. main form bound to CREATOR and subform bound to ITEM CREATOR with combobox to select item

3. main form bound to ITEM and subform bound to ITEM CREATOR with combobox to select creator

[rucknroll]

Thanks very much for your help. I have used option 3 above for data entry. This is the code I have used (a little messy):

SELECT [T Creator].[Creator ID], [T Creator].[Creator Title], [Creator Surname] & ", " & [Creator Forename] AS FullName, [T Creator].[Creator Pseudonym], [T Creator].[Creator death date], [T Creator].[Creator notes] FROM [T Creator] WHERE ((([T Creator].[Creator Surname])=[Please enter Creator Surname])) ORDER BY [T Creator].[Creator Surname], [T Creator].[Creator Forename];

The filter now works well, although I have some additional questions:

When trying to modify or edit a link (for example, when an incorrect link has been created or when updating), a user needs to exit a record and go back in again. The same is true if there are two or more creators for an item. Is there a way round this?

Is it possible to create a macro, called (say) 'Search by Creator Surname'? The macro would run the filter. Is it then possible to put a command button on the subform which, when clicked, would run the macro and therefore the filter?

If I also wanted to add a second filter (to filter by another field), would a second subform be needed? Or to use the tab feature and to put each subform and command button on a separate tab? There would then be 2 tabs.

Thanks for any help you can provide.

[June7]

What is that SQL statement for?


Options for filtering records on form:

http://www.datapigtechnologies.com/f...tomfilter.html

http://www.allenbrowne.com/ser-62.html


Not sure what you mean by 'edit a link'. If you mean 'edit a record', should not have to exit and return.