Filter Form on Combo Variable

[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

 

[Marshall Barton]

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

Note 1) Instead of using the Filter property, you should
use the OpenForm method's WhereCondition argument fo all the
filtering conditions. If you were setting the Filter
property correctly, the Wherecondition string would look the
same.

Note 2) You should not try to use the wizard generated
DoMenuItem stuff, which is a clunky leftover from Access 1
and 2. The RunCommand method was intorduced way back then
as the replacement, but is very rearely the best way to do
anything. A key defficiency of those things is that they
have no way to specify which form they are supposed to
operate on. Instead of those things, hunt around for form
properties or method arguments that do what you need.

From what you've posted, I think the code you 're looking
for would look like:

Dim strWhere As String

If Not IsNull(Me.cmblistlookup) Then
strWhere = strWhere & " And " & Me.cmblistlookup
End If
If Not IsNull(Me.cmbstkidlookup) Then
strWhere = strWhere & " And " & Me.cmbstkidlookup
End If

DoCmd.OpenForm "frmcounts", , , "STATUS<>'Complete' " _
& strWhere

That code assumes that the combo box's bound column is the
selected items numeric foreign key field.

 

[Daryl S]

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)