Need Help Filtering a Form with VBA

[EricG]

I have a form whose underlying data comes primarily from "Table1" (for this
example). On the form is a drop-down box that displays a field named
"Primary OBS" for each record. "Primary OBS" is stored in "Table1". It is
an index into another table "Table2" which contains a field named "Team
Code". "Team Code" can look like "JD130000" or "JG320000", etc.

What I need to do is to filter the records in the form by the first two
letters of the "Team Code". In other words, the user can elect to filter by
"JD" and will get all the teams associated with that top-level code.

How can I do a filter in VBA using doCmd.ApplyFilter or some other method
that will perform this action? I can easily filter the index "Primary OBS"
itself, but how can I filter on what the index is pointing to in this other
table?

Thanks in advance,

Eric

 

[Marshall Barton]

I have a form whose underlying data comes primarily from "Table1" (for this
example). On the form is a drop-down box that displays a field named
"Primary OBS" for each record. "Primary OBS" is stored in "Table1". It is
an index into another table "Table2" which contains a field named "Team
Code". "Team Code" can look like "JD130000" or "JG320000", etc.

What I need to do is to filter the records in the form by the first two
letters of the "Team Code". In other words, the user can elect to filter by
"JD" and will get all the teams associated with that top-level code.

How can I do a filter in VBA using doCmd.ApplyFilter or some other method
that will perform this action? I can easily filter the index "Primary OBS"
itself, but how can I filter on what the index is pointing to in this other
table?

First create a query that joins the two tables. Select only
the fields from the two tables that you need for the form
including the team code field. Use the query as the form's
record source.

Now, you should have an unbound form with a text (or combo)
box (named txtLetters) for users to enter the two letters.
This form would also have a command button they can click to
open your table1 form. Then, in the code for the button,
use the OpenForm method's WhereCondition argument to filter
the form. The button's Click event procedure could be
something like:

DoCmd.OpenForm "name of table1 form", _
WhereCondition:= "[Team Code] Like """ & Me.txtLetters &
"*"" "

 

[EricG]

Thanks for the response. I'll give your suggestion a try.

Eric

I have a form whose underlying data comes primarily from "Table1" (for this
example). On the form is a drop-down box that displays a field named
"Primary OBS" for each record. "Primary OBS" is stored in "Table1". It is
an index into another table "Table2" which contains a field named "Team
Code". "Team Code" can look like "JD130000" or "JG320000", etc.

What I need to do is to filter the records in the form by the first two
letters of the "Team Code". In other words, the user can elect to filter by
"JD" and will get all the teams associated with that top-level code.

How can I do a filter in VBA using doCmd.ApplyFilter or some other method
that will perform this action? I can easily filter the index "Primary OBS"
itself, but how can I filter on what the index is pointing to in this other
table?

First create a query that joins the two tables. Select only
the fields from the two tables that you need for the form
including the team code field. Use the query as the form's
record source.

Now, you should have an unbound form with a text (or combo)
box (named txtLetters) for users to enter the two letters.
This form would also have a command button they can click to
open your table1 form. Then, in the code for the button,
use the OpenForm method's WhereCondition argument to filter
the form. The button's Click event procedure could be
something like:

DoCmd.OpenForm "name of table1 form", _
WhereCondition:= "[Team Code] Like """ & Me.txtLetters &
"*"" "