Filter Combo Box Source

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I have a form that has a button on it that opens another
form with a combo box. I need to set a filter on the combo
box list. There are two fields that are tied to the combo
box. Employee name and Section ID.

I need all of the employees in section id "AA" to be
visible and all of the others to be invisible.

-Chris
 
Reassign the RowSource of the combo, e.g.:

Forms![MyForm]![MyCombo].RowSource = "SELECT [employeeid],
[employeename] FROM [EmployeeTable] WHERE [section id] = 'AA' ORDER BY
[employeename];"
 
-----Original Message-----
Reassign the RowSource of the combo, e.g.:

Forms![MyForm]![MyCombo].RowSource = "SELECT [employeeid],
[employeename] FROM [EmployeeTable] WHERE [section id] = 'AA' ORDER BY
[employeename];"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form that has a button on it that opens another
form with a combo box. I need to set a filter on the combo
box list. There are two fields that are tied to the combo
box. Employee name and Section ID.

I need all of the employees in section id "AA" to be
visible and all of the others to be invisible.

-Chris
Click to expand...
Click to expand...
Good day Allen Browne,

I recently found your response to Chris, that seemed to
answer my problems. Except that what I'm trying to do is
have a TABed form. TAB I has a listbox listing
Countries/Continents/Number of coins in collection. Once
a country is pressed and the user clicks onto TAB II,
which has another listbox listing all the coins from that
country. Now the hard part. Once the user chooses one
coin, TAB III's subform is supposed to display all the
characteristics connected to that coin.
I have queries for each TAB. I haven't been able to find
the right SQL for the query which takes the 2 criterias
from 2 listboxes on 2 different TABs. One is the
countryID#, the other is the coinID#.

Any thoughts, or assistance, would be greatly appreciated,

Dave :~)
 
Hi Dave

The solution will depend on your data structure.
Presumably you have a table of countries, and related table of coins, and a
related (join?) table of coin characteristics.

If so, you should be able to nominate the list box in the LinkMasterFields
property of the subform control, and Access will take care of it from there.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

David Hubbard said:
-----Original Message-----
Reassign the RowSource of the combo, e.g.:

Forms![MyForm]![MyCombo].RowSource = "SELECT [employeeid],
[employeename] FROM [EmployeeTable] WHERE [section id] = 'AA' ORDER BY
[employeename];"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form that has a button on it that opens another
form with a combo box. I need to set a filter on the combo
box list. There are two fields that are tied to the combo
box. Employee name and Section ID.

I need all of the employees in section id "AA" to be
visible and all of the others to be invisible.

-Chris
Click to expand...
Click to expand...
Good day Allen Browne,

I recently found your response to Chris, that seemed to
answer my problems. Except that what I'm trying to do is
have a TABed form. TAB I has a listbox listing
Countries/Continents/Number of coins in collection. Once
a country is pressed and the user clicks onto TAB II,
which has another listbox listing all the coins from that
country. Now the hard part. Once the user chooses one
coin, TAB III's subform is supposed to display all the
characteristics connected to that coin.
I have queries for each TAB. I haven't been able to find
the right SQL for the query which takes the 2 criterias
from 2 listboxes on 2 different TABs. One is the
countryID#, the other is the coinID#.

Any thoughts, or assistance, would be greatly appreciated,

Dave :~)
Click to expand...
 
-----Original Message-----
Hi Dave

The solution will depend on your data structure.
Presumably you have a table of countries, and related table of coins, and a
related (join?) table of coin characteristics.

If so, you should be able to nominate the list box in the LinkMasterFields
property of the subform control, and Access will take care of it from there.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

David Hubbard said:
-----Original Message-----
Reassign the RowSource of the combo, e.g.:

Forms![MyForm]![MyCombo].RowSource = "SELECT [employeeid],
[employeename] FROM [EmployeeTable] WHERE [section id] = 'AA' ORDER BY
[employeename];"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I have a form that has a button on it that opens another
form with a combo box. I need to set a filter on the combo
box list. There are two fields that are tied to the combo
box. Employee name and Section ID.

I need all of the employees in section id "AA" to be
visible and all of the others to be invisible.

-Chris
Click to expand...
Good day Allen Browne,

