Multi-query filters

[Annemarie]

I have a combo box with A-Z listed to filter a subform by last name. I have
26 queries to filter with. I want to be able to select "A" in the dropdown,
and have my subform display the "A" query which displays "A" names. My main
form is called lookup_form. The query names are a_last_search, b_last_search,
etc. The subform is called contact_list subform.

I am a newbie to Access, and know very little, but I can't seem to figure
out how to make the combo box display the correct query in the subform.

Any help is appreciated.

 

[Duane Hookom]

You should have only one query that uses the combo box to filter the results
in the query. The critieria in your query might look like:

Like Forms!lookup_form!cboYourA2ZCombo & "*"

 

[Annemarie]

Thanks! When I enter that info on the combo box, I get this error:

Microsoft Office Access can't find the macro 'Like Forms!lookup_form!Combo0
& "*".'

The macro (or its macro group) doesn't exist, or the macro is new but hasn't
been saved.
Note that when you enter the macrogroupname.nacroname syntax in an argument,
you must specify the name the macro's group was last saved under.

Combo0 is the name of my unbound combo box, located in the main form.

 

[John W. Vinson]

Thanks! When I enter that info on the combo box, I get this error:

Microsoft Office Access can't find the macro 'Like Forms!lookup_form!Combo0
& "*".'

Duane was not suggesting that you enter that line in the combo box's
properties. Instead, create a Query in the query design window, based on your
table, and use that string as a Criterion on the name field; and use that
Query as the Row Source of your combo box.

 

[Annemarie]

Ok, I have a query called "_test_search". The one column in the query is
"last_name" from the "contact_list" table. In the criteria field, i have
this: Like [Forms]![lookup_form]![Combo0] & "*"

In my "Combo0" combo box, I have this in the control source:
=[_test_search]!last_name

When I try to filter my form, it dings at me and has this message in the
status bar:
Control can't be edited; it's bound to the expression
'[_test_search]!last_name'.

Where did I go wrong?

 

[John W. Vinson]

Ok, I have a query called "_test_search". The one column in the query is
"last_name" from the "contact_list" table. In the criteria field, i have
this: Like [Forms]![lookup_form]![Combo0] & "*"

In my "Combo0" combo box, I have this in the control source:
=[_test_search]!last_name

When I try to filter my form, it dings at me and has this message in the
status bar:
Control can't be edited; it's bound to the expression
'[_test_search]!last_name'.

Where did I go wrong?

Using the Control Source (the field into which the combo's selected value will
be stored) rather than its Row Source (the query which provides the data
displayed in the combo).

Set the RowSource property to

_test_search

and leave the Control Source blank, since this combo is used only to make a
selection (right?)

 

[Annemarie]

I set the Row Source property to _test_search for what? The combo Box? The
Query? I don't understand.

Ok, I have a query called "_test_search". The one column in the query is
"last_name" from the "contact_list" table. In the criteria field, i have
this: Like [Forms]![lookup_form]![Combo0] & "*"

In my "Combo0" combo box, I have this in the control source:
=[_test_search]!last_name

When I try to filter my form, it dings at me and has this message in the
status bar:
Control can't be edited; it's bound to the expression
'[_test_search]!last_name'.

Where did I go wrong?

Using the Control Source (the field into which the combo's selected value will
be stored) rather than its Row Source (the query which provides the data
displayed in the combo).

Set the RowSource property to

_test_search

and leave the Control Source blank, since this combo is used only to make a
selection (right?)

 

[John W. Vinson]

On Mon, 8 Dec 2008 12:46:26 -0800, Annemarie

You said that the name of the query was "_test_search" so I assumed that that
was indeed the name of the query.

A Combo Box has a "Row Source" property.
This is usually the name of a Query which contains the data which will be
displayed and selected when the combo box is dropped down.

A Combo Box also has a "Control Source" property.
This is the name of the Field in the form's recordsource into which the
selected value will be stored.

I set the Row Source property to _test_search for what? The combo Box? The
Query? I don't understand.

If you don't understand your database (which you can see) then I certainly
don't either (since I cannot see it). I can only go by what you post, and you
did say that the name of the query was "_test_search". Odd name, but ok, I
believe you; and that was the basis of my response.

 

[Annemarie]

When I put "_test_search" (query name) in the row source of the combo box,
the only option in the combo box is "_test_search", not the alphabet as I had
before. My _test_search query looks like this:
column 1 :
field: last_name
table: contact_list
sort:
show:
criteria: Like [Forms]![lookup_form]![Combo0] & "*"

and that's it. Is my query set up wrong?

 

[John W. Vinson]

When I put "_test_search" (query name) in the row source of the combo box,
the only option in the combo box is "_test_search", not the alphabet as I had
before. My _test_search query looks like this:
column 1 :
field: last_name
table: contact_list
sort:
show:
criteria: Like [Forms]![lookup_form]![Combo0] & "*"

and that's it. Is my query set up wrong?

I suspect you have a "Row Source Type" property of "Value List". Change it to
Table/Query using the dropdown and see if that fixes it.

If not, try deleting the combo box and recreating it using the combo box
wizard.

 

[Annemarie]

Ok, I was able to get that to work, but the combo box just lists the names,
it doesn't display the alphabet. How do I make it display the alphabet and
not the names?

I set the Row Source property to _test_search for what? The combo Box? The
Query? I don't understand.

Ok, I have a query called "_test_search". The one column in the query is
"last_name" from the "contact_list" table. In the criteria field, i have
this: Like [Forms]![lookup_form]![Combo0] & "*"

In my "Combo0" combo box, I have this in the control source:
=[_test_search]!last_name

When I try to filter my form, it dings at me and has this message in the
status bar:
Control can't be edited; it's bound to the expression
'[_test_search]!last_name'.

