Filter with listbox

[Paul]

Hi,

I'm using acces 2003.

I've got a listbox on top of my form that I would like to use to filterer
the form, so the listbox will contain entries that can be made in the first
column of my form. What I would like to achieve is that when I select a value
in that listbox my form will automatically only display the records that
match the value of that listbox.
Any ideas / suggestions?

Cheers,

Paul.

 

[vbasean]

try this:

http://support.microsoft.com/kb/135546



me.filter = me.[your list box name].selected

 

[Paul]

Sean,

I cannot get it to work, my database i nothing fancy just a table and a form
that is it!

Any more ideas?

cheers,

Paul

try this:

http://support.microsoft.com/kb/135546



me.filter = me.[your list box name].selected

Hi,

I'm using acces 2003.

I've got a listbox on top of my form that I would like to use to filterer
the form, so the listbox will contain entries that can be made in the first
column of my form. What I would like to achieve is that when I select a value
in that listbox my form will automatically only display the records that
match the value of that listbox.
Any ideas / suggestions?

Cheers,

Paul.

 

[vbasean]

Ok,

You have a table and a form
on your form are a listbox and the fields from your table.
I assume your form's recordsource is bound to the table. (standard practice
for beginner Access development)

I walked through the example in the linked thread.
the only thing that may hold you up is the line at [customerID]
where your id may be different IF YOUR ID IS A NUMBER REMOVE THE SINGLE
QUOTES AS IN MY EXAMPLE BELOW


Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[CustomerId]=" _
& Me![List0].ItemData(i)
Next i

' Filter the form using selected items in the list box.
Me.Filter = Criteria
Me.FilterOn = True

1) you add a list box
2) you set it's datasource to the same as your form
you could change the amount of columns so you can see the data for each ID
by going to the format tab under properties and changing the column count and
then formating the column widths see example here:
http://msdn.microsoft.com/en-us/library/aa224077(office.11).aspx
3) you set multi select to simple or extended
simple: just selects and deselects each item as you click on it
extended: selects multiple as you hold the control button
it doesn't matter as long as it suits you
4) you add a button
5) you add the code above to the buttons 'on click' event as it applies to
your situation changing control names etcetera

Sean,

I cannot get it to work, my database i nothing fancy just a table and a form
that is it!

Any more ideas?

cheers,

Paul

try this:

http://support.microsoft.com/kb/135546



me.filter = me.[your list box name].selected

Hi,

I'm using acces 2003.

I've got a listbox on top of my form that I would like to use to filterer
the form, so the listbox will contain entries that can be made in the first
column of my form. What I would like to achieve is that when I select a value
in that listbox my form will automatically only display the records that
match the value of that listbox.
Any ideas / suggestions?

Cheers,

Paul.

 

[Paul]

Sean,

Thanks it was indeed the customerId that had to change.

Now it works perfectly!!

Do you know how the filtering works with a combobox?

Thanks again,

Paul

Ok,

You have a table and a form
on your form are a listbox and the fields from your table.
I assume your form's recordsource is bound to the table. (standard practice
for beginner Access development)

I walked through the example in the linked thread.
the only thing that may hold you up is the line at [customerID]
where your id may be different IF YOUR ID IS A NUMBER REMOVE THE SINGLE
QUOTES AS IN MY EXAMPLE BELOW


Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[CustomerId]=" _
& Me![List0].ItemData(i)
Next i

' Filter the form using selected items in the list box.
Me.Filter = Criteria
Me.FilterOn = True

1) you add a list box
2) you set it's datasource to the same as your form
you could change the amount of columns so you can see the data for each ID
by going to the format tab under properties and changing the column count and
then formating the column widths see example here:
http://msdn.microsoft.com/en-us/library/aa224077(office.11).aspx
3) you set multi select to simple or extended
simple: just selects and deselects each item as you click on it
extended: selects multiple as you hold the control button
it doesn't matter as long as it suits you
4) you add a button
5) you add the code above to the buttons 'on click' event as it applies to
your situation changing control names etcetera

Sean,

I cannot get it to work, my database i nothing fancy just a table and a form
that is it!

Any more ideas?

cheers,

Paul

try this:

http://support.microsoft.com/kb/135546



me.filter = me.[your list box name].selected

:

Hi,

I'm using acces 2003.

I've got a listbox on top of my form that I would like to use to filterer
the form, so the listbox will contain entries that can be made in the first
column of my form. What I would like to achieve is that when I select a value
in that listbox my form will automatically only display the records that
match the value of that listbox.
Any ideas / suggestions?

