Filter records by list box selection

[Julia]

Hi,

This seems like it has to be very basic but I can't find the answer anywhere!

I have a form containing data for all records in my database. Since there is
a lot of data, I have some buttons that pop up a small form and allow me to
select a criteria from a combo box that I can use to filter the records in
the original form. I can scroll through the filtered records and find what I
need more easily.

When I try to make the combo box a list box, I get some error messages and I
just can't get it to work.

The unbound listbox (list0) is populated with the right criteria and I have
tried to add either the primary key or the name of the field selected and
Me![List0] in different variations to the filter part of the OpenForm command
(e.g. "[StatusID]=" & Me![List0]).

Can someone please help? I have the feeling that it should be really easy to
get it to work and I am not sure why it doesn't.

 

[Dirk Goldgar]

Hi,

This seems like it has to be very basic but I can't find the answer
anywhere!

I have a form containing data for all records in my database. Since there
is
a lot of data, I have some buttons that pop up a small form and allow me
to
select a criteria from a combo box that I can use to filter the records in
the original form. I can scroll through the filtered records and find what
I
need more easily.

When I try to make the combo box a list box, I get some error messages and
I
just can't get it to work.

The unbound listbox (list0) is populated with the right criteria and I
have
tried to add either the primary key or the name of the field selected and
Me![List0] in different variations to the filter part of the OpenForm
command
(e.g. "[StatusID]=" & Me![List0]).

Can someone please help? I have the feeling that it should be really easy
to
get it to work and I am not sure why it doesn't.

It would help to know what the error messages are, what your current code
looks like, and what line(s) code raise the errors.

You're right -- this should be simple, so it's probably something minor,
especially if you originally had code that used a combo box for this
successfully.

 

[Julia]

Hi Dirk,

Thanks for getting back to me.

The code I have for the combo box is this, and it does exactly what I want:
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "MainForm"
stLinkCriteria = "[ExperimentID]=" & Me![Combo0]
DoCmd.Close acForm, "frmExperimentSelect", acSaveNo
DoCmd.OpenForm stDocName, , , stLinkCriteria

If I put the same code into the onclick for a button on the form with the
list box:
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "MainForm"
stLinkCriteria = "[StatusID]=" & Me![List0]
DoCmd.Close acForm, "frmStatusSelect", acSaveNo
DoCmd.OpenForm stDocName, , ,stLinkCriteria

I get a popup "Enter parameter value" and the name I clicked on that matches
the StatusID is printed just above the little box that I can type in in the
popup.

Whatever I type, I am returned to "MainForm" with no records selected.

So it seems strange that the popup has captured the name I selected in the
listbox, but can't use it to filter the records in MainForm like the combo
box does. It looks like the list box works differently to the combo box but I
can't seem to find out in what ways.

Is this enough information?

Julia

Hi,

This seems like it has to be very basic but I can't find the answer
anywhere!

I have a form containing data for all records in my database. Since there
is
a lot of data, I have some buttons that pop up a small form and allow me
to
select a criteria from a combo box that I can use to filter the records in
the original form. I can scroll through the filtered records and find what
I
need more easily.

When I try to make the combo box a list box, I get some error messages and
I
just can't get it to work.

The unbound listbox (list0) is populated with the right criteria and I
have
tried to add either the primary key or the name of the field selected and
Me![List0] in different variations to the filter part of the OpenForm
command
(e.g. "[StatusID]=" & Me![List0]).

Can someone please help? I have the feeling that it should be really easy
to
get it to work and I am not sure why it doesn't.

It would help to know what the error messages are, what your current code
looks like, and what line(s) code raise the errors.

You're right -- this should be simple, so it's probably something minor,
especially if you originally had code that used a combo box for this
successfully.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Julia]

Just to explain what I mean, Alison Balter's book "Mastering Access 2003" says:
List boxes are similar to combo boxes, but differ from them in three major
ways:
*They consume more screen space
*They allow you to select only from the list that's displayed. This means
you can't type new values into a list box (as you can with a combo box).
*They can be configured to let you select multiple items.

This is similar to the information out there. So I wasn't really expecting
that the same code wouldn't work.

Hi,

This seems like it has to be very basic but I can't find the answer
anywhere!

I have a form containing data for all records in my database. Since there
is
a lot of data, I have some buttons that pop up a small form and allow me
to
select a criteria from a combo box that I can use to filter the records in
the original form. I can scroll through the filtered records and find what
I
need more easily.

When I try to make the combo box a list box, I get some error messages and
I
just can't get it to work.

The unbound listbox (list0) is populated with the right criteria and I
have
tried to add either the primary key or the name of the field selected and
Me![List0] in different variations to the filter part of the OpenForm
command
(e.g. "[StatusID]=" & Me![List0]).