I recently found your response to Chris, that seemed to
answer my problems. Except that what I'm trying to do is
have a TABed form. TAB I has a listbox listing
Countries/Continents/Number of coins in collection. Once
a country is pressed and the user clicks onto TAB II,
which has another listbox listing all the coins from that
country. Now the hard part. Once the user chooses one
coin, TAB III's subform is supposed to display all the
characteristics connected to that coin.
I have queries for each TAB. I haven't been able to find
the right SQL for the query which takes the 2 criterias
from 2 listboxes on 2 different TABs. One is the
countryID#, the other is the coinID#.

Any thoughts, or assistance, would be greatly appreciated,

Dave :~)
Click to expand...
Click to expand...

Good day Allen,

Much thanks re your post. I at first was telling access
to get the data from a control, where I should of been
telling access to look inside a list box. I'll get back
to you if any problems arise.

Dave :~)
 
-----Original Message-----
Hi Dave

The solution will depend on your data structure.
Presumably you have a table of countries, and related table of coins, and a
related (join?) table of coin characteristics.

If so, you should be able to nominate the list box in the LinkMasterFields
property of the subform control, and Access will take care of it from there.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

David Hubbard said:
-----Original Message-----
Reassign the RowSource of the combo, e.g.:

Forms![MyForm]![MyCombo].RowSource = "SELECT [employeeid],
[employeename] FROM [EmployeeTable] WHERE [section id] = 'AA' ORDER BY
[employeename];"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I have a form that has a button on it that opens another
form with a combo box. I need to set a filter on the combo
box list. There are two fields that are tied to the combo
box. Employee name and Section ID.

I need all of the employees in section id "AA" to be
visible and all of the others to be invisible.

-Chris
Click to expand...
Good day Allen Browne,

I recently found your response to Chris, that seemed to
answer my problems. Except that what I'm trying to do is
have a TABed form. TAB I has a listbox listing
Countries/Continents/Number of coins in collection. Once
a country is pressed and the user clicks onto TAB II,
which has another listbox listing all the coins from that
country. Now the hard part. Once the user chooses one
coin, TAB III's subform is supposed to display all the
characteristics connected to that coin.
I have queries for each TAB. I haven't been able to find
the right SQL for the query which takes the 2 criterias
from 2 listboxes on 2 different TABs. One is the
countryID#, the other is the coinID#.

Any thoughts, or assistance, would be greatly appreciated,

Dave :~)
Click to expand...


.
Click to expand...
Hi there Allen,

I thought your last post would have worked, except once I
( nominate the list box in the LinkMasterFiels property
Click to expand...
of the subform control. )
like you suggested nothing seemed to change.

I'll give you some specs:
Table 1 has: countryID# / country name / continent name.
Table 2 has: countryID#/ coinID#/ and all the other
characteristics.
I have the countryID# being the Primary Key being similar
to both tables.

So in TAB I I'm trying, with a list box, to list the
countries to be picked and used as one of the criterias to
be used in TAB III.
In TAB II, also with a list box, having the country from
TAB I be the criteria for filtering out its' coins.

Also in TAB II the user must pick one of the coins
listed. After it is picked TAB III is chosen to show all
the characteristics for such coin.

I have a query, when run outside a subform, will ask for
which countryID# and which coinID#, before filtering with
these 2 criterias. Access will use 2 criterias fine this
way but won't use 2 from list boxes. Confusing?

Any other thoughts as to other directions to use 2
seperate criterias from 2 different list boxes?

Dave :~()
 
Okay.

Tab 1 has list box named (say) "lstCountry", with CountryID#.

Tab 2 has listbox named (say) "lstCoin, with CoinID#.

Tab 3 has a subform bound to Table2.
Set its LinkMasterFields property to
lstCoin
and LinkChildFields to
[CoinID#]
This takes care of the link between 2 and 3.

Now you need to use the AfterUpdate event procedure of lstCountry to limit
the RowSource of lstCoin:

Private Sub lstCountry_AfterUpdate
Dim strSQL As String
strSQL = "SELECT [CoinID#] FROM Table2 WHERE [CountryID#] = " &
lstCountry & ";"
Me.lstCoin.RowSource = strSQL
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


David Hubbard said:
-----Original Message-----
Hi Dave

The solution will depend on your data structure.
Presumably you have a table of countries, and related table of coins, and a
related (join?) table of coin characteristics.

If so, you should be able to nominate the list box in the LinkMasterFields
property of the subform control, and Access will take care of it from there.

David Hubbard said:
-----Original Message-----
Reassign the RowSource of the combo, e.g.:

Forms![MyForm]![MyCombo].RowSource = "SELECT
[employeeid],
[employeename] FROM [EmployeeTable] WHERE [section id]
= 'AA' ORDER BY
[employeename];"

Good day Allen Browne,

I recently found your response to Chris, that seemed to
answer my problems. Except that what I'm trying to do is
have a TABed form. TAB I has a listbox listing
Countries/Continents/Number of coins in collection. Once
a country is pressed and the user clicks onto TAB II,
which has another listbox listing all the coins from that
country. Now the hard part. Once the user chooses one
coin, TAB III's subform is supposed to display all the
characteristics connected to that coin.
I have queries for each TAB. I haven't been able to find
the right SQL for the query which takes the 2 criterias
from 2 listboxes on 2 different TABs. One is the
countryID#, the other is the coinID#.

Any thoughts, or assistance, would be greatly appreciated,

Dave :~)
Click to expand...


