second acCmdFilterMenu doesnt work as expected

[JMETSKER]

I have a continuous form with multiple fields based on a query. In the header I have some filter buttons for each field. when I click the first one and select from the list it filters correctly. when I click the second filter I don't get the list of items with check boxes, it just displays the "A to Z", "Z to A", and "Text Filter" in the dropdown. here is the code:

Private Sub POSITION_TYPE_FLT_Click()
DoCmd.GoToControl "POSITION_TYPE"
DoCmd.RunCommand acCmdFilterMenu
End Sub

Private Sub DATE_ISSUED_FLT_Click()
DoCmd.GoToControl "DATE_ISSUED"
DoCmd.RunCommand acCmdFilterMenu
End Sub

[June7]

Won't always get check list in quick filter for every field in a query. Can't remember circumstances that prevent - perhaps if it is a field with lookup or calculated field.

I have never used acCmdFilterMenu in VBA. I prefer building filter criteria string. Review http://allenbrowne.com/ser-62.html

[JMETSKER]

you never used this function yet you replied.

[JMETSKER]

I would like a better answer but the answer is that query filters are not "sticky". if you base it off a table then you are ok. I might be able to rework everything so that I can do this. anyone got something useful to add?

[June7]

I have used the quick filter manually but never coded it in VBA, probably because it doesn't always work nice, as you are experiencing.

Is your query joining tables?

[JMETSKER]

YES. a bunch of joined tables that I solved through dlookup. last stupid problem as I am burning out now. how do I set a filter on load to a variable (which is something I dlookup)

Private Sub Form_Load()
EPS_YEAR = DLookup("[EPS_YEAR]", "[EPS_YEAR]")
Debug.Print EPS_YEAR 'right now this returns 2018 as expected... problem is in next line as I cant remember how to write it
Me.Filter = ([CTRL#_ISSUE_E5_SGT].[EPS_YEAR] = EPS_YEAR)
End Sub

[June7]

Me.FilterOn = False
Me.Filter = "EPS_YEAR=" & EPS_YEAR
Me.FilterOn = True

Suggest VBA variable not have same name as field or don't use variable in this case:

Me.Filter = "EPS_YEAR=" & Nz(DLookup("[EPS_YEAR]", "[EPS_YEAR]"), Year(Date()))

I would also not give field and table exact same name. EPS_YEAR has only 1 record?

Review http://allenbrowne.com/ser-62.html

[JMETSKER]

right... one record. user selects it on the first screen of the DB and then everything from there on is filtered to that input. just fyi... its a text field. so this works:

Private Sub Form_Load()
Dim EPS_YEAR As String
EPS_YEAR = DLookup("[EPS_YEAR]", "[EPS_YEAR]")
Me.FilterOn = False
Me.Filter = "EPS_YEAR = '" & EPS_YEAR & "'"
Me.FilterOn = True
End Sub

everything works now.

solution:
base your form off a table not a query. in my case this required some dlookups to get some additional tidbits of info and filtering on load to manipulate the table down to what I needed but all in all not bad. thanks for the help.