help to Filtering Listbox

[zozzz]

hi everyone
I have a module for filtering subform bese on 4 Fields in onchange event and I cant use it to filtering Listbox . can anyone help please ?


Module Code :

Option Compare Database
Function Search(Expr As String, Domain As String, txt_search As String) As String
x = txt_search & " "
For i = 1 To Len(txt_search)
c = InStr(1, x, " ")
If c > 0 Then
y = Left(x, c - 1)
x = Right(x, Len(x) - c)
i = c
q = q & " and " & "((" & Domain & "." & Expr & ") like '*" & y & "*')"
Else
Exit For
End If
Next i
q = q & " and " & "((" & Domain & "." & Expr & ") like '*" & x & "*')"
Search = Right(q, Len(q) - 5)
Debug.Print x
End Function

Text4 code for filtering subform :

Private Sub Text4_Change()
On Error Resume Next
Me.Form_sub.Form.Filter = Search("[Fname] & [Contry] & [Age] & [Phon]", "Table1", Nz(Me.Text4.Text))
Me.Form_sub.Form.FilterOn = True
Me.Form_sub.Form.Requery
If Me.Text4.Text = "" Then
Me.Form_sub.Form.FilterOn = False
Me.Form_sub.Form.Requery
End If
End Sub

Text4 code for filtering Listbox ???

 

[pbaldy]

You want to use that function? Presumably it creates a valid filter, so to use it on a listbox you'd set the row source:

Me.ListboxName.RowSource = "SELECT Blah FROM TableName WHERE " & Search(...)

 

[zozzz]

Thanks for your help

Excellent My Friend

 

[pbaldy]

Happy to help!

 

[moke123]

As written above, does that function work?
notably you have no dim statements.

you should also have at the top of each module

Option Compare Database
Option Explicit

 

[zozzz]

As written above, does that function work?
notably you have no dim statements.

you should also have at the top of each module

Option Compare Database
Option Explicit

==========================
Compile Error:
Variable not defined


what is this error for??
and how can fix it ??

 

[isladogs]

==========================
Compile Error:
Variable not defined


what is this error for??
and how can fix it ??

Option explicit requires you to declare the datatype of all variables used.
It makes for better coding though with a little more effort.

So in your case you need statement like this for all variable used

 Dim I as Integer, c as integer
Dim x as string

and so on ...

 

[moke123]

if you go into the VBE under tools>options, you can set it to always include Option Explicit in all your modules. It will save you a lot of frustration in the long run.

 

[zozzz]

Special Thanks to dear friends for replay

this code for just one textbox

Private Sub Text10_Change()
Me.List2.RowSource = "SELECT * FROM Table1 WHERE " & Search("[Fname] & [Contry] & [Age] & [Phon]", "Table1", Nz(Me.Text10.Text))
Me.List2.Requery
End Sub

Now, if we want to define separate textbox how to change the code ?
txt_fname , txt_contry , txt_age

 

[isladogs]

You could just do this for e.g. textbox Text2:

Private Sub Text2_Change()
Me.List2.RowSource = "SELECT * FROM Table1 WHERE " & Search("[Fname] & [Contry] & [Age] & [Phon]", "Table1", Nz(Me.Text2,""))
Me.List2.Requery
End Sub

or perhaps use this repeateable code:

Private Sub Text2_Change()
strText = "Me.Text2"
Me.List2.RowSource = "SELECT * FROM Table1 WHERE " & Search("[Fname] & [Contry] & [Age] & [Phon]", "Table1", Nz(strText,""))
Me.List2.Requery
End Sub

If you do the 2nd version, add this line in the form module declaration section under Option Explicit

Dim strText As String

 

[zozzz]

where is my fault in this code ?

for text1 :

Private Sub Text1_Change()
Me.List2.RowSource = "SELECT * FROM Table1 WHERE " & _
(Search("[Fname]", "Table1", Nz(Me.Text1.Text))) And (Search("[Contry]", "Table1", Nz(Me.Text2.Text)))
Me.List2.Requery
End Sub

and for text2

Private Sub Text2_Change()
Me.List2.RowSource = "SELECT * FROM Table1 WHERE " & _
(Search("[Contry]", "Table1", Nz(Me.Text2.Text))) And (Search("[Fname]", "Table1", Nz(Me.Text1.Text)))
Me.List2.Requery
End Sub

To better understand
I wish could attached file

 

[pbaldy]

You should be able to attach the file after compact/repair and zipping. You might try this and see what the finished SQL is:

http://www.baldyweb.com/ImmediateWindow.htm

Telling us the error might help too.

 

[isladogs]

Some points to consider:

1. You need to specify the alternative in each of the Nz statements

2. You almost certainly don't need .Text

3. Bracketing may need altering

This might be correct

Private Sub Text1_Change()
Me.List2.RowSource = "SELECT * FROM Table1 WHERE " & _
(Search("[Fname]", "Table1", Nz(Me.Text1,"")) And Search("[Contry]", "Table1", Nz(Me.Text2,"")))
Me.List2.Requery
End Sub

 

[pbaldy]

2. You almost certainly don't need .Text

In the change event?

 

[isladogs]

Covering all bets...

Possibly almost certainly or perhaps not ...

 

[zozzz]

sorry
unfortunately not resolved

 

[zozzz]

In the change event?

yes
In the change event

 

[pbaldy]

You haven't said what error you get, or used the method I posted to see the finished SQL.

 

[zozzz]

honestly I say?
I want best and fast way to filter in too much data

About 32 Field and 98000 ID

I'm very grateful if anyone can give me a sample

 

[isladogs]

That doesn't answer pbaldy's questions and so doesn't help us help you