Filter Combo Box, how?

[Guest]

I have a tabular column form. Let's say ColumnA is "ActionDate" for
date-records, ColumnB is "NumberRecord" for a number relevant to a date in
the "ActionDate" field and some more columns on the form. Yet I only want to
set a combo box on the top of these 2 columns, one is used for filtering
(search) the date in the "ActionDate" and one is used for filtering (search)
the number in the "NumberRecord".
The "ActionDate" has many records, the "NumberRecord" may be just one record.
I understand I can use the "Filter For" tool by using the RH-click on the
field, but it will be more obvious and convenience for other users if I can
create a unbound box for such function.

I think it's not a difficult one for the experienced Access users, but I
have been trying to do that, I even try to copy (similar) settings from this
Discussion Group questions and answers. It just didn't work out.

Please help and I am not using VBA, I use 2003 Access and work mainly with
tables, query, form and macro, simple VBA in "event procedure" only.

I would appreciate if any idea could be given to me please.
thanks
karen

 

[kingston via AccessMonster.com]

Put your combo boxes outside of the form's details section (e.g. in the
header). You'll have to use their AfterUpdate events to build a filter
criteria (a WHERE clause without the WHERE). Then apply the filter to the
form:

Me.Form.Filter = "[ActionDate]=#" & Me.Combo1 & "# AND [NumberRecord]=" & Me.
Combo2
Me.Form.FilterOn = True

Naturally, you'll have to adjust the filter string, check for Nulls, etc.
You might want to look up the topic "cascading combobox."

 

[Guest]

Thanks a lot, Kingston, it works!
As I described about the date in the [ActionDate], there are many records
for "one date", is it possible to display a single row of date,
e.g. 3/22/07 (40 records) and 3/21/07 (30 records), 3/20/07 (30 records),
I want the pull down to display only 3 rows, i.e. 3 dates instead of 40 rows
of 3/22/07, then 30 rows of 3/21/07, etc.
I am sure there must be a way to list single row of date, would you please
help for that.
thanks in advance
Karen

Put your combo boxes outside of the form's details section (e.g. in the
header). You'll have to use their AfterUpdate events to build a filter
criteria (a WHERE clause without the WHERE). Then apply the filter to the
form:

Me.Form.Filter = "[ActionDate]=#" & Me.Combo1 & "# AND [NumberRecord]=" & Me.
Combo2
Me.Form.FilterOn = True

Naturally, you'll have to adjust the filter string, check for Nulls, etc.
You might want to look up the topic "cascading combobox."

I have a tabular column form. Let's say ColumnA is "ActionDate" for
date-records, ColumnB is "NumberRecord" for a number relevant to a date in
the "ActionDate" field and some more columns on the form. Yet I only want to
set a combo box on the top of these 2 columns, one is used for filtering
(search) the date in the "ActionDate" and one is used for filtering (search)
the number in the "NumberRecord".
The "ActionDate" has many records, the "NumberRecord" may be just one record.
I understand I can use the "Filter For" tool by using the RH-click on the
field, but it will be more obvious and convenience for other users if I can
create a unbound box for such function.

I think it's not a difficult one for the experienced Access users, but I
have been trying to do that, I even try to copy (similar) settings from this
Discussion Group questions and answers. It just didn't work out.

Please help and I am not using VBA, I use 2003 Access and work mainly with
tables, query, form and macro, simple VBA in "event procedure" only.

I would appreciate if any idea could be given to me please.
thanks
karen

 

[kingston via AccessMonster.com]

You're welcome. Change the combo box's Row Source to a query if it isn't
already and use the DISTINCT predicate:

SELECT DISTINCT [ActionDate] FROM Table;

Thanks a lot, Kingston, it works!
As I described about the date in the [ActionDate], there are many records
for "one date", is it possible to display a single row of date,
e.g. 3/22/07 (40 records) and 3/21/07 (30 records), 3/20/07 (30 records),
I want the pull down to display only 3 rows, i.e. 3 dates instead of 40 rows
of 3/22/07, then 30 rows of 3/21/07, etc.
I am sure there must be a way to list single row of date, would you please
help for that.
thanks in advance
Karen

Put your combo boxes outside of the form's details section (e.g. in the
header). You'll have to use their AfterUpdate events to build a filter

[quoted text clipped - 29 lines]

 

[Guest]

Perfect!
Thanks a million!

karen

You're welcome. Change the combo box's Row Source to a query if it isn't
already and use the DISTINCT predicate:

SELECT DISTINCT [ActionDate] FROM Table;

Thanks a lot, Kingston, it works!
As I described about the date in the [ActionDate], there are many records
for "one date", is it possible to display a single row of date,
e.g. 3/22/07 (40 records) and 3/21/07 (30 records), 3/20/07 (30 records),
I want the pull down to display only 3 rows, i.e. 3 dates instead of 40 rows
of 3/22/07, then 30 rows of 3/21/07, etc.
I am sure there must be a way to list single row of date, would you please
help for that.
thanks in advance
Karen

Put your combo boxes outside of the form's details section (e.g. in the
header). You'll have to use their AfterUpdate events to build a filter

[quoted text clipped - 29 lines]

thanks
karen