How to Use Combo result as filter

[alexboitz]

I have "Trades" table with 3 fields - Trade, Trade ref, Company ref
The "Companys" Table is linked to it by the Company ref field.
There are many Companys for each Trade.
I combo query to get a pick list with unique values of Trade...which only
seems to work if there is only the one field used.
I try to use the selected Trade as the filter in a query to show only
Companys with this trade, but it always asks for input before running the
first query.
It uses my attempt at a selection filter as the prompt
viz. - [Forms]![ParamForm]![Comboboxname]

This question seems to crop up often but i can't find a response with clear
instructions that work. Your response to fredg on 2/28/2006 seems very
helpful and i tried to use that format, but it produces the input prompt as
above.

The Set Column Width property to 0";1" in that response is confusing and
seems to stop the query from working, so I omitted it.

I would be very grateful if you could help.

 

[Jeanette Cunningham]

Hi alexboitz,
Make the combo based on the Trades table.
Assuming that Trade ref is the primary key, create a saved query based on
the Trades table.
Create a new query and add the Trades table.
Drag both Trade ref and Trade onto the grid.
Put Trade ref in the first column, Trade in the second column.
In the Sort row put Ascending under Trade.
Click the query in the big space above the grid and on the property dialog
choose Yes for Unique Values.
Save the query with a name such as qrycboTrade.

Use this query as the row source for the combo.
The combo needs 2 columns
Column widths are 0";1"
On the data tab, the bound column is 1
Set the limit to list property to Yes
It's often helpful to set the AutoCorrect to No (Other tab).
The combo will be unbound ( nothing in its control source).


The form will be based on the Trades table, using all 3 fields.

Use code like this to make the combo work-->

Private Sub TheComboName_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.TheComboName) Then

'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[KeyID] = " & Me.TheComboName
'rs.FindFirst "[KeyID] = """ & Me.TheComboName & """" 'text field

If rs.NoMatch Then
MsgBox "Not found"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub


Note: replace TheComboName and KeyID with your names.
[KeyID] will be the bound column of the combo, the field you called Trade
ref.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[alexboitz]

Thanks for the quick response Jeanette..will try that now

Hi alexboitz,
Make the combo based on the Trades table.
Assuming that Trade ref is the primary key, create a saved query based on
the Trades table.
Create a new query and add the Trades table.
Drag both Trade ref and Trade onto the grid.
Put Trade ref in the first column, Trade in the second column.
In the Sort row put Ascending under Trade.
Click the query in the big space above the grid and on the property dialog
choose Yes for Unique Values.
Save the query with a name such as qrycboTrade.

Use this query as the row source for the combo.
The combo needs 2 columns
Column widths are 0";1"
On the data tab, the bound column is 1
Set the limit to list property to Yes
It's often helpful to set the AutoCorrect to No (Other tab).
The combo will be unbound ( nothing in its control source).


The form will be based on the Trades table, using all 3 fields.

Use code like this to make the combo work-->

Private Sub TheComboName_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.TheComboName) Then

'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[KeyID] = " & Me.TheComboName
'rs.FindFirst "[KeyID] = """ & Me.TheComboName & """" 'text field

If rs.NoMatch Then
MsgBox "Not found"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub


Note: replace TheComboName and KeyID with your names.
[KeyID] will be the bound column of the combo, the field you called Trade
ref.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have "Trades" table with 3 fields - Trade, Trade ref, Company ref
The "Companys" Table is linked to it by the Company ref field.
There are many Companys for each Trade.
I combo query to get a pick list with unique values of Trade...which only
seems to work if there is only the one field used.
I try to use the selected Trade as the filter in a query to show only
Companys with this trade, but it always asks for input before running the
first query.
It uses my attempt at a selection filter as the prompt
viz. - [Forms]![ParamForm]![Comboboxname]

This question seems to crop up often but i can't find a response with
clear
instructions that work. Your response to fredg on 2/28/2006 seems very
helpful and i tried to use that format, but it produces the input prompt
as
above.

The Set Column Width property to 0";1" in that response is confusing and
seems to stop the query from working, so I omitted it.

I would be very grateful if you could help.

 

[alexboitz]

Hi again Jeanette,

I got the first part to work ok to get a 'Trade" selected from a combo
utilising a a query of the Trades table resulting in unique occurences of
"Trades" and "Trade ref", so either of these could be a key I think.