Can someone please help? I have the feeling that it should be really easy
to
get it to work and I am not sure why it doesn't.

It would help to know what the error messages are, what your current code
looks like, and what line(s) code raise the errors.

You're right -- this should be simple, so it's probably something minor,
especially if you originally had code that used a combo box for this
successfully.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Hi Dirk,

Thanks for getting back to me.

The code I have for the combo box is this, and it does exactly what I
want:
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "MainForm"
stLinkCriteria = "[ExperimentID]=" & Me![Combo0]
DoCmd.Close acForm, "frmExperimentSelect", acSaveNo
DoCmd.OpenForm stDocName, , , stLinkCriteria

If I put the same code into the onclick for a button on the form with the
list box:
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "MainForm"
stLinkCriteria = "[StatusID]=" & Me![List0]
DoCmd.Close acForm, "frmStatusSelect", acSaveNo
DoCmd.OpenForm stDocName, , ,stLinkCriteria

I get a popup "Enter parameter value" and the name I clicked on that
matches
the StatusID is printed just above the little box that I can type in in
the
popup.

Whatever I type, I am returned to "MainForm" with no records selected.

So it seems strange that the popup has captured the name I selected in the
listbox, but can't use it to filter the records in MainForm like the combo
box does. It looks like the list box works differently to the combo box
but I
can't seem to find out in what ways.

Is this enough information?

I believe so. I don't think it has anything to do with the list box itself.
Instead, I suspect that the StatusID field is a text field, and so the value
from the list box must be enclosed in quotes:

stLinkCriteria = "[StatusID]='" & Me![List0] & "'"

Note that the quoting I used above assumes that the single-quote character
(') will never appear in any StatusID.

If I'm wrong about the StatusID being a text field, then my secondary guess
is that your list box's Bound Column property is not set to the column that
contains the StatusID, but rather to some other column that contains text
(the "name" you referred to in your message). In that case, you would just
have to change the list box's Bound Column property to the correct column.
But, as I said, my first guess is that StatusID is a text field.

 

[Julia]

Thanks for the tip!

My Primary Keys and Foreign Keys are all numbers so it couldn't be your
first suggestion, but your second one worked.

This is how I did it (for other newbies who might be struggling):

I went into the list box properties and changed the number of bound columns
to 3 because my primary key was for some reason the third field in the table
that the list box is based on. I adjusted the column widths to 3cm;0cm;0cm so
that the list box shows text rather than the primary key and another column
that is not particularly meaningful. I then changed the "Bound Column"
property to 3, which contains the primary key, and matches with the foreign
key in the main table that I want to sort on. It worked really well!

Thanks,
Julia

Hi Dirk,

Thanks for getting back to me.

The code I have for the combo box is this, and it does exactly what I
want:
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "MainForm"
stLinkCriteria = "[ExperimentID]=" & Me![Combo0]
DoCmd.Close acForm, "frmExperimentSelect", acSaveNo
DoCmd.OpenForm stDocName, , , stLinkCriteria

If I put the same code into the onclick for a button on the form with the
list box:
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "MainForm"
stLinkCriteria = "[StatusID]=" & Me![List0]
DoCmd.Close acForm, "frmStatusSelect", acSaveNo
DoCmd.OpenForm stDocName, , ,stLinkCriteria

I get a popup "Enter parameter value" and the name I clicked on that
matches
the StatusID is printed just above the little box that I can type in in
the
popup.

Whatever I type, I am returned to "MainForm" with no records selected.

So it seems strange that the popup has captured the name I selected in the
listbox, but can't use it to filter the records in MainForm like the combo
box does. It looks like the list box works differently to the combo box
but I
can't seem to find out in what ways.

Is this enough information?

I believe so. I don't think it has anything to do with the list box itself.
Instead, I suspect that the StatusID field is a text field, and so the value
from the list box must be enclosed in quotes:

stLinkCriteria = "[StatusID]='" & Me![List0] & "'"

Note that the quoting I used above assumes that the single-quote character
(') will never appear in any StatusID.

If I'm wrong about the StatusID being a text field, then my secondary guess
is that your list box's Bound Column property is not set to the column that
contains the StatusID, but rather to some other column that contains text
(the "name" you referred to in your message). In that case, you would just
have to change the list box's Bound Column property to the correct column.
But, as I said, my first guess is that StatusID is a text field.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks for the tip!

My Primary Keys and Foreign Keys are all numbers so it couldn't be your
first suggestion, but your second one worked.

Darn! I was hoping to get it on the first guess.

I'm glad the second guess worked for you.