Combobox filter using VBA (1 Viewer)

shafara7

Registered User.
Local time
Today, 09:40
Joined
May 8, 2017
Messages
118
I have a subform that I want to filter using a combobox.
The subform query is in the left side of the photo that I have attached and the result that supposed to show up on the subform when I clicked on the combobox is on the right side (in red box).

The problem is I couldn't get the subform to show up like that.
I am not good with logic so I can't comprehend how to make the subform appears like that.
When I choose Texas, it also shows the Billing No. for 103 and 104 but I don't want that. I just want it to show Billing No. 106.

Below is the code that I use:
Code:
Private Sub cboLocation_AfterUpdate()
    If Not IsNull(cboLocation.value) Then
            subDuty.Form.FilterOn = True
            subDuty.Form.Filter = " [StandardWorkLoc] = " & cboLocation.value & " OR [DeviantWorkLoc] = " & cboLocation &"  "
           
    Else
            subDuty.Form.FilterOn = False
    End If
End Sub

I also tried using the following codes, but the result will only show the records in Standard Work Location.
Code:
subDuty.Form.Filter = "IIf(IsNull([DeviantWorkLoc]), [StandardWorkLoc], [DeviantWorkLoc]) = " & cboLocation

Where did I do wrong here?
 

Attachments

  • Location Filter.PNG
    Location Filter.PNG
    18 KB · Views: 233

Orthodox Dave

Home Developer
Local time
Today, 08:40
Joined
Apr 13, 2017
Messages
218
I am trying to understand what you are trying to do, so bear with me.

According to the results you say you are expecting, it seems you want to only show billings for either the Standard Location or the Deviant Location but not both and that if there is a Deviant Location for the billing, you show the Deviant Location one, not the Standard.

If that is right, try this:
Code:
Private Sub cboLocation_AfterUpdate()
    If Not IsNull(cboLocation.value) Then
       subDuty.Form.Filter = " [DeviantWorkLoc] = " & cboLocation & " OR " ([StandardWorkLoc] = " & cboLocation.value & " AND [DeviantWorkLoc] = Null)"
       subDuty.Form.FilterOn = True
           
    Else
       subDuty.Form.FilterOn = False
    End If
End Sub
 

shafara7

Registered User.
Local time
Today, 09:40
Joined
May 8, 2017
Messages
118
Omg you understand it perfectly and you are absolutely right!
The codes works!
Thank you very much!!
:D
 

Users who are viewing this thread

Top Bottom