After that I'm a bit lost.

What Form?

What combo?

The code looks af it returns one record...is this the case?

I'm looking at my selected Trade on screen.
I know that I want to use it as a filter to find all records in the Trades
table containing that Trade.
I then want to link those records to the Companys table to display the
Companys identified by the Linked "company Ref " field.

My problem is basically that I don't really know what the combo field is
called, or how to allocate it to a a filter, or how to assign it to variable
which is then used in the filter.

Is it just called Form!ComboName? ( I called it qrycboTradeList)

To allocate it to a filter is it just - WHERE Trade=Form!ComboName ?

Sorry to be so much trouble.

Thanks for the quick response Jeanette..will try that now

Hi alexboitz,
Make the combo based on the Trades table.
Assuming that Trade ref is the primary key, create a saved query based on
the Trades table.
Create a new query and add the Trades table.
Drag both Trade ref and Trade onto the grid.
Put Trade ref in the first column, Trade in the second column.
In the Sort row put Ascending under Trade.
Click the query in the big space above the grid and on the property dialog
choose Yes for Unique Values.
Save the query with a name such as qrycboTrade.

Use this query as the row source for the combo.
The combo needs 2 columns
Column widths are 0";1"
On the data tab, the bound column is 1
Set the limit to list property to Yes
It's often helpful to set the AutoCorrect to No (Other tab).
The combo will be unbound ( nothing in its control source).


The form will be based on the Trades table, using all 3 fields.

Use code like this to make the combo work-->

Private Sub TheComboName_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.TheComboName) Then

'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[KeyID] = " & Me.TheComboName
'rs.FindFirst "[KeyID] = """ & Me.TheComboName & """" 'text field

If rs.NoMatch Then
MsgBox "Not found"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub


Note: replace TheComboName and KeyID with your names.
[KeyID] will be the bound column of the combo, the field you called Trade
ref.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have "Trades" table with 3 fields - Trade, Trade ref, Company ref
The "Companys" Table is linked to it by the Company ref field.
There are many Companys for each Trade.
I combo query to get a pick list with unique values of Trade...which only
seems to work if there is only the one field used.
I try to use the selected Trade as the filter in a query to show only
Companys with this trade, but it always asks for input before running the
first query.
It uses my attempt at a selection filter as the prompt
viz. - [Forms]![ParamForm]![Comboboxname]

This question seems to crop up often but i can't find a response with
clear
instructions that work. Your response to fredg on 2/28/2006 seems very
helpful and i tried to use that format, but it produces the input prompt
as
above.

The Set Column Width property to 0";1" in that response is confusing and
seems to stop the query from working, so I omitted it.

I would be very grateful if you could help.

 

[Jeanette Cunningham]

I have built a sample database based on your tables.
The find first code I posted, works best with a form and subform setup.
We can just use a filter when there is a single form - as you have.

By the way, access does not like spaces in table names and field names ( it
gets confused). If you are just starting this database, now would be a good
time to make both the table names and the field names without spaces. If you
stay with the spaces in the names, everytime you write code, you must put
square brackets around the field names.
Example field names TradeRef, CompanyRef


To find all companies with a particular trade - make a form based on the
company table.

The combo goes in the header of the form and is unbound as explained
previously.
You can get a list of the trades using a query based on the Trade table.
Make it the record source of the combo.
I named the combo as cboFilterTrade

Use the following filter code-->
-------------------
Private Sub cboFilterTrade_AfterUpdate()
On Error Resume Next

If Not IsNull(Me.cboFilterTrade) Then
Me.Filter = "[TradeRef] = " & Me.cboFilterTrade
Me.FilterOn = True
Else
Me.FilterOn = False
End If


End Sub
-----------------

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi again Jeanette,

I got the first part to work ok to get a 'Trade" selected from a combo
utilising a a query of the Trades table resulting in unique occurences of
"Trades" and "Trade ref", so either of these could be a key I think.

After that I'm a bit lost.

What Form?

What combo?

The code looks af it returns one record...is this the case?

I'm looking at my selected Trade on screen.
I know that I want to use it as a filter to find all records in the
Trades
table containing that Trade.
I then want to link those records to the Companys table to display the
Companys identified by the Linked "company Ref " field.

