Listbox filter VBA

[Neilster]

Hi Guys

I have a listbox with shows Name, login, logout ect. I have a textbox which filters the name using the below method.

Private Sub txtName_AfterUpdate()

Me!lstLogin.RowSource = "SELECT * FROM TblLoginTime Where [EmpName] Like '*" & Me.txtName & "*';"

End Sub

Which is fine however I then need to filter (AND) the login, logout ......so kind of like a cascading filert. or could I have a command button with the same effect that will filter what is selected in each text box??

Hope you can help.

 

[arnelgp]

dim strSelect As String

strSelect = "SELECT * FROM TblLoginTime Where [EmpName] Like '*" & Me.txtName & "*'"

if Me.Control1 & "" <> "" then
strSelect = strSelect & " And [field1ToFilter] = '" & Me.Control1 & "'"
end if

if Me.Control2 & "" <> "" then
strSelect = strSelect & " And [field2ToFilter] = '" & Me.Control2 & "'"
end if

.... etc.

Me!lstLogin.RowSource= strSelect

 

[Neilster]

Hi arnelgp

Thanks for the post, however this method isn't working are you suggesting the code to be like this?

strSelect = "SELECT * FROM TblLoginTime "

if Me.Control1 & "" <> "" then
strSelect = strSelect & " And [field1ToFilter] = '" & Me.Control1 & "'"
end if

if Me.Control2 & "" <> "" then
strSelect = strSelect & " And [field2ToFilter] = '" & Me.Control2 & "'"
end if

.... etc.

Me.lstLogin.RowSource= strSelect

And if so it fillters nothing. (-:

 

[arnelgp]

the code is a template, Me.Control1, control2 are just examples, so are field1ToFilter, and field2ToFilter. you need to fill this with correct control names from your form, and actual field name from your table.

 

[Neilster]

I understand that and have filled in both to the correct names as below, however it filters nothing. Am I missing something ?

Dim strSelect As String

strSelect = "SELECT * FROM TblLoginTime "

If Me.txtName & "" <> "" Then
strSelect = strSelect & " And [EmpName] = '" & Me.txtName & "'"
End If

If Me.txtLogin & "" <> "" Then
strSelect = strSelect & " And [LoginTime] = '" & Me.txtLogin & "'"
End If


Me.LstLogin.RowSource = strSelect

 

[arnelgp]

Private Sub txtName AfterUpdate()
Dim strSelect As String

strSelect = "SELECT * FROM TblLoginTime "

If Me.txtName & "" <> "" Then
strSelect = strSelect & " Where [EmpName] = '" & Me.txtName & "'"
End If

If Me.txtLogin & "" <> "" Then
if strSelect = "" then
strSelect = strSelect & " WHERE [LoginTime] = #" & Me.txtLogin & "#"
ELSE
strSelect = strSelect & " And [LoginTime] = #" & Me.txtLogin & "#"
END IF
End If


Me.LstLogin.RowSource = strSelect
END SUB


PRIVATE SUB txtLogin_AfterUpdate()
Call txtName_AfterUpdate
End Sub

i have to go for now, its lunch time here in the middle east

 

[Neilster]

OK thanks I've worked it so that it works on a click button instead of after update.

Thanks again for all your help.

 

[Neilster]

Hi arnelgp

Just one last question, when filterng is there anyway to make the other fields no visable, when i filter the name then the login date it filters that specific date and then filters that specific date in logout and logbackin also filters random dates.

Thanks for any help.