Filter listbox with combobox depending on checkbox

[Lynx2x]

Hello to everyone!

I have a little problem here. I have a form wich includes listbox of employees. When I click on them, it shows his or her data (date of birth,address,etc...). For every employee there are also three diferent checkboxes - if he is regularly employed, temporaly employed or student.
So now what I need is to filter listbox of employees - only student or only regular or only temporar or all.
I was wondering if I can do that with combobox, but I really dont know how. I am prety new to VBA.

Hope one of you can help me and I thank you all in advance.

 

[pr2-eugin]

Hello Lynx2x, Welcome to AWF..

It depends how your data is stored in your table.. Does your table have three fields(regularEmployed, tempEmployed, Student) with Yes/No type? Or it is it one field with different Number or Text in it?

 

[Lynx2x]

First thanks for the reply

And yes, I have three fields with yes/no.

 

[pr2-eugin]

Then you need to create/design a Form that will have four checkboxes, or IMO an Option Group and then you can have the options as,
1 - Regularly Employed
2 - Temporarily Employed
3 - Student
4 - All

Using VBA you construct a SELECT Query that will for the RowSource of the ListBox.. Something along the lines of..

Private Sub buttonName_Click()
    Dim sqlQry As String
    sqlQry = "SELECT theFieldsYouWant FROM theTable"
    Select Case Me.OptionGroupName
        Case 1 
            sqlQry = sqlQry & " WHERE RegEmployed = True"
        Case 2
            sqlQry = sqlQry & " WHERE TempEmployed = True"
        Case 3
            sqlQry = sqlQry & " WHERE Student = True"
        Case Else 
            sqlQry = sqlQry & " WHERE RegEmployed = True Or TempEmployed = True Or Student = True"
    End Select
    Me.ListBoxName.RowSource = sqlQry
    Me.ListBoxName.Requery
End Sub

The above is considering there is a button that will refresh the listbox..

 

[Lynx2x]

Ok, I think we are close. But I also think there is a litlle misunderstanging.

I have list of employees.
I have 3 checkboxes: Regular, Temporar, Student.
Each employee has proper tick on checkbox (is he regular,temporar or student)

Now I also have combo box in wich I've typed values myself...so regular,temporar,student and all. And after update event on this combo box, program should filter first list box based on what tick employee has in check box.

I've tried your code but it is not working properly...when I chose wich one do I want to see in my list box it shows me all employees no mather what tick they have (reg,temp, student)....

I hope I'm not complicating to much and I really apreciate any of help...so thank you in advance.

 

[pr2-eugin]

The one I have given was under the assumption that it is a Option Group.. So is your code looking something like this now?

Private Sub comboBoxName_AfterUpdate()
    Dim sqlQry As String
    sqlQry = "SELECT theFieldsYouWant FROM theTable"
    Select Case Me.comboBoxName
        Case "Regular" 
            sqlQry = sqlQry & " WHERE RegEmployed = True"
        Case "Temporary"
            sqlQry = sqlQry & " WHERE TempEmployed = True"
        Case "Student"
            sqlQry = sqlQry & " WHERE Student = True"
        Case Else 
            sqlQry = sqlQry & " WHERE RegEmployed = True Or TempEmployed = True Or Student = True"
    End Select
    Me.ListBoxName.RowSource = sqlQry
    Me.ListBoxName.Requery
End Sub

 

[Lynx2x]

Sorry for not replying fast, but I have afternoon school. I think that my code is exactly the same as you listed in last reply. But something still not right, because whatever I select (reg,temp,student or all) it is showing me all of them.

I cant tell at the moment, because I'm at school till 22pm. I will tell more as soon as I come home or tomorow morning at work.

Thank you very very much for your help. I'm very grateful.

 

[pr2-eugin]

Thats alright.. Check the Rowsource Type of the ComboBox.. Is it a Table/Query or Value List? If it is Table/Query then check the RowSource, Column Count, Bound column information.. Those might be causing some trouble.. Alternatively.. Try debugging..

