Solved Filter Subform by ComboBox (1 Viewer)

[Malcolm17]

Hello,

I have a table that logs all my users activities\changes etc and I would like to be able to filter by Employee or Action but I cannot quite get this right. I have my combobox grouped so I can see all my users but when I filter by a user I cannot see the whole table unfiltered - how can I see the whole table data please if the combobox is empty?

I have tried putting in a record with an empty username. I would like to use this in other areas of my database, so if you can show me this time then it will help for other bits that I want to do.

Small mockup attached as I obviously cannot attach the real thing.

Thank you

 

[theDBguy]

Hi. Quick question. Do you have to use macros? Can you use VBA?

 

[theDBguy]

You can give this a try.

 

[Malcolm17]

Hi. Quick question. Do you have to use macros? Can you use VBA?

Hi DBguy,

I prefer everything to be VBA, so if thats possible that would be great. I just picked up my example from a Youtube video.

 

[Malcolm17]

I have just seen your example using the macro, thats amazing!! Thank you!!

 

[theDBguy]

I have just seen your example using the macro, thats amazing!! Thank you!!

I trust you can convert it into VBA then? If not, just let us know. I thought you were bound to using macros only.

 

[Malcolm17]

Well I thought that I could but I don't seem to be able to - Please help!! :-(

 

[Mike Krailo]

In the Navigation Pane, right-click the macro that you want to convert, and then click Design View. On the Design tab, in the Tools group, click Convert Macros To Visual Basic. In the Convert Macro dialog box, select the options that you want, and then click Convert.

 

[Malcolm17]

The converted macro gives me

DoCmd.ApplyFilter "", "[UserName]=[Combo2] Or [Combo2] & """"=""""", "UserLog subform"

however it doesn't work.

I am working with the following code which works for filtering users but does not show all records when I select the blank.

[Forms]![Form1]![UserLogsubform].Form.Filter = "[Username] = '" & Me.Combo2 & "'"
[Forms]![Form1]![UserLogsubform].Form.FilterOn = True

Can you tell me how to get it to show all records please?

Also, when we get this to work, is it as simple as adding another combobox with the right code to filter the records down further (2 filters at the same time)?

Thank you.

 

[Malcolm17]

I have got this code to work for filtering the data from one combo box:

'Forms.Form1.UserLogsubform.Form.Filter = "[Username] = Forms.Form1.Combo2 Or Forms.Form1.Combo2 Is Null"
'Forms.Form1.UserLogsubform.Form.FilterOn = True
'Forms.Form1.UserLogsubform.Requery

And I got the following code for filtering the data from two unbound combo box, this code is attached to a button for use once the filters have been selected:

Dim strWhere As String
If Nz(Me.Combo2, "") <> "" Then
    strWhere = strWhere & "[UserName] = '" & Trim(Me.Combo2) & "' AND "
End If
If Nz(Me.Combo6, "") <> "" Then
    strWhere = strWhere & "[Action] = '" & Trim(Me.Combo6) & "' AND "
End If
    If strWhere <> "" Then
        strWhere = Left(strWhere, Len(strWhere) - 5)
        Me.UserLogsubform.Form.Filter = strWhere
        Me.UserLogsubform.Form.FilterOn = True
    Else
        Me.UserLogsubform.Form.Filter = ""
        Me.UserLogsubform.Form.FilterOn = False
    End If
Forms.Form1.UserLogsubform.Requery

Thank you everyone once again for all the ongoing help with my project

 

[theDBguy]

Congratulations! Good luck with your project.