On form, how to filter List Box based on Combo Box criteria?

[Guest]

Hi All,
…browsed on this great forum but couldn’t really find what I needed.
On the Form Header I have a combo box with names, then on Detail section I
have a list box with names, addresses etc….(this List box and Form are both
being fed by a the same Query)
How do I filter this list box based on the criteria given on the Combo Box?

Thank you for any help provided!
Adnan

 

[JK]

Adnan,

Assuming that your combo box is bound to a sting containg the condition"

Private Sub YrCombo_AfterUpdate()

Me.yrTextBox.RowSource = "SELECT [Field1],[Field2]. ... etc
FROM [YrQuery] WHERE " & Me.YrCombo
' the Row source is a string

End Sub

Regards/JK

 

[Van T. Dinh]

While The Access Web article:

http://www.mvps.org/access/forms/frm0028.htm

refers to 2 ComboBoxes (cascade), the method also works for ComboBox
filtering the "following" ListBox.

 

[Guest]

JK, I appreciate your quick response,
I tried your code and it doesn’t seam to work. I think I’m missing something
here. Let me try again being a little more clear:

My form name is…………….frmCosts
My combo box name is…….cmbBldgs
My list box name is………….lstBldgs
My query name is…………...qryForListBox

I need the Cambo box (cmbBldgs) to filter my List Box (lstBldgs) after Combo
Box’s update. The List Box (lstBldgs) feeds from Query (qryForListBox) and
has these fields: WRNumber, POC and DescriptionOfWork

Again, thank you JK!
Adnan

--
Please post all your inquiries on this community so we can all benefit -
Thank you!

Adnan,

Assuming that your combo box is bound to a sting containg the condition"

Private Sub YrCombo_AfterUpdate()

Me.yrTextBox.RowSource = "SELECT [Field1],[Field2]. ... etc
FROM [YrQuery] WHERE " & Me.YrCombo
' the Row source is a string

End Sub

Regards/JK

 

[JK]

Andan,

Go to both your combo and list boxes, copy their Row Source from their
respective Property sheets and paste them here. (they both start with the
word "SELECT")

Also tell me, for each of them, the bound column (a number) and which value
(field name) in your combo you want filter on in your list box. and how
(e.g. =,>=,< etc )

Than I will be able to give you the *exact* syntax.

Regards/JK

 

[Guest]

This is from Combo Box (cmbBldgs):
SELECT tblBldgs.Bldg FROM tblBldgs ORDER BY [Bldg];

And this is from the List Box (lstBldgs):
SELECT tblWorkServiceOrders.Id, tblWorkServiceOrders.WRNumber,
tblWorkServiceOrders.POC, tblWorkServiceOrders.DescriptionOfRequirements FROM
tblWorkServiceOrders;

Thank you JK!
Adnan

 

[Guest]

Van,
It seams a little hard for me to implement this. I’ll keep trying…

I appreciate your help!
Adnan

--
Please post all your inquiries on this community so we can all benefit -
Thank you!

While The Access Web article:

http://www.mvps.org/access/forms/frm0028.htm

refers to 2 ComboBoxes (cascade), the method also works for ComboBox
filtering the "following" ListBox.

 

[JK]

Adnan,

You must have a common field in both the combo and the list boxes. In order
for it to work you need to tell Access how to filter the list box depending
on what is in the combo. The usual way is :

cmbBuliding:
SELECT tblBldgs.Bldg, tblBldgs.[SomeField] FROM tblBldgs ORDER BY [Bldg];

SELECT tblWorkServiceOrders.Id, tblWorkServiceOrders.WRNumber,
tblWorkServiceOrders.POC,
tblWorkServiceOrders.DescriptionOfRequirements,tblWorkServiceOrders.[SomeField],
FROM tblWorkServiceOrders

Only then we can limit the List box to show all the records that [someField]
in the list box = [SomeFiled] in the combo.

However if you want to use the Bldg field in the combo you have to tell how
to restrict it in list box e.g.

tblBldgs.Bldg= what in the list box (or < or >= or whatever,)

Regards/JK

This is from Combo Box (cmbBldgs):
SELECT tblBldgs.Bldg FROM tblBldgs ORDER BY [Bldg];

