Filter Data Within List Box

A

a_20120

tan

Local time

Today, 04:36

Joined

Nov 21, 2006

Messages

175

• Apr 3, 2007

• #1

Hi every one...How it is possible to Filter Data within List Box, according to favorite field...?

for further information, see the attached file...

 

Attachments

• List Filter.zip

  19 KB · Views: 151

A

a_20120

tan

Local time

Today, 04:36

Joined

Nov 21, 2006

Messages

175

• Apr 3, 2007

• #2

Request

Could anyone plz tell me...
1. How can I filter the list box data according to which field?
there should be some one who reply me...Help appreciated...

 

G

geko

Registered User.

Local time

Today, 01:06

Joined

Jun 6, 2003

Messages

33

• Apr 3, 2007

• #3

I would guess you need to make a query (or use sql in vba) that would change the rowsource of your listbox according to your selected field - I assume you are using a combo box for the selection?

eg.
After update of myCombo

strSQL = "SELECT myTable.myField FROM myTable WHERE myTable.myField="myCombo.Value"

myListBox.rowsource = strSQL

Or something like that.

 

A

a_20120

tan

Local time

Today, 04:36

Joined

Nov 21, 2006

Messages

175

• Apr 3, 2007

• #4

Thanks for replying Mr. geko
But that combo contains the field names, when I click on any button, filter should consider the combo and then filter the list according to the combo value

 

G

geko

Registered User.

Local time

Today, 01:06

Joined

Jun 6, 2003

Messages

33

• Apr 3, 2007

• #5

so where are you getting the value to filter by?

unless you are confusing filter with sort that is.

 

G

geko

Registered User.

Local time

Today, 01:06

Joined

Jun 6, 2003

Messages

33

• Apr 3, 2007

• #6

oh i see.

ok, something like:

Code:

Dim strSQL as string
strSQL = "SELECT tblCustomers.CustomerID, tblCustomers.FName, tblCustomers.LName, tblCustomers.City, tblCustomers.Phone FROM tblCustomers WHERE tblCustomers" & me.cmbField.value & " LIKE " & Me.Toggle22.caption & ";"

On each toggle buttons onclick event, to get the datasource.

Then under that add:

Code:

me.lstData.rowsource = strSQL
me.lstDate.requery

Untested, just off the top of my head.

Hope that helps

 

A

a_20120

tan

Local time

Today, 04:36

Joined

Nov 21, 2006

Messages

175

• Apr 4, 2007

• #7

Thanks very much Mr. geko,
really appreciate ur help and it works well, but I have 27 toggle button caption from (A to Z), should I write for all them the same code? or there is a way to do that with only few lines?

 

G

geko

Registered User.

Local time

Today, 01:06

Joined

Jun 6, 2003

Messages

33

• Apr 5, 2007

• #8

Yes you can just make a routine that does most of the work, applying a parameter. Then call the routine from each button with 1 line of code that can be copied and pasted and slightly modified for each button.

For example

Code:

Public Sub sFilterListBox(strFilterValue as string)

Dim strSQL as string

strSQL = "SELECT tblCustomers.CustomerID, tblCustomers.FName, 
tblCustomers.LName, tblCustomers.City, tblCustomers.Phone " & _ 
"FROM tblCustomers WHERE tblCustomers" & Me.cmbField.Value & " LIKE " & strFilterValue & ";"

Me.lstData.RowSource = strSQL
Me.lstDate.Requery

End Sub

Then call that from each of the buttons with:

Code:

Private Sub Toggle22_Click()

sFilterListBox Me.Toggle22.Caption

End Sub

Then just change the single line for Toggle22 so that it works for Toggle23 etc.

Hope that helps.