Private Sub comboBoxName_AfterUpdate()
    Debug.Print Me.comboBoxName
[COLOR=SeaGreen]'    Dim sqlQry As String
'    sqlQry = "SELECT theFieldsYouWant FROM theTable"
'    Select Case Me.comboBoxName
'        Case "Regular"
'            sqlQry = sqlQry & " WHERE RegEmployed = True"
'        Case "Temporary"
'            sqlQry = sqlQry & " WHERE TempEmployed = True"
'        Case "Student"
'            sqlQry = sqlQry & " WHERE Student = True"
'        Case Else
'            sqlQry = sqlQry & " WHERE RegEmployed = True Or TempEmployed = True Or Student = True"
'    End Select
'    Me.ListBoxName.RowSource = sqlQry
'    Me.ListBoxName.Requery[/COLOR]
End Sub

See what values you get for each of the status in the Immediate Window.. (Ctrl+G).. Then you can copy and paste it to avoid misspelling of the values..

 

[Lynx2x]

Good morning

So, I've gave it another look and found mistake (misstyping :banghead and now it is working great. Thank you thousand times
Although I dont know how to sort it alphabeticly by surnames. I've tried ordering by like you can see, but when I do that it shows empty list box.
sqlQry = "SELECT * FROM qryOsebjeNovo11 ORDER BY qryOsebjeNovo11.SURNAME"

 

[pr2-eugin]

Well that's good news..

Although it normally does not affect, I would always use the specific column names to be included in the Query.. This will not only set the columns right, but also helps you to understand what is included where.. So try including the columns and see if that helps.. If not, create a Query using the Query builder switch it to SQL view and then copy paste to avoid any misspelling..

 

[Lynx2x]

Ok, so I've made query, changed it to sql view and this is what I've got

SELECT tblOsebje.ID, tblOsebje.SURNAME, tblOsebje.NAME FROM tblOsebje ORDER BY tblOsebje.SURNAME, tblOsebje.NAME;

Now I've copied this to VBA code, so it was like:

sqlQry = "SELECT tblOsebje.ID, tblOsebje.SURNAME, tblOsebje.NAME FROM tblOsebje ORDER BY tblOsebje.SURNAME, tblOsebje.NAME"

But when I chose from combo box (REG,TEMP,STUDENT,ALL) it shows me nothing....blank list box. But if I remove ORDER BY tblOsebje.SURNAME, tblOsebje.NAME , then it is working it is just not alphabeticly ordered by surnames.

Any ideas why is that? Thank you

 

[pr2-eugin]

Okay you might be constructing the SQL string wrong.. Order By comes after Where.. So try this..

Private Sub comboBoxName_AfterUpdate()
    Dim sqlQry As String
    sqlQry = "SELECT tblOsebje.ID, tblOsebje.SURNAME, tblOsebje.NAME FROM tblOsebje"
    Select Case Me.comboBoxName
        Case "Regular" 
            sqlQry = sqlQry & " WHERE RegEmployed = True"
        Case "Temporary"
            sqlQry = sqlQry & " WHERE TempEmployed = True"
        Case "Student"
            sqlQry = sqlQry & " WHERE Student = True"
        Case Else 
            sqlQry = sqlQry & " WHERE RegEmployed = True Or TempEmployed = True Or Student = True"
    End Select
    Me.ListBoxName.RowSource = sqlQry [COLOR=Red][B]& " ORDER BY tblOsebje.SURNAME, tblOsebje.NAME"[/B][/COLOR]
    Me.ListBoxName.Requery
End Sub

 

[Lynx2x]

Ohh..I didn't know that order by is after where...ok...good to know something new
And yes...now it is working perfectly

Thank you very much again and also I hope this thread will help someone else

Have a nice day.

 

[pr2-eugin]

Glad you have it sorted.. Good Luck..