My problem is basically that I don't really know what the combo field is
called, or how to allocate it to a a filter, or how to assign it to
variable
which is then used in the filter.

Is it just called Form!ComboName? ( I called it qrycboTradeList)

To allocate it to a filter is it just - WHERE Trade=Form!ComboName ?

Sorry to be so much trouble.

Thanks for the quick response Jeanette..will try that now

Hi alexboitz,
Make the combo based on the Trades table.
Assuming that Trade ref is the primary key, create a saved query based
on
the Trades table.
Create a new query and add the Trades table.
Drag both Trade ref and Trade onto the grid.
Put Trade ref in the first column, Trade in the second column.
In the Sort row put Ascending under Trade.
Click the query in the big space above the grid and on the property
dialog
choose Yes for Unique Values.
Save the query with a name such as qrycboTrade.

Use this query as the row source for the combo.
The combo needs 2 columns
Column widths are 0";1"
On the data tab, the bound column is 1
Set the limit to list property to Yes
It's often helpful to set the AutoCorrect to No (Other tab).
The combo will be unbound ( nothing in its control source).


The form will be based on the Trades table, using all 3 fields.

Use code like this to make the combo work-->

Private Sub TheComboName_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.TheComboName) Then

'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[KeyID] = " & Me.TheComboName
'rs.FindFirst "[KeyID] = """ & Me.TheComboName & """" 'text
field

If rs.NoMatch Then
MsgBox "Not found"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub


Note: replace TheComboName and KeyID with your names.
[KeyID] will be the bound column of the combo, the field you called
Trade
ref.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have "Trades" table with 3 fields - Trade, Trade ref, Company ref
The "Companys" Table is linked to it by the Company ref field.
There are many Companys for each Trade.
I combo query to get a pick list with unique values of Trade...which
only
seems to work if there is only the one field used.
I try to use the selected Trade as the filter in a query to show
only
Companys with this trade, but it always asks for input before
running the
first query.
It uses my attempt at a selection filter as the prompt
viz. - [Forms]![ParamForm]![Comboboxname]

This question seems to crop up often but i can't find a response with
clear
instructions that work. Your response to fredg on 2/28/2006 seems
very
helpful and i tried to use that format, but it produces the input
prompt
as
above.

The Set Column Width property to 0";1" in that response is confusing
and
seems to stop the query from working, so I omitted it.

I would be very grateful if you could help.

 

[alexboitz]

Hi yet again Jeanette,

As you might guess I am a total newbie at this.

I am trying to improve the old "Supplier Database" at work.
It works fine except that it ASKS FOR INPUT of the "Trade" before finding
the relevant Companies...which it does fine thereafter.
That feature really put people off using it since they had to know the exact
"Trade" to type in.
I have managed to produce a "Pick List" in a combo...(in the Header of a
Form as you suggested.)
It works fine.
The code you provided doesn't seem to do anything "afterupdate" although i
managed to get a macro to work .."afterupdate".

If it did work ...What happens after that code runs? Should there be code to
start a query that the filter applies to?


So I am still struggling with the basic problem of getting cboFilterTrade
transferred into a query filter that only allows records in the "Trade"
table that contain the selected Trade, so that these are linked via the
Company Ref field that occurs in the "Trade" and "Company" tables. This is
the only common field.

You said "To find all companies........make a form based on the company
table."
I don't know what that means.

I tried to do it by putting some of the company fields in a query list on
the form below the header (which contained the cboFilterTrade combo) but it
then took about 5 minute for the form to load with a list of the fields all
populated with the same record data.

I have built a sample database based on your tables.
The find first code I posted, works best with a form and subform setup.
We can just use a filter when there is a single form - as you have.

By the way, access does not like spaces in table names and field names ( it
gets confused). If you are just starting this database, now would be a good
time to make both the table names and the field names without spaces. If you
stay with the spaces in the names, everytime you write code, you must put
square brackets around the field names.
Example field names TradeRef, CompanyRef


To find all companies with a particular trade - make a form based on the
company table.

The combo goes in the header of the form and is unbound as explained
previously.
You can get a list of the trades using a query based on the Trade table.
Make it the record source of the combo.
I named the combo as cboFilterTrade

Use the following filter code-->
-------------------
Private Sub cboFilterTrade_AfterUpdate()
On Error Resume Next

