Bypass Filter

  • Thread starter Thread starter Drew
  • Start date Start date
D

Drew

I have a two list boxes on my switchboard. One of the list boxes contains
company names (list box A). The second contains products purchased which are
linked to a table which identifies the product purchased by company (list box
B). Currently when I click on list box A only the relevant products (to the
company chosen) show up in list box B (this is what I want to happen),
however, I also have an "All" option in the list box A which I would like to
have override the filter to show all of the products without respect to
company in list box B. Is this a VB issue or a query issue?

The way that it is setup now is that when the company is selected in list
box A I have VB code setup to requery the query that supplies the info in
list box B which is filtered on the selection in list box A. I assume that
there is VB code that I can use to just override the filter.
 
I have a two list boxes on my switchboard. One of the list boxes contains
company names (list box A). The second contains products purchased which are
linked to a table which identifies the product purchased by company (list box
B). Currently when I click on list box A only the relevant products (to the
company chosen) show up in list box B (this is what I want to happen),
however, I also have an "All" option in the list box A which I would like to
have override the filter to show all of the products without respect to
company in list box B. Is this a VB issue or a query issue?

The way that it is setup now is that when the company is selected in list
box A I have VB code setup to requery the query that supplies the info in
list box B which is filtered on the selection in list box A. I assume that
there is VB code that I can use to just override the filter.
Click to expand...

How are you currently setting the ListBoxB rowsource.

I would leave ListBoxB rowsource blank.
Then, using the ListBoxA AfterUpdate event, set the ListBoxB rowsource
according to what was selected in ListBoxA:

If Me.ListBoxA = "All" Then
Me.ListBoxB.Rowsource = "Select TableName.Products from TableName"
Else
Me.ListBoxB.Rowsource = "Select TableName.Products from TableName
Where TableName.CompanyID = "& Me.ListBoxA
End If

The bound column of ListBoxA should be the CompanyID field, not the
Company Name.

If you are using the company Name, then the syntax should be:

"Select ..... Where TableName.CompanyName = """ & Me.ListBoxA & """"

No need for that Query. No need to requery.
 
Hi Drew

It would have been useful for you to post the VB code you already have, and
also the RowSources of your two listboxes.

However, the principle here is that you use the AfterUpdate event of
ListBoxA to change the RowSource of ListBoxB as appropriate.

Let's say you have a query (qryProductsForListBoxB) which is the unfiltered
RowSource for ListBoxB. Your AfterUpdate event procedure for ListBoxA would
need to do something like this:

If ListBoxA.Value = <whatever value signifies "All"> Then
ListBoxB.RowSource = "qryProductsForListBoxB"
Else
ListBoxB.RowSource = "Select * from qryProductsForListBoxB " _
& "where ProductCompany=" & ListBoxA.Value
End If
 
Back
Top