Cheers,

Paul.

 

[vbasean]

try this:

http://www.techonthenet.com/access/forms/filter_form.php

Sean,

Thanks it was indeed the customerId that had to change.

Now it works perfectly!!

Do you know how the filtering works with a combobox?

Thanks again,

Paul

Ok,

You have a table and a form
on your form are a listbox and the fields from your table.
I assume your form's recordsource is bound to the table. (standard practice
for beginner Access development)

I walked through the example in the linked thread.
the only thing that may hold you up is the line at [customerID]
where your id may be different IF YOUR ID IS A NUMBER REMOVE THE SINGLE
QUOTES AS IN MY EXAMPLE BELOW


Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[CustomerId]=" _
& Me![List0].ItemData(i)
Next i

' Filter the form using selected items in the list box.
Me.Filter = Criteria
Me.FilterOn = True

1) you add a list box
2) you set it's datasource to the same as your form
you could change the amount of columns so you can see the data for each ID
by going to the format tab under properties and changing the column count and
then formating the column widths see example here:
http://msdn.microsoft.com/en-us/library/aa224077(office.11).aspx
3) you set multi select to simple or extended
simple: just selects and deselects each item as you click on it
extended: selects multiple as you hold the control button
it doesn't matter as long as it suits you
4) you add a button
5) you add the code above to the buttons 'on click' event as it applies to
your situation changing control names etcetera

Sean,

I cannot get it to work, my database i nothing fancy just a table and a form
that is it!

Any more ideas?

cheers,

Paul

:

try this:

http://support.microsoft.com/kb/135546



me.filter = me.[your list box name].selected

:

Hi,

I'm using acces 2003.

I've got a listbox on top of my form that I would like to use to filterer
the form, so the listbox will contain entries that can be made in the first
column of my form. What I would like to achieve is that when I select a value
in that listbox my form will automatically only display the records that
match the value of that listbox.
Any ideas / suggestions?

Cheers,

Paul.

 

[Paul]

Thanks,

I will, one more question regarding the listbox; I have several records with
the some value, so in my listbox i got now several identical names in there.
Is there a way to overcome this? I only would like the listbox to show 1
value and when I click on this one it shows me all the records with that same
value.

Any seggestions?

Cheers,

Paul

try this:

http://www.techonthenet.com/access/forms/filter_form.php

Sean,

Thanks it was indeed the customerId that had to change.

Now it works perfectly!!

Do you know how the filtering works with a combobox?

Thanks again,

Paul

Ok,

You have a table and a form
on your form are a listbox and the fields from your table.
I assume your form's recordsource is bound to the table. (standard practice
for beginner Access development)

I walked through the example in the linked thread.
the only thing that may hold you up is the line at [customerID]
where your id may be different IF YOUR ID IS A NUMBER REMOVE THE SINGLE
QUOTES AS IN MY EXAMPLE BELOW


Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[CustomerId]=" _
& Me![List0].ItemData(i)
Next i

' Filter the form using selected items in the list box.
Me.Filter = Criteria
Me.FilterOn = True

1) you add a list box
2) you set it's datasource to the same as your form
you could change the amount of columns so you can see the data for each ID
by going to the format tab under properties and changing the column count and
then formating the column widths see example here:
http://msdn.microsoft.com/en-us/library/aa224077(office.11).aspx
3) you set multi select to simple or extended
simple: just selects and deselects each item as you click on it
extended: selects multiple as you hold the control button
it doesn't matter as long as it suits you
4) you add a button
5) you add the code above to the buttons 'on click' event as it applies to
your situation changing control names etcetera


:

Sean,

I cannot get it to work, my database i nothing fancy just a table and a form
that is it!

Any more ideas?

cheers,

Paul

:

try this:

http://support.microsoft.com/kb/135546



me.filter = me.[your list box name].selected

:

Hi,

I'm using acces 2003.

I've got a listbox on top of my form that I would like to use to filterer
the form, so the listbox will contain entries that can be made in the first
column of my form. What I would like to achieve is that when I select a value
in that listbox my form will automatically only display the records that
match the value of that listbox.
Any ideas / suggestions?

Cheers,

Paul.

 

[vbasean]

1) instead of the listbox being bound to the ID you'll have to have it bound
to the name
2) you'll have to change the code with the button

Criteria = Criteria & "[name of name field]=" & chr(34) _
& Me![List0].ItemData(i) & chr(34)

this will only work IF all the names you are looking for are spelled EXACTLY
the same.

--
~Your Friend Chris
http://myvbastuff.blogspot.com/