And this is from the List Box (lstBldgs):
SELECT tblWorkServiceOrders.Id, tblWorkServiceOrders.WRNumber,
tblWorkServiceOrders.POC, tblWorkServiceOrders.DescriptionOfRequirements
FROM
tblWorkServiceOrders;

Thank you JK!
Adnan
--
Please post all your inquiries on this community so we can all benefit -
Thank you!

Andan,

Go to both your combo and list boxes, copy their Row Source from their
respective Property sheets and paste them here. (they both start with the
word "SELECT")

Also tell me, for each of them, the bound column (a number) and which
value
(field name) in your combo you want filter on in your list box. and how
(e.g. =,>=,< etc )

Than I will be able to give you the *exact* syntax.

Regards/JK

 

[Guest]

JK,
I don’t seam to implement this yet…
How about a create a cmbBldg that have as Row Source tblBldgs? (tblBldgs is
a table that has all the number I need).
Now, could I filter lstBldgs with with this cmbBldgs box
lstBldgs Row Source is:
SELECT tblWorkServiceOrders.Id, tblWorkServiceOrders.[Bldg#],
tblWorkServiceOrders.WRNumber, tblWorkServiceOrders.POC,
tblWorkServiceOrders.DescriptionOfRequirements FROM tblWorkServiceOrders;

I appreciate your efforts JK!
Adnan

--
Please post all your inquiries on this community so we can all benefit -
Thank you!

Adnan,

You must have a common field in both the combo and the list boxes. In order
for it to work you need to tell Access how to filter the list box depending
on what is in the combo. The usual way is :

cmbBuliding:
SELECT tblBldgs.Bldg, tblBldgs.[SomeField] FROM tblBldgs ORDER BY [Bldg];

SELECT tblWorkServiceOrders.Id, tblWorkServiceOrders.WRNumber,
tblWorkServiceOrders.POC,
tblWorkServiceOrders.DescriptionOfRequirements,tblWorkServiceOrders.[SomeField],
FROM tblWorkServiceOrders

Only then we can limit the List box to show all the records that [someField]
in the list box = [SomeFiled] in the combo.

However if you want to use the Bldg field in the combo you have to tell how
to restrict it in list box e.g.

tblBldgs.Bldg= what in the list box (or < or >= or whatever,)

Regards/JK

This is from Combo Box (cmbBldgs):
SELECT tblBldgs.Bldg FROM tblBldgs ORDER BY [Bldg];

And this is from the List Box (lstBldgs):
SELECT tblWorkServiceOrders.Id, tblWorkServiceOrders.WRNumber,
tblWorkServiceOrders.POC, tblWorkServiceOrders.DescriptionOfRequirements
FROM
tblWorkServiceOrders;

Thank you JK!
Adnan
--
Please post all your inquiries on this community so we can all benefit -
Thank you!

Andan,

Go to both your combo and list boxes, copy their Row Source from their
respective Property sheets and paste them here. (they both start with the
word "SELECT")

Also tell me, for each of them, the bound column (a number) and which
value
(field name) in your combo you want filter on in your list box. and how
(e.g. =,>=,< etc )

Than I will be able to give you the *exact* syntax.

Regards/JK





JK, I appreciate your quick response,
I tried your code and it doesn't seam to work. I think I'm missing
something
here. Let me try again being a little more clear:

My form name is......frmCosts
My combo box name is...cmbBldgs
My list box name is.....lstBldgs
My query name is.......qryForListBox

I need the Cambo box (cmbBldgs) to filter my List Box (lstBldgs) after
Combo
Box's update. The List Box (lstBldgs) feeds from Query (qryForListBox)
and
has these fields: WRNumber, POC and DescriptionOfWork

Again, thank you JK!
Adnan

 

[JK]

Adnan

Ceate a combo, cmbBldg with the number you need, make that filed as Bound
coloumn. If for example, you want the list box to show you only the records
where tblWorkServiceOrders.[Bldg#]= The result of the combo. you need to add
an expression in AfterUpdate events of the Combo *AND* in the OnCurrent of
the *Form* , use Event Procedures.

The expression is:

Me.lstBldgs.RowSouce="SELECT tblWorkServiceOrders.Id,
tblWorkServiceOrders.[Bldg#],
tblWorkServiceOrders.WRNumber, tblWorkServiceOrders.POC,
tblWorkServiceOrders.DescriptionOfRequirements FROM tblWorkServiceOrders
WHERE
tblWorkServiceOrders.[Bldg#]=" & Me.cmbBldg

in Other words:
Me.lstBldgs.RowSource=" paste here what you have now in row source AND add:
WHERE tblWorkServiceOrders.[Bldg#]=" & Me.cmbBldg
(the addition is *exactly* as I put it)

If your combo name is not "cmbBldg "chage the Me. expression at the end to
your combo box name


Hope that that make sense

Regards

JK,
I don't seam to implement this yet.
How about a create a cmbBldg that have as Row Source tblBldgs? (tblBldgs
is
a table that has all the number I need).
Now, could I filter lstBldgs with with this cmbBldgs box
lstBldgs Row Source is:
SELECT tblWorkServiceOrders.Id, tblWorkServiceOrders.[Bldg#],
tblWorkServiceOrders.WRNumber, tblWorkServiceOrders.POC,
tblWorkServiceOrders.DescriptionOfRequirements FROM tblWorkServiceOrders;

I appreciate your efforts JK!
Adnan

--
Please post all your inquiries on this community so we can all benefit -
Thank you!

Adnan,

You must have a common field in both the combo and the list boxes. In
order
for it to work you need to tell Access how to filter the list box
depending
on what is in the combo. The usual way is :

cmbBuliding:
SELECT tblBldgs.Bldg, tblBldgs.[SomeField] FROM tblBldgs ORDER BY [Bldg];

SELECT tblWorkServiceOrders.Id, tblWorkServiceOrders.WRNumber,
tblWorkServiceOrders.POC,
tblWorkServiceOrders.DescriptionOfRequirements,tblWorkServiceOrders.[SomeField],
FROM tblWorkServiceOrders

Only then we can limit the List box to show all the records that
[someField]
in the list box = [SomeFiled] in the combo.

However if you want to use the Bldg field in the combo you have to tell
how
to restrict it in list box e.g.

tblBldgs.Bldg= what in the list box (or < or >= or whatever,)

Regards/JK

This is from Combo Box (cmbBldgs):
SELECT tblBldgs.Bldg FROM tblBldgs ORDER BY [Bldg];

And this is from the List Box (lstBldgs):
SELECT tblWorkServiceOrders.Id, tblWorkServiceOrders.WRNumber,
tblWorkServiceOrders.POC,
tblWorkServiceOrders.DescriptionOfRequirements
FROM
tblWorkServiceOrders;

Thank you JK!
Adnan
--
Please post all your inquiries on this community so we can all
benefit -
Thank you!


:

Andan,

Go to both your combo and list boxes, copy their Row Source from their
respective Property sheets and paste them here. (they both start with
the
word "SELECT")

Also tell me, for each of them, the bound column (a number) and which
value
(field name) in your combo you want filter on in your list box. and
how
(e.g. =,>=,< etc )

Than I will be able to give you the *exact* syntax.

Regards/JK





JK, I appreciate your quick response,
I tried your code and it doesn't seam to work. I think I'm missing
something
here. Let me try again being a little more clear:

My form name is......frmCosts
My combo box name is...cmbBldgs
My list box name is.....lstBldgs
My query name is.......qryForListBox

I need the Cambo box (cmbBldgs) to filter my List Box (lstBldgs)
after
Combo
Box's update. The List Box (lstBldgs) feeds from Query
(qryForListBox)
and
has these fields: WRNumber, POC and DescriptionOfWork

Again, thank you JK!
Adnan

 

[Guest]

JK,

I've been trying. It is just not working. I did exactly as you instructed
me. No luck. I’m stuck.
Do u want me to email you a sample of this mdb? You can check this out then
post the code here again so the others will still benefit…

Thank you,
Adnan

--
Please post all your inquiries on this community so we can all benefit -
Thank you!

Adnan

Ceate a combo, cmbBldg with the number you need, make that filed as Bound
coloumn. If for example, you want the list box to show you only the records
where tblWorkServiceOrders.[Bldg#]= The result of the combo. you need to add
an expression in AfterUpdate events of the Combo *AND* in the OnCurrent of
the *Form* , use Event Procedures.

The expression is:

Me.lstBldgs.RowSouce="SELECT tblWorkServiceOrders.Id,
tblWorkServiceOrders.[Bldg#],
tblWorkServiceOrders.WRNumber, tblWorkServiceOrders.POC,
tblWorkServiceOrders.DescriptionOfRequirements FROM tblWorkServiceOrders
WHERE
tblWorkServiceOrders.[Bldg#]=" & Me.cmbBldg

in Other words:
Me.lstBldgs.RowSource=" paste here what you have now in row source AND add:
WHERE tblWorkServiceOrders.[Bldg#]=" & Me.cmbBldg
(the addition is *exactly* as I put it)

If your combo name is not "cmbBldg "chage the Me. expression at the end to
your combo box name


Hope that that make sense

Regards

JK,
I don't seam to implement this yet.
How about a create a cmbBldg that have as Row Source tblBldgs? (tblBldgs
is
a table that has all the number I need).
Now, could I filter lstBldgs with with this cmbBldgs box
lstBldgs Row Source is:
SELECT tblWorkServiceOrders.Id, tblWorkServiceOrders.[Bldg#],
tblWorkServiceOrders.WRNumber, tblWorkServiceOrders.POC,
tblWorkServiceOrders.DescriptionOfRequirements FROM tblWorkServiceOrders;

I appreciate your efforts JK!
Adnan

--
Please post all your inquiries on this community so we can all benefit -
Thank you!

Adnan,

You must have a common field in both the combo and the list boxes. In
order
for it to work you need to tell Access how to filter the list box
depending
on what is in the combo. The usual way is :

cmbBuliding:
SELECT tblBldgs.Bldg, tblBldgs.[SomeField] FROM tblBldgs ORDER BY [Bldg];

SELECT tblWorkServiceOrders.Id, tblWorkServiceOrders.WRNumber,
tblWorkServiceOrders.POC,
tblWorkServiceOrders.DescriptionOfRequirements,tblWorkServiceOrders.[SomeField],
FROM tblWorkServiceOrders

Only then we can limit the List box to show all the records that
[someField]
in the list box = [SomeFiled] in the combo.

However if you want to use the Bldg field in the combo you have to tell
how
to restrict it in list box e.g.

tblBldgs.Bldg= what in the list box (or < or >= or whatever,)

Regards/JK


This is from Combo Box (cmbBldgs):
SELECT tblBldgs.Bldg FROM tblBldgs ORDER BY [Bldg];

And this is from the List Box (lstBldgs):
SELECT tblWorkServiceOrders.Id, tblWorkServiceOrders.WRNumber,
tblWorkServiceOrders.POC,
tblWorkServiceOrders.DescriptionOfRequirements
FROM
tblWorkServiceOrders;

Thank you JK!
Adnan
--
Please post all your inquiries on this community so we can all
benefit -
Thank you!


:

Andan,

Go to both your combo and list boxes, copy their Row Source from their
respective Property sheets and paste them here. (they both start with
the
word "SELECT")

Also tell me, for each of them, the bound column (a number) and which
value
(field name) in your combo you want filter on in your list box. and
how
(e.g. =,>=,< etc )

Than I will be able to give you the *exact* syntax.

Regards/JK





JK, I appreciate your quick response,
I tried your code and it doesn't seam to work. I think I'm missing
something
here. Let me try again being a little more clear:

My form name is......frmCosts
My combo box name is...cmbBldgs
My list box name is.....lstBldgs
My query name is.......qryForListBox

I need the Cambo box (cmbBldgs) to filter my List Box (lstBldgs)
after
Combo
Box's update. The List Box (lstBldgs) feeds from Query
(qryForListBox)
and
has these fields: WRNumber, POC and DescriptionOfWork

Again, thank you JK!
Adnan

 

[JK]

Adnan,

Zip your db and send it to:

sarichart at ozzienet dot net

Put in mail subject something that it will distinguish you mail from a spam
(my anti spam is vicious), something like "Adnan DB" or similar

Await yours.

Regards/JK

JK,

I've been trying. It is just not working. I did exactly as you instructed
me. No luck. I'm stuck.
Do u want me to email you a sample of this mdb? You can check this out
then
post the code here again so the others will still benefit.

Thank you,
Adnan

--
Please post all your inquiries on this community so we can all benefit -
Thank you!

Adnan

Ceate a combo, cmbBldg with the number you need, make that filed as
Bound
coloumn. If for example, you want the list box to show you only the
records
where tblWorkServiceOrders.[Bldg#]= The result of the combo. you need to
add
an expression in AfterUpdate events of the Combo *AND* in the OnCurrent
of
the *Form* , use Event Procedures.

The expression is:

Me.lstBldgs.RowSouce="SELECT tblWorkServiceOrders.Id,
tblWorkServiceOrders.[Bldg#],
tblWorkServiceOrders.WRNumber, tblWorkServiceOrders.POC,
tblWorkServiceOrders.DescriptionOfRequirements FROM tblWorkServiceOrders
WHERE
tblWorkServiceOrders.[Bldg#]=" & Me.cmbBldg

in Other words:
Me.lstBldgs.RowSource=" paste here what you have now in row source AND
add:
WHERE tblWorkServiceOrders.[Bldg#]=" & Me.cmbBldg
(the addition is *exactly* as I put it)

If your combo name is not "cmbBldg "chage the Me. expression at the end
to
your combo box name


Hope that that make sense

Regards

JK,
I don't seam to implement this yet.
How about a create a cmbBldg that have as Row Source tblBldgs?
(tblBldgs
is
a table that has all the number I need).
Now, could I filter lstBldgs with with this cmbBldgs box
lstBldgs Row Source is:
SELECT tblWorkServiceOrders.Id, tblWorkServiceOrders.[Bldg#],
tblWorkServiceOrders.WRNumber, tblWorkServiceOrders.POC,
tblWorkServiceOrders.DescriptionOfRequirements FROM
tblWorkServiceOrders;

I appreciate your efforts JK!
Adnan

--
Please post all your inquiries on this community so we can all
benefit -
Thank you!


:

Adnan,

You must have a common field in both the combo and the list boxes. In
order
for it to work you need to tell Access how to filter the list box
depending
on what is in the combo. The usual way is :

cmbBuliding:
SELECT tblBldgs.Bldg, tblBldgs.[SomeField] FROM tblBldgs ORDER BY
[Bldg];

SELECT tblWorkServiceOrders.Id, tblWorkServiceOrders.WRNumber,
tblWorkServiceOrders.POC,
tblWorkServiceOrders.DescriptionOfRequirements,tblWorkServiceOrders.[SomeField],
FROM tblWorkServiceOrders

Only then we can limit the List box to show all the records that
[someField]
in the list box = [SomeFiled] in the combo.

However if you want to use the Bldg field in the combo you have to
tell
how
to restrict it in list box e.g.

tblBldgs.Bldg= what in the list box (or < or >= or whatever,)

Regards/JK


This is from Combo Box (cmbBldgs):
SELECT tblBldgs.Bldg FROM tblBldgs ORDER BY [Bldg];

And this is from the List Box (lstBldgs):
SELECT tblWorkServiceOrders.Id, tblWorkServiceOrders.WRNumber,
tblWorkServiceOrders.POC,
tblWorkServiceOrders.DescriptionOfRequirements
FROM
tblWorkServiceOrders;

Thank you JK!
Adnan
--
Please post all your inquiries on this community so we can all
benefit -
Thank you!


:

Andan,

Go to both your combo and list boxes, copy their Row Source from
their
respective Property sheets and paste them here. (they both start
with
the
word "SELECT")

Also tell me, for each of them, the bound column (a number) and
which
value
(field name) in your combo you want filter on in your list box. and
how
(e.g. =,>=,< etc )

Than I will be able to give you the *exact* syntax.

Regards/JK





JK, I appreciate your quick response,
I tried your code and it doesn't seam to work. I think I'm
missing
something
here. Let me try again being a little more clear:

My form name is......frmCosts
My combo box name is...cmbBldgs
My list box name is.....lstBldgs
My query name is.......qryForListBox

I need the Cambo box (cmbBldgs) to filter my List Box (lstBldgs)
after
Combo
Box's update. The List Box (lstBldgs) feeds from Query
(qryForListBox)
and
has these fields: WRNumber, POC and DescriptionOfWork

Again, thank you JK!
Adnan