.
Click to expand...
Hi there Allen,

I thought your last post would have worked, except once I
( nominate the list box in the LinkMasterFiels property
Click to expand...
of the subform control. )
like you suggested nothing seemed to change.

I'll give you some specs:
Table 1 has: countryID# / country name / continent name.
Table 2 has: countryID#/ coinID#/ and all the other
characteristics.
I have the countryID# being the Primary Key being similar
to both tables.

So in TAB I I'm trying, with a list box, to list the
countries to be picked and used as one of the criterias to
be used in TAB III.
In TAB II, also with a list box, having the country from
TAB I be the criteria for filtering out its' coins.

Also in TAB II the user must pick one of the coins
listed. After it is picked TAB III is chosen to show all
the characteristics for such coin.

I have a query, when run outside a subform, will ask for
which countryID# and which coinID#, before filtering with
these 2 criterias. Access will use 2 criterias fine this
way but won't use 2 from list boxes. Confusing?

Any other thoughts as to other directions to use 2
seperate criterias from 2 different list boxes?
Click to expand...
 
-----Original Message-----
Okay.

Tab 1 has list box named (say) "lstCountry", with CountryID#.

Tab 2 has listbox named (say) "lstCoin, with CoinID#.

Tab 3 has a subform bound to Table2.
Set its LinkMasterFields property to
lstCoin
and LinkChildFields to
[CoinID#]
This takes care of the link between 2 and 3.

Now you need to use the AfterUpdate event procedure of lstCountry to limit
the RowSource of lstCoin:

Private Sub lstCountry_AfterUpdate
Dim strSQL As String
strSQL = "SELECT [CoinID#] FROM Table2 WHERE [CountryID#] = " &
lstCountry & ";"
Me.lstCoin.RowSource = strSQL
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


David Hubbard said:
-----Original Message-----
Hi Dave

The solution will depend on your data structure.
Presumably you have a table of countries, and related table of coins, and a
related (join?) table of coin characteristics.

If so, you should be able to nominate the list box in
Click to expand...
the
LinkMasterFields
property of the subform control, and Access will take care of it from there.


-----Original Message-----
Reassign the RowSource of the combo, e.g.:

Forms![MyForm]![MyCombo].RowSource = "SELECT
[employeeid],
[employeename] FROM [EmployeeTable] WHERE [section id]
= 'AA' ORDER BY
[employeename];"

Good day Allen Browne,

I recently found your response to Chris, that seemed to
answer my problems. Except that what I'm trying to
Click to expand...
do
is
have a TABed form. TAB I has a listbox listing
Countries/Continents/Number of coins in collection. Once
a country is pressed and the user clicks onto TAB II,
which has another listbox listing all the coins from that
country. Now the hard part. Once the user chooses one
coin, TAB III's subform is supposed to display all the
characteristics connected to that coin.
I have queries for each TAB. I haven't been able to find
the right SQL for the query which takes the 2 criterias
from 2 listboxes on 2 different TABs. One is the
countryID#, the other is the coinID#.

Any thoughts, or assistance, would be greatly appreciated,

Dave :~)


.
Click to expand...
Hi there Allen,

I thought your last post would have worked, except once I
( nominate the list box in the LinkMasterFiels
Click to expand...
property
of the subform control. )
like you suggested nothing seemed to change.

I'll give you some specs:
Table 1 has: countryID# / country name / continent name.
Table 2 has: countryID#/ coinID#/ and all the other
characteristics.
I have the countryID# being the Primary Key being similar
to both tables.

So in TAB I I'm trying, with a list box, to list the
countries to be picked and used as one of the criterias to
be used in TAB III.
In TAB II, also with a list box, having the country from
TAB I be the criteria for filtering out its' coins.