Thanks,

I will, one more question regarding the listbox; I have several records with
the some value, so in my listbox i got now several identical names in there.
Is there a way to overcome this? I only would like the listbox to show 1
value and when I click on this one it shows me all the records with that same
value.

Any seggestions?

Cheers,

Paul

try this:

http://www.techonthenet.com/access/forms/filter_form.php

Sean,

Thanks it was indeed the customerId that had to change.

Now it works perfectly!!

Do you know how the filtering works with a combobox?

Thanks again,

Paul

:

Ok,

You have a table and a form
on your form are a listbox and the fields from your table.
I assume your form's recordsource is bound to the table. (standard practice
for beginner Access development)

I walked through the example in the linked thread.
the only thing that may hold you up is the line at [customerID]
where your id may be different IF YOUR ID IS A NUMBER REMOVE THE SINGLE
QUOTES AS IN MY EXAMPLE BELOW


Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[CustomerId]=" _
& Me![List0].ItemData(i)
Next i

' Filter the form using selected items in the list box.
Me.Filter = Criteria
Me.FilterOn = True

1) you add a list box
2) you set it's datasource to the same as your form
you could change the amount of columns so you can see the data for each ID
by going to the format tab under properties and changing the column count and
then formating the column widths see example here:
http://msdn.microsoft.com/en-us/library/aa224077(office.11).aspx
3) you set multi select to simple or extended
simple: just selects and deselects each item as you click on it
extended: selects multiple as you hold the control button
it doesn't matter as long as it suits you
4) you add a button
5) you add the code above to the buttons 'on click' event as it applies to
your situation changing control names etcetera


:

Sean,

I cannot get it to work, my database i nothing fancy just a table and a form
that is it!

Any more ideas?

cheers,

Paul

:

try this:

http://support.microsoft.com/kb/135546



me.filter = me.[your list box name].selected

:

Hi,

I'm using acces 2003.

I've got a listbox on top of my form that I would like to use to filterer
the form, so the listbox will contain entries that can be made in the first
column of my form. What I would like to achieve is that when I select a value
in that listbox my form will automatically only display the records that
match the value of that listbox.
Any ideas / suggestions?

Cheers,

Paul.

 

[vbasean]

I think it would also be necessary to change the 'listbox' datasource

open the query builder for the rowsource (the elipse button [...])

click on view, totals
add the name field you want to display in your listbox
it should have the term "Group By" under it once added.
close the query (save or not save, does not matter)

change the criterea portion of the button click event as explained in
earlier post
--
~Your Friend Chris
http://myvbastuff.blogspot.com/

Thanks,

I will, one more question regarding the listbox; I have several records with
the some value, so in my listbox i got now several identical names in there.
Is there a way to overcome this? I only would like the listbox to show 1
value and when I click on this one it shows me all the records with that same
value.

Any seggestions?

Cheers,

Paul

try this:

http://www.techonthenet.com/access/forms/filter_form.php

Sean,

Thanks it was indeed the customerId that had to change.

Now it works perfectly!!

Do you know how the filtering works with a combobox?

Thanks again,

Paul

:

Ok,

You have a table and a form
on your form are a listbox and the fields from your table.
I assume your form's recordsource is bound to the table. (standard practice
for beginner Access development)

I walked through the example in the linked thread.
the only thing that may hold you up is the line at [customerID]
where your id may be different IF YOUR ID IS A NUMBER REMOVE THE SINGLE
QUOTES AS IN MY EXAMPLE BELOW


Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[CustomerId]=" _
& Me![List0].ItemData(i)
Next i

' Filter the form using selected items in the list box.
Me.Filter = Criteria
Me.FilterOn = True

1) you add a list box
2) you set it's datasource to the same as your form
you could change the amount of columns so you can see the data for each ID
by going to the format tab under properties and changing the column count and
then formating the column widths see example here:
http://msdn.microsoft.com/en-us/library/aa224077(office.11).aspx
3) you set multi select to simple or extended
simple: just selects and deselects each item as you click on it
extended: selects multiple as you hold the control button
it doesn't matter as long as it suits you
4) you add a button
5) you add the code above to the buttons 'on click' event as it applies to
your situation changing control names etcetera


:

Sean,

I cannot get it to work, my database i nothing fancy just a table and a form
that is it!

Any more ideas?

cheers,

Paul

:

try this:

http://support.microsoft.com/kb/135546



me.filter = me.[your list box name].selected

:

Hi,

I'm using acces 2003.

