Hi Andrew:
Actually, what I frequently do is use a combobox along side of a listbox to
filter the combobox elements. For example, you can have a field for "movie
type"... the user would type in "action movie" and the listbox would show
all of the action movies. Of course, you should also add a clickbutton to
get rid of all filters.
The following example does this for chemotherapy regimen consent form
elements associated with a cancer type (i.e. select "colon cancer" and you
get the 3 chemo regimen types consent letters):
STEP 1:
Form's Record Source property-
SELECT ConsProtFm.* FROM ConsProtFm ORDER BY [ConsProtFm].[chemoregimen];
Note- in your example, the ConsProtFm would be analogous to a list of
movies, which includes a field for "movie types".
STEP 2:
COMBOTT combobox's Row Source property-
SELECT [Staging].[DiseaseName] FROM Staging ORDER BY
[Staging].[DiseaseName];
Note- the COMBOTT is the filtering combobox on List46. The Staging table is
a table which lists the various tumor types... in your example you would
have a table which lists the various movie types.
STEP 3:
List46 Row Source property-
SELECT [CONSPROTFM].[CHEMOREGIMEN], [CONSPROTFM].[PRECHEMO1],
[CONSPROTFM].[TUMORTYPE] FROM CONSPROTFM WHERE
((([CONSPROTFM].[TUMORTYPE])=[FORMS]![CONSENTPROTPT]!COMBOTT)) ORDER BY
[CONSPROTFM].[CHEMOREGIMEN];
Note- the Tumortype is the feild where the filter is being set by Combott.
STEP 4:
COMBOTT afterupdate event-
[List46].RowSource = "SELECT CONSPROTFM.CHEMOREGIMEN,
CONSPROTFM.PRECHEMO1,CONSPROTFM.TUMORTYPE FROM CONSPROTFM WHERE
CONSPROTFM.TUMORTYPE=[COMBOTT]ORDER BY CONSPROTFM.CHEMOREGIMEN;"
Note- the Consportfm is the table where the data exists, the filter is
placed on the Tumortype field, and the Chemoregimens are the field items
that show up in the List46 listbox.
Now you can get the same effect using a simple field by placing step 4 as
the OnClick event of a clickbutton (in this instance, the text field's name
would be "ComboTT"). I've done this when filtering records by alphabet, i.e.
ABC, DEF, GHI, etc. Now, you should also add-
STEP 5:
Place a clickbutton on the form to get rid of the filter. On the OnClick
event, place-
[List46].RowSource = "SELECT CONSPROTFM.CHEMOREGIMEN,
CONSPROTFM.PRECHEMO1,CONSPROTFM.TUMORTYPE FROM CONSPROTFM ORDER BY
CONSPROTFM.CHEMOREGIMEN;"
(to again select all of the records).
Regards,
Al