Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-20-2017, 01:22 AM   #1
shafara7
Newly Registered User
 
Join Date: May 2017
Posts: 118
Thanks: 23
Thanked 0 Times in 0 Posts
shafara7 is on a distinguished road
Combobox filter using VBA

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?
Attached Images
File Type: png Location Filter.PNG (18.0 KB, 123 views)

shafara7 is offline   Reply With Quote
Old 06-20-2017, 04:50 AM   #2
Orthodox Dave
Home Developer
 
Orthodox Dave's Avatar
 
Join Date: Apr 2017
Location: London UK
Posts: 218
Thanks: 25
Thanked 51 Times in 50 Posts
Orthodox Dave will become famous soon enough
Re: Combobox filter using VBA

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
__________________
Dave

The simplest solutions
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
can be the hardest to find
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Orthodox Dave is offline   Reply With Quote
The Following User Says Thank You to Orthodox Dave For This Useful Post:
shafara7 (06-21-2017)
Old 06-21-2017, 09:02 PM   #3
shafara7
Newly Registered User
 
Join Date: May 2017
Posts: 118
Thanks: 23
Thanked 0 Times in 0 Posts
shafara7 is on a distinguished road
Re: Combobox filter using VBA

Omg you understand it perfectly and you are absolutely right!
The codes works!
Thank you very much!!

shafara7 is offline   Reply With Quote
Reply

Tags
combobox , filter , subform , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Filter a combobox in a subform with another combobox from main form jjake Forms 3 12-18-2015 05:44 AM
filter combobox based on another combobox vb.net spider2vb VB.NET 0 10-06-2013 11:49 PM
Filter One Combobox based on selection in another combobox rachelkm2 Modules & VBA 4 12-13-2010 09:48 PM
Filter Subform ComboBox based on Master Form ComboBox selection azammalick Forms 1 12-16-2009 12:27 PM
How can I filter one combobox based on another combobox selection? aeleech Forms 2 04-26-2005 08:45 AM




All times are GMT -8. The time now is 07:20 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World