I've got a listbox on top of my form that I would like to use to filterer
the form, so the listbox will contain entries that can be made in the first
column of my form. What I would like to achieve is that when I select a value
in that listbox my form will automatically only display the records that
match the value of that listbox.
Any ideas / suggestions?

Cheers,

Paul.

 

[Paul]

Sean,

It worked partially, the double entries in the listbox are removed but when
I press the command button there is a pop up window that needs a parameter
value (and if i enter the value I need then the filter is applied). Any
suggestions??

Cheers,

Paul

I think it would also be necessary to change the 'listbox' datasource

open the query builder for the rowsource (the elipse button [...])

click on view, totals
add the name field you want to display in your listbox
it should have the term "Group By" under it once added.
close the query (save or not save, does not matter)

change the criterea portion of the button click event as explained in
earlier post
--
~Your Friend Chris
http://myvbastuff.blogspot.com/

Thanks,

I will, one more question regarding the listbox; I have several records with
the some value, so in my listbox i got now several identical names in there.
Is there a way to overcome this? I only would like the listbox to show 1
value and when I click on this one it shows me all the records with that same
value.

Any seggestions?

Cheers,

Paul

try this:

http://www.techonthenet.com/access/forms/filter_form.php

:

Sean,

Thanks it was indeed the customerId that had to change.

Now it works perfectly!!

Do you know how the filtering works with a combobox?

Thanks again,

Paul

:

Ok,

You have a table and a form
on your form are a listbox and the fields from your table.
I assume your form's recordsource is bound to the table. (standard practice
for beginner Access development)

I walked through the example in the linked thread.
the only thing that may hold you up is the line at [customerID]
where your id may be different IF YOUR ID IS A NUMBER REMOVE THE SINGLE
QUOTES AS IN MY EXAMPLE BELOW


Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[CustomerId]=" _
& Me![List0].ItemData(i)
Next i

' Filter the form using selected items in the list box.
Me.Filter = Criteria
Me.FilterOn = True

1) you add a list box
2) you set it's datasource to the same as your form
you could change the amount of columns so you can see the data for each ID
by going to the format tab under properties and changing the column count and
then formating the column widths see example here:
http://msdn.microsoft.com/en-us/library/aa224077(office.11).aspx
3) you set multi select to simple or extended
simple: just selects and deselects each item as you click on it
extended: selects multiple as you hold the control button
it doesn't matter as long as it suits you
4) you add a button
5) you add the code above to the buttons 'on click' event as it applies to
your situation changing control names etcetera


:

Sean,

I cannot get it to work, my database i nothing fancy just a table and a form
that is it!

Any more ideas?

cheers,

Paul

:

try this:

http://support.microsoft.com/kb/135546



me.filter = me.[your list box name].selected

:

Hi,

I'm using acces 2003.

I've got a listbox on top of my form that I would like to use to filterer
the form, so the listbox will contain entries that can be made in the first
column of my form. What I would like to achieve is that when I select a value
in that listbox my form will automatically only display the records that
match the value of that listbox.
Any ideas / suggestions?

Cheers,

Paul.

 

[vbasean]

show me your command button vba and the sql for the group query
you can open the query and on the view button (looks like a triangle) change
it to "SQL" and copy and paste it.
--
~Your Friend Chris
http://myvbastuff.blogspot.com/

Sean,

It worked partially, the double entries in the listbox are removed but when
I press the command button there is a pop up window that needs a parameter
value (and if i enter the value I need then the filter is applied). Any
suggestions??

Cheers,

Paul

I think it would also be necessary to change the 'listbox' datasource

open the query builder for the rowsource (the elipse button [...])

click on view, totals
add the name field you want to display in your listbox
it should have the term "Group By" under it once added.
close the query (save or not save, does not matter)

change the criterea portion of the button click event as explained in
earlier post
--
~Your Friend Chris
http://myvbastuff.blogspot.com/

Thanks,

I will, one more question regarding the listbox; I have several records with
the some value, so in my listbox i got now several identical names in there.
Is there a way to overcome this? I only would like the listbox to show 1
value and when I click on this one it shows me all the records with that same
value.

Any seggestions?

Cheers,

Paul

:

try this:

http://www.techonthenet.com/access/forms/filter_form.php

:

Sean,

Thanks it was indeed the customerId that had to change.

Now it works perfectly!!

Do you know how the filtering works with a combobox?

Thanks again,

Paul

:

Ok,

You have a table and a form
on your form are a listbox and the fields from your table.
I assume your form's recordsource is bound to the table. (standard practice
for beginner Access development)