If Not IsNull(Me.cboFilterTrade) Then
Me.Filter = "[TradeRef] = " & Me.cboFilterTrade
Me.FilterOn = True
Else
Me.FilterOn = False
End If


End Sub
-----------------

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi again Jeanette,

I got the first part to work ok to get a 'Trade" selected from a combo
utilising a a query of the Trades table resulting in unique occurences of
"Trades" and "Trade ref", so either of these could be a key I think.

After that I'm a bit lost.

What Form?

What combo?

The code looks af it returns one record...is this the case?

I'm looking at my selected Trade on screen.
I know that I want to use it as a filter to find all records in the
Trades
table containing that Trade.
I then want to link those records to the Companys table to display the
Companys identified by the Linked "company Ref " field.

My problem is basically that I don't really know what the combo field is
called, or how to allocate it to a a filter, or how to assign it to
variable
which is then used in the filter.

Is it just called Form!ComboName? ( I called it qrycboTradeList)

To allocate it to a filter is it just - WHERE Trade=Form!ComboName ?

Sorry to be so much trouble.

Thanks for the quick response Jeanette..will try that now

:

Hi alexboitz,
Make the combo based on the Trades table.
Assuming that Trade ref is the primary key, create a saved query based
on
the Trades table.
Create a new query and add the Trades table.
Drag both Trade ref and Trade onto the grid.
Put Trade ref in the first column, Trade in the second column.
In the Sort row put Ascending under Trade.
Click the query in the big space above the grid and on the property
dialog
choose Yes for Unique Values.
Save the query with a name such as qrycboTrade.

Use this query as the row source for the combo.
The combo needs 2 columns
Column widths are 0";1"
On the data tab, the bound column is 1
Set the limit to list property to Yes
It's often helpful to set the AutoCorrect to No (Other tab).
The combo will be unbound ( nothing in its control source).


The form will be based on the Trades table, using all 3 fields.

Use code like this to make the combo work-->

Private Sub TheComboName_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.TheComboName) Then

'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[KeyID] = " & Me.TheComboName
'rs.FindFirst "[KeyID] = """ & Me.TheComboName & """" 'text
field

If rs.NoMatch Then
MsgBox "Not found"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub


Note: replace TheComboName and KeyID with your names.
[KeyID] will be the bound column of the combo, the field you called
Trade
ref.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have "Trades" table with 3 fields - Trade, Trade ref, Company ref
The "Companys" Table is linked to it by the Company ref field.
There are many Companys for each Trade.
I combo query to get a pick list with unique values of Trade...which
only
seems to work if there is only the one field used.
I try to use the selected Trade as the filter in a query to show
only
Companys with this trade, but it always asks for input before
running the
first query.
It uses my attempt at a selection filter as the prompt
viz. - [Forms]![ParamForm]![Comboboxname]

This question seems to crop up often but i can't find a response with
clear
instructions that work. Your response to fredg on 2/28/2006 seems
very
helpful and i tried to use that format, but it produces the input
prompt
as
above.

The Set Column Width property to 0";1" in that response is confusing
and
seems to stop the query from working, so I omitted it.

I would be very grateful if you could help.

 

[Jeanette Cunningham]

If it worked, the form would show all the companies with the Trade shown in
the combo.
I made a working form yesterday in a new database. I am happy to email it to
you.
You can send me an email address to jj cc 14 at g mail dot com
Remove the spaces from the email address on the line above and change at and
dot to you-know-what. Don't put your email address in this newsgroup -
spammers and bots will find it.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi yet again Jeanette,

As you might guess I am a total newbie at this.

I am trying to improve the old "Supplier Database" at work.
It works fine except that it ASKS FOR INPUT of the "Trade" before finding
the relevant Companies...which it does fine thereafter.
That feature really put people off using it since they had to know the
exact
"Trade" to type in.
I have managed to produce a "Pick List" in a combo...(in the Header of a
Form as you suggested.)
It works fine.
The code you provided doesn't seem to do anything "afterupdate" although i
managed to get a macro to work .."afterupdate".

If it did work ...What happens after that code runs? Should there be code
to
start a query that the filter applies to?


So I am still struggling with the basic problem of getting cboFilterTrade
transferred into a query filter that only allows records in the "Trade"
table that contain the selected Trade, so that these are linked via the
Company Ref field that occurs in the "Trade" and "Company" tables. This is
the only common field.

