Filter based on two combo box selections

[jeremy.lankenau]

Hi all,

I'm still pretty new to access, but tried to search the forum for a solution for my issue but didn't find anything that worked. I'm brand new to VBA and am still learning how it works.

I currently have a combo box that filters form based on specialist (an employee using the system), and this works fine:

Private Sub FilterSpecialist_AfterUpdate()
Me.Filter = "SpecialistAssigned = '" & Me.FilterSpecialist & "'"
Me.FilterOn = True

End Sub

When Specialist Assigned name is selected, it only shows their cases (records of the form that are assigned to them).

However, now I'm trying to also filter based on only the selected Specialists Open cases. I have a combo box at the bottom of the form that saves whether the Case is Open or Closed, (creatively) called CaseOpenClosed:

Private Sub FilterSpecialist_AfterUpdate()
Me.Filter = ("SpecialistAssigned = '" & Me.FilterSpecialist & "'" And CaseOpenClosed = "Open")
Me.FilterOn = True

End Sub


I've tried this based on me searching the forum but it doesnt filter at all anymore, and no error either. I've tried other ways but get errors. What is wrong with this?

I've been able to figure out how to do lots in VBA the last month or two, but this one is eluding me as I'm still new to it.

As an aside, what is a good guide to VBA that y'all would recommend? I'm at the point now where I wouldnt even mind picking up a good book to learn the nuts and bolts instead of scouring the forums and not understanding the solution I put in (even though happy it works). I want to know why

Thanks for your help!
Jeremy

 

[jdraw]

2 great links for Learning Access
http://www.functionx.com/vbaccess/
http://allenbrowne.com/casu-22.html

Good reference link to learn Table design, relationships and normalization
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

 

[jeremy.lankenau]

Thanks for the resources, and I will def be checking them out.

However, any idea on the current issue above as I have a deadline of CoB today and the boss-man does not want the Specialists to see the Closed cases

 

[jdraw]

Tell us more about CaseOpenClosed , a jpg may help.

 

[Cronk]

If you had put a break point on your code and examined the filter string being generated, you would have seen the error.

The filter string should read
Me.Filter = ("SpecialistAssigned = '" & Me.FilterSpecialist & "' And CaseOpenClosed = 'Open'")

 

[jeremy.lankenau]

That did it, thanks Cronk. Duly noted regarding use of break points.