I walked through the example in the linked thread.
the only thing that may hold you up is the line at [customerID]
where your id may be different IF YOUR ID IS A NUMBER REMOVE THE SINGLE
QUOTES AS IN MY EXAMPLE BELOW


Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[CustomerId]=" _
& Me![List0].ItemData(i)
Next i

' Filter the form using selected items in the list box.
Me.Filter = Criteria
Me.FilterOn = True

1) you add a list box
2) you set it's datasource to the same as your form
you could change the amount of columns so you can see the data for each ID
by going to the format tab under properties and changing the column count and
then formating the column widths see example here:
http://msdn.microsoft.com/en-us/library/aa224077(office.11).aspx
3) you set multi select to simple or extended
simple: just selects and deselects each item as you click on it
extended: selects multiple as you hold the control button
it doesn't matter as long as it suits you
4) you add a button
5) you add the code above to the buttons 'on click' event as it applies to
your situation changing control names etcetera


:

Sean,

I cannot get it to work, my database i nothing fancy just a table and a form
that is it!

Any more ideas?

cheers,

Paul

:

try this:

http://support.microsoft.com/kb/135546



me.filter = me.[your list box name].selected

:

Hi,

I'm using acces 2003.

I've got a listbox on top of my form that I would like to use to filterer
the form, so the listbox will contain entries that can be made in the first
column of my form. What I would like to achieve is that when I select a value
in that listbox my form will automatically only display the records that
match the value of that listbox.
Any ideas / suggestions?

Cheers,

Paul.

 

[Paul]

Chris,

I got it working!! Thanks for your help!!

Cheers,

Paul

show me your command button vba and the sql for the group query
you can open the query and on the view button (looks like a triangle) change
it to "SQL" and copy and paste it.
--
~Your Friend Chris
http://myvbastuff.blogspot.com/

Sean,

It worked partially, the double entries in the listbox are removed but when
I press the command button there is a pop up window that needs a parameter
value (and if i enter the value I need then the filter is applied). Any
suggestions??

Cheers,

Paul

I think it would also be necessary to change the 'listbox' datasource

open the query builder for the rowsource (the elipse button [...])

click on view, totals
add the name field you want to display in your listbox
it should have the term "Group By" under it once added.
close the query (save or not save, does not matter)

change the criterea portion of the button click event as explained in
earlier post
--
~Your Friend Chris
http://myvbastuff.blogspot.com/


:

Thanks,

I will, one more question regarding the listbox; I have several records with
the some value, so in my listbox i got now several identical names in there.
Is there a way to overcome this? I only would like the listbox to show 1
value and when I click on this one it shows me all the records with that same
value.

Any seggestions?

Cheers,

Paul

:

try this:

http://www.techonthenet.com/access/forms/filter_form.php

:

Sean,

Thanks it was indeed the customerId that had to change.

Now it works perfectly!!

Do you know how the filtering works with a combobox?

Thanks again,

Paul

:

Ok,

You have a table and a form
on your form are a listbox and the fields from your table.
I assume your form's recordsource is bound to the table. (standard practice
for beginner Access development)

I walked through the example in the linked thread.
the only thing that may hold you up is the line at [customerID]
where your id may be different IF YOUR ID IS A NUMBER REMOVE THE SINGLE
QUOTES AS IN MY EXAMPLE BELOW


Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[CustomerId]=" _
& Me![List0].ItemData(i)
Next i

' Filter the form using selected items in the list box.
Me.Filter = Criteria
Me.FilterOn = True

1) you add a list box
2) you set it's datasource to the same as your form
you could change the amount of columns so you can see the data for each ID
by going to the format tab under properties and changing the column count and
then formating the column widths see example here:
http://msdn.microsoft.com/en-us/library/aa224077(office.11).aspx
3) you set multi select to simple or extended
simple: just selects and deselects each item as you click on it
extended: selects multiple as you hold the control button
it doesn't matter as long as it suits you
4) you add a button
5) you add the code above to the buttons 'on click' event as it applies to
your situation changing control names etcetera


:

Sean,

I cannot get it to work, my database i nothing fancy just a table and a form
that is it!

Any more ideas?

cheers,

Paul

:

try this:

http://support.microsoft.com/kb/135546



me.filter = me.[your list box name].selected

:

Hi,

I'm using acces 2003.

I've got a listbox on top of my form that I would like to use to filterer
the form, so the listbox will contain entries that can be made in the first
column of my form. What I would like to achieve is that when I select a value
in that listbox my form will automatically only display the records that
match the value of that listbox.
Any ideas / suggestions?

Cheers,

Paul.