Also in TAB II the user must pick one of the coins
listed. After it is picked TAB III is chosen to show all
the characteristics for such coin.

I have a query, when run outside a subform, will ask for
which countryID# and which coinID#, before filtering with
these 2 criterias. Access will use 2 criterias fine this
way but won't use 2 from list boxes. Confusing?

Any other thoughts as to other directions to use 2
seperate criterias from 2 different list boxes?
Click to expand...


.
Click to expand...
Hi Allen,

Thanks for your last post, except that in my Access 2002
there didn't seem to be any change.
I read somewhere about a sub query. Except that I can't
find any more inforation on it. Would this be what I
could use?
I was thinking that TAB II uses a criteria from the
country I pick from TAB I. Is it possible to run a query
having a criteria from TAB II's list box, related to its'
own listbox? Since the TAB II's list box has the coins
which I want to choose one for its' characteristics.

Any thoughts would be appreciated,

Dave
 
Not sure what else to suggest, David.

David Hubbard said:
-----Original Message-----
Okay.

Tab 1 has list box named (say) "lstCountry", with CountryID#.

Tab 2 has listbox named (say) "lstCoin, with CoinID#.

Tab 3 has a subform bound to Table2.
Set its LinkMasterFields property to
lstCoin
and LinkChildFields to
[CoinID#]
This takes care of the link between 2 and 3.

Now you need to use the AfterUpdate event procedure of lstCountry to limit
the RowSource of lstCoin:

Private Sub lstCountry_AfterUpdate
Dim strSQL As String
strSQL = "SELECT [CoinID#] FROM Table2 WHERE [CountryID#] = " &
lstCountry & ";"
Me.lstCoin.RowSource = strSQL
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


David Hubbard said:
-----Original Message-----
Hi Dave

The solution will depend on your data structure.
Presumably you have a table of countries, and related
table of coins, and a
related (join?) table of coin characteristics.

If so, you should be able to nominate the list box in the
LinkMasterFields
property of the subform control, and Access will take
care of it from there.


-----Original Message-----
Reassign the RowSource of the combo, e.g.:

Forms![MyForm]![MyCombo].RowSource = "SELECT
[employeeid],
[employeename] FROM [EmployeeTable] WHERE [section id]
= 'AA' ORDER BY
[employeename];"

Good day Allen Browne,

I recently found your response to Chris, that seemed to
answer my problems. Except that what I'm trying to do
is
have a TABed form. TAB I has a listbox listing
Countries/Continents/Number of coins in collection.
Once
a country is pressed and the user clicks onto TAB II,
which has another listbox listing all the coins from
that
country. Now the hard part. Once the user chooses one
coin, TAB III's subform is supposed to display all the
characteristics connected to that coin.
I have queries for each TAB. I haven't been able to
find
the right SQL for the query which takes the 2 criterias
from 2 listboxes on 2 different TABs. One is the
countryID#, the other is the coinID#.

Any thoughts, or assistance, would be greatly
appreciated,

Dave :~)


.
Hi there Allen,

I thought your last post would have worked, except once I
( nominate the list box in the LinkMasterFiels property
of the subform control. )
like you suggested nothing seemed to change.

I'll give you some specs:
Table 1 has: countryID# / country name / continent name.
Table 2 has: countryID#/ coinID#/ and all the other
characteristics.
I have the countryID# being the Primary Key being similar
to both tables.

So in TAB I I'm trying, with a list box, to list the
countries to be picked and used as one of the criterias to
be used in TAB III.
In TAB II, also with a list box, having the country from
TAB I be the criteria for filtering out its' coins.

Also in TAB II the user must pick one of the coins
listed. After it is picked TAB III is chosen to show all
the characteristics for such coin.

I have a query, when run outside a subform, will ask for
which countryID# and which coinID#, before filtering with
these 2 criterias. Access will use 2 criterias fine this
way but won't use 2 from list boxes. Confusing?

Any other thoughts as to other directions to use 2
seperate criterias from 2 different list boxes?
Click to expand...


.
Click to expand...
Hi Allen,

Thanks for your last post, except that in my Access 2002
there didn't seem to be any change.
I read somewhere about a sub query. Except that I can't
find any more inforation on it. Would this be what I
could use?
I was thinking that TAB II uses a criteria from the
country I pick from TAB I. Is it possible to run a query
having a criteria from TAB II's list box, related to its'
own listbox? Since the TAB II's list box has the coins
which I want to choose one for its' characteristics.

Any thoughts would be appreciated,

Dave
Click to expand...
 
Back
Top