Where did I go wrong?

Using the Control Source (the field into which the combo's selected value will
be stored) rather than its Row Source (the query which provides the data
displayed in the combo).

Set the RowSource property to

_test_search

and leave the Control Source blank, since this combo is used only to make a
selection (right?)

 

[John W. Vinson]

Ok, I was able to get that to work, but the combo box just lists the names,
it doesn't display the alphabet. How do I make it display the alphabet and
not the names?

Ummm...

You want to give the combo a list of names, and have it automagically deduce
that "no, she doesn't want the names, she wants A, B, C, D...?"

If you want a combo box to display the alphabet... use the alphabet as its Row
Source; either create a little 26 row table or (better) use a Row Source Type
of Value List and a Row Source of

A;B;C;D;E; <and so on>

You can then use this combo as a criterion in a query on the Names table as
the rowsource of a second combo to display the names.

 

[Annemarie]

Right, my original question was that I have a combo box with the alphabet A,
B, C... and I want to select a letter, and have it sort the sub form by
people who's last name start with that letter. I can't get it to work. That's
the issue I've been having.

26 queries to filter with. I want to be able to select "A" in the dropdown,
and have my subform display the "A" query which displays "A" names. My main
form is called lookup_form. The query names are a_last_search, b_last_search,
etc. The subform is called contact_list subform.

 

[John W. Vinson]

Right, my original question was that I have a combo box with the alphabet A,
B, C... and I want to select a letter, and have it sort the sub form by
people who's last name start with that letter. I can't get it to work. That's
the issue I've been having.

Ok... I think we can get this wandering thread back on track!

Part of the problem is your use of the word "sort". "Sort" in database jargon
means "to put a set of records in a specific order". I think you mean "to
select a subset of the records" - i.e. when you pick B from the alphabet
combo, you want the subform to display names from Baalam through Byers?

If so, use the Value List combo box suggested above (let's name it cboAlpha),
and base the Subform on a Query using

LIKE [Forms]![YourFormName]![cboAlpha] & "*"

Then Requery the subform in the AfterUpdate event of cboAlpha.

 

[Annemarie]

What do you mean "Requery the subform in the AfterUpdate event of cboAlpha"
I put the combo box back in as a value list. i put the string you had below
into the _test_search query in the last_name column. Is that correct?

Right, my original question was that I have a combo box with the alphabet A,
B, C... and I want to select a letter, and have it sort the sub form by
people who's last name start with that letter. I can't get it to work. That's
the issue I've been having.

Ok... I think we can get this wandering thread back on track!

Part of the problem is your use of the word "sort". "Sort" in database jargon
means "to put a set of records in a specific order". I think you mean "to
select a subset of the records" - i.e. when you pick B from the alphabet
combo, you want the subform to display names from Baalam through Byers?

If so, use the Value List combo box suggested above (let's name it cboAlpha),
and base the Subform on a Query using

LIKE [Forms]![YourFormName]![cboAlpha] & "*"

Then Requery the subform in the AfterUpdate event of cboAlpha.

 

[John W. Vinson]

What do you mean "Requery the subform in the AfterUpdate event of cboAlpha"
I put the combo box back in as a value list. i put the string you had below
into the _test_search query in the last_name column. Is that correct?

My telepathy isn't working very well, Annemarie. Please open your query in SQL
view and post it here.

To requery, view the Properties of the form. Select the combo box cboAlpha. On
the "Events" tab find the "After Update" event; click the ... icon by it.
Select "Code Builder". Access will give you the first and last line - edit in
the one additional line:

Private Sub cboAlpha_AfterUpdate()
Me!yoursubformnamehere.Requery
End Sub

replacing yoursubformnamehere with the actual name of your subform (the
Subform control on the mainform, not the Form within that control).

 

[Annemarie]

Here is the _test_search query:
SELECT contact_list.department, contact_list.first_name,
contact_list.last_name, contact_list.title, contact_list.phone_extension,
contact_list.email_address
FROM contact_list
WHERE (((contact_list.last_name) Like [Forms]![lookup_form]![cboAlpha] &
"*"));


Here is the a_last_search query which displays only A's
SELECT contact_list.department, contact_list.first_name,
contact_list.last_name, contact_list.title, contact_list.phone_extension,
contact_list.email_address, center_department.department_category
FROM contact_list INNER JOIN center_department ON contact_list.department =
center_department.department
WHERE (((contact_list.last_name) Like "a*"));

 

[Annemarie]

It's working now. Thank you SO much!

Here is the _test_search query:
SELECT contact_list.department, contact_list.first_name,
contact_list.last_name, contact_list.title, contact_list.phone_extension,
contact_list.email_address
FROM contact_list
WHERE (((contact_list.last_name) Like [Forms]![lookup_form]![cboAlpha] &
"*"));


Here is the a_last_search query which displays only A's
SELECT contact_list.department, contact_list.first_name,
contact_list.last_name, contact_list.title, contact_list.phone_extension,
contact_list.email_address, center_department.department_category
FROM contact_list INNER JOIN center_department ON contact_list.department =
center_department.department
WHERE (((contact_list.last_name) Like "a*"));

My telepathy isn't working very well, Annemarie. Please open your query in SQL
view and post it here.

To requery, view the Properties of the form. Select the combo box cboAlpha. On
the "Events" tab find the "After Update" event; click the ... icon by it.
Select "Code Builder". Access will give you the first and last line - edit in
the one additional line:

Private Sub cboAlpha_AfterUpdate()
Me!yoursubformnamehere.Requery
End Sub

replacing yoursubformnamehere with the actual name of your subform (the
Subform control on the mainform, not the Form within that control).