You said "To find all companies........make a form based on the company
table."
I don't know what that means.

I tried to do it by putting some of the company fields in a query list
on
the form below the header (which contained the cboFilterTrade combo) but
it
then took about 5 minute for the form to load with a list of the fields
all
populated with the same record data.

I have built a sample database based on your tables.
The find first code I posted, works best with a form and subform setup.
We can just use a filter when there is a single form - as you have.

By the way, access does not like spaces in table names and field names
( it
gets confused). If you are just starting this database, now would be a
good
time to make both the table names and the field names without spaces. If
you
stay with the spaces in the names, everytime you write code, you must put
square brackets around the field names.
Example field names TradeRef, CompanyRef


To find all companies with a particular trade - make a form based on the
company table.

The combo goes in the header of the form and is unbound as explained
previously.
You can get a list of the trades using a query based on the Trade table.
Make it the record source of the combo.
I named the combo as cboFilterTrade

Use the following filter code-->
-------------------
Private Sub cboFilterTrade_AfterUpdate()
On Error Resume Next

If Not IsNull(Me.cboFilterTrade) Then
Me.Filter = "[TradeRef] = " & Me.cboFilterTrade
Me.FilterOn = True
Else
Me.FilterOn = False
End If


End Sub
-----------------

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi again Jeanette,

I got the first part to work ok to get a 'Trade" selected from a combo
utilising a a query of the Trades table resulting in unique occurences
of
"Trades" and "Trade ref", so either of these could be a key I think.

After that I'm a bit lost.

What Form?

What combo?

The code looks af it returns one record...is this the case?

I'm looking at my selected Trade on screen.
I know that I want to use it as a filter to find all records in the
Trades
table containing that Trade.
I then want to link those records to the Companys table to display the
Companys identified by the Linked "company Ref " field.

My problem is basically that I don't really know what the combo field
is
called, or how to allocate it to a a filter, or how to assign it to
variable
which is then used in the filter.

Is it just called Form!ComboName? ( I called it qrycboTradeList)

To allocate it to a filter is it just - WHERE Trade=Form!ComboName ?

Sorry to be so much trouble.

:

Thanks for the quick response Jeanette..will try that now

:

Hi alexboitz,
Make the combo based on the Trades table.
Assuming that Trade ref is the primary key, create a saved query
based
on
the Trades table.
Create a new query and add the Trades table.
Drag both Trade ref and Trade onto the grid.
Put Trade ref in the first column, Trade in the second column.
In the Sort row put Ascending under Trade.
Click the query in the big space above the grid and on the property
dialog
choose Yes for Unique Values.
Save the query with a name such as qrycboTrade.

Use this query as the row source for the combo.
The combo needs 2 columns
Column widths are 0";1"
On the data tab, the bound column is 1
Set the limit to list property to Yes
It's often helpful to set the AutoCorrect to No (Other tab).
The combo will be unbound ( nothing in its control source).


The form will be based on the Trades table, using all 3 fields.

Use code like this to make the combo work-->

Private Sub TheComboName_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.TheComboName) Then

'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[KeyID] = " & Me.TheComboName
'rs.FindFirst "[KeyID] = """ & Me.TheComboName & """" 'text
field

If rs.NoMatch Then
MsgBox "Not found"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub


Note: replace TheComboName and KeyID with your names.
[KeyID] will be the bound column of the combo, the field you called
Trade
ref.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have "Trades" table with 3 fields - Trade, Trade ref, Company ref
The "Companys" Table is linked to it by the Company ref field.
There are many Companys for each Trade.
I combo query to get a pick list with unique values of
Trade...which
only
seems to work if there is only the one field used.
I try to use the selected Trade as the filter in a query to show
only
Companys with this trade, but it always asks for input before
running the
first query.
It uses my attempt at a selection filter as the prompt
viz. - [Forms]![ParamForm]![Comboboxname]

This question seems to crop up often but i can't find a response
with
clear
instructions that work. Your response to fredg on 2/28/2006 seems
very
helpful and i tried to use that format, but it produces the input
prompt
as
above.

The Set Column Width property to 0";1" in that response is
confusing
and
seems to stop the query from working, so I omitted it.

I would be very grateful if you could help.