Filter function in subform

[Boon]

Hello,

When you open a table, you can click on the right portion of field's name on
each field and Access will pop up the option for you to sore, filter.

Supposed that I have a table contains states and its zip codes. Just two
columns. When I open this table, move the cursor to the right area in the
field name of column 'state', I can filter on which states I would like to
see. There are check marks in front of each state.

Now,
I put this table in a subform within a form. I cannot do the same as I
explained above. There is no a list of states with check mark for me to
choose from.

Is there a way to have a checkmark in the subform?

Thanks a lot,
Boon

 

[Clifford Bass]

Hi Boon,

Is the subform linked to the form? If yes, are there are rows listed
in the subform? If not, that is why. Once you add rows, you will get a
check box, but only for the current state in the main form.

Clifford Bass

 

[Boon]

There are rows in the subform.

I am not sure if I understand what you meant by "linked". What I did is.. I
have a combo box in main form, then I wrote VBA code to requery the subform
after the combo has been updated.

Thanks,
Boon

 

[Clifford Bass]

Hi Boon,

When you create a subform you have the option to link it to the main
form. If you add the subform with the wizard turned on (the wand with the
dots/stars falling from the top), it will prompt you on how to link the
subform to the form. Or, if it is already added, clicked once on the subform
so that it is selected as a whole. In the properties window you will see a
"Link Master Fields" and a "Link Child Fields" item. Click in either of them
and click on the little button with the three periods. You can then choose
the related fields in each form. When subforms are linked, they
automatically display only the records related to the current row in the main
form. So, if you have the states listed in the main form and the Zip codes
for the states listed on the subform, and you have them linked on the state
code, when you are on Montana in the main form, it will only display the Zip
codes for Montana in the subform. No need to do any coding. That is, unless
you are doing something other than a straight-forward parent/child display.
Also, for the sort option to appear in the subform, it needs to be in
Datasheet mode.

Does that help?

Clifford Bass

 

[Boon]

Hi Clifford,

I didn't use Link Master Fields or Link Child Fields in my subform. Instead,
I use Record Source, and making query in it.

I set up the subform as datasheet, and I am able to see and use the sort
option.

The situation is this... I have combo State in the main form. Says I
choose Wisconsin in the combo, the subform will display all the zipcodes in
WI. I then can sort this zipcode in the subform. (This has been done
already). But, I cannot filter it to see just one zipcode. (this is just an
example, it might seems weird to filter to see only one zipcode).

Or.... The only way to see the filter in the subform is to do the link
master fileds and link child fields?

Again, by filter I mean to be able to do multiple selections.

I appreciate your helps, thanks.

 

[Clifford Bass]

Hi Boon,

Oops, I meant the filter options when I said sort. Regardless, when I
replicate what you describe, I do get the option to select specific Zip codes
for filtering. What version of Access are you using? Double-checking: Is
the Default View property of your subform set to Datasheet?

Clifford Bass

 

[Boon]

Hi,

I use Access 2007.

The default view is datasheet. Strange huh?

- I didn't use link master field or link child field.
- I can sort, use filter text option, but no list to choose multiple
selection.

I have noticed for a while that in Access, when you open a table when try to
filter a field, if the value in that field is too many, it will not have a
multiple selection. For example, I can do multiple selection on zip codes,
states, countries. But I cannot do multiple selection on invoice ID
(#######). It is like there are too many different values.

Thank you,
Boon

 

[Boon]

Hi,

I think I know why I cannot use multiple selection.

As I described eariliy, I use combo in the main form to filter the value in
subform. In the subform>Data source> in query view, I use criteria in the
query. i.e. "Like form!combo2.value".

Could this be the reason?

thanks.