Filter Form on Combo Variable

  • Thread starter Thread starter Aaron
  • Start date Start date
A

Aaron

Hi,

I am needing some help with the code below. It is not filtering what I need
it to and not sure what to do.
I have two comdo boxes that I have the user clicking on to filter the form.

Dim strlistlookup As String
Dim strstkidlookup As String

strlistlookup = cmblistlookup.Value
strstkidlookup = cmbstkidlookup.Value

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

DoCmd.OpenForm "frmcounts", , , "STATUS<>'Complete' "
Me.Form.Filter = (LISTTYPE = strlistlookup)
Me.Form.Filter = (MAXSTKID = strstkidlookup)
Me.Form.FilterOn = True


Thanks in advance for your help!
Aaron
 
Aaron -

Three issues I see. After opening the form frmcounts, then you are setting
trying to set the filter of the current form - I assume you want the filter
on the newly-opened form? Second, when you use two 'Me.Form.Filter ='
statements in a row, the second one replaces the first. For a filter with
two pieces, you need to create one filter with the AND joiner. Finally,
when building a filter string, you need to pass in the values by adding the
delimeters within the double-quotes and append the values outside of the
double-quotes. It will look more like this:

DoCmd.OpenForm "frmcounts", , , "STATUS<>'Complete' AND " & _
"[LISTTYPE] = '" & strlistlookup & "' AND [MAXSTKID] = '" & _
strstkidlookup & "'")

If you are struggling with this, Dim a string variable and use Debug.Print
to help find the issue, like this:

Dim txtFilter As String
txtFilter = "STATUS<>'Complete' AND " & _
"[LISTTYPE] = '" & strlistlookup & "' AND [MAXSTKID] = '" & _
strstkidlookup & "'"
Debug.Print txtFilter
DoCmd.OpenForm "frmcounts", , ,txtFilter)
 
Back
Top