Filtering a Report from a Pop-Up Form (2000)

Topic

New Reply

This article from Microsoft shows how to create a report that you can filter dynamically in Print Preview by selecting filter criteria from a pop-up form.
I’ve tried to implement it (see attached mdb) but when, according to the instructions in the article, I select “BC” in the Region combo box, and then click the Set Filter button. I get the following error message:
Run-time error ’13’:
Type mismatch

Reply | Quote

Replies

It would be easier to post the code here. It is too much of a bother to download databases.

My expectation is that you didn’t form your string that holds the WHERE criteria to include apostrophes around “BC”. How you form the string depends on whether it is a numeric or a text field, so depending on the situation, you can have:

If Region is numeric, this is okay: Region=123
If Region is text, then you must have: Region=’BC’

An easy way is to use the BuildCriteria function, this will automatically put in the right delimiters, depending on the field type you give it.

Reply | Quote

The way the code that the article showed to type in the OnClick event of the Set Filter command button caused extra quotes to be inserted when you moved off the lines. I am referring to the red portion of the following code:

strSQL = strSQL & “[” & Me(“Filter” & intCounter).Tag & “] ” _
& ” = ” & Chr(34) & Me(“Filter” & intCounter) & Chr(34) & “” _
And “”

What I did was remove the line break so that ” And ” was all on the same line and it worked fine.

Reply | Quote

Thanks Judy.
If I look up the OpenReport Method in VBA on-line help, I see the View argument has three intrinsic constants available: acViewDesign, acViewNormal (default) and acViewPreview, I don’t see the constant A_PREVIEW used in Private Sub Form_Open(Cancel As Integer) of the article.
Do you know why they used A_PREVIEW?
Ciao

Reply | Quote

I changed that in my code to acViewPreview as well because I figured it was an error.

Reply | Quote

The odd thing is that I left A_PREVIEW as is in my code and it still works.
How come, when, in design view, I use CTRL+A to supposedly select all the items in the frmFilter form, the Filter3, Filter4 and Filter5 combo boxes don’t get selected?
Ciao

Reply | Quote

Hi,
A_PREVIEW is the old version of the intrinsic constant acViewPreview. It should continue to work in newer versions of Access but you’re better off using the new version (not that I’m suggesting MS would remove support for the old version without warning….).
Hope that helps.

Reply | Quote

But what about the second part of my reply to Judy:
How come, when, in design view, I use CTRL+A to supposedly select all the items in the frmFilter form, the Filter3, Filter4 and Filter5 combo boxes don’t get selected?
Ciao

Reply | Quote

Actually, they do get selected (as you should see if you select all, then move them) they just don’t appear to. I think it’s because the labels are directly underneath (and the same size as) the comboboxes, so the selection ‘highlighting’ of the 2 controls is cancelling itself out, if you see what I mean. Try moving the labels and you should be able to see the controls being selected.
Hope that helps.

Reply | Quote

Yep, I used Format, Size and Format, Align for the combo boxes and buttons on frmFilter once I created them and didn’t realize the labels(being transparent) were covering the combo boxes. Still one thing leaves me curious:
if, e.g., I move the label(Label5) associated with the Filter3 combo box away from Filter3(see attachment) and then go into Form View, the caption(Combo4: ) of the label appears on the form. If I then select Label5, select the Menu command: Format, Bring to Front and position Label5 back onto the Filter3 combo box I would expect the label caption to appear superimposed on Filter3 once in Form View. Why isn’t it so?
Ciao

Reply | Quote

I’ve tried changing the Filter1 combo box into a list box(see attachment) and set its Multi Select property to Extended but how should I change the code building the strSQL string in order to be able to select multiple Company Names?

Reply | Quote

I don’t have access 2000 but the 97 method would be something like this.
(straight from the help)

The following example prints the value of the bound column for each selected row in a Names list box on a Contacts form. To try this example, create the list box and set its BoundColumn property as desired and its MultiSelect property to Simple or Extended. Switch to Form view, select several rows in the list box, and run the following code:

Sub BoundData()
Dim frm As Form, ctl As Control
Dim varItm As Variant

Set frm = Forms!Contacts
Set ctl = frm!Names
For Each varItm In ctl.ItemsSelected
Debug.Print ctl.ItemData(varItm)
Next varItm
End Sub

Reply | Quote

I used your suggestion to achieve being able to select multiple Company Names.(see attachment)
Problem is I think I need to build strSQL using an if condition to take into account the fact that the Filter1 list box can now have multiple values but I get a Syntax Error when I click the “Set Filter” button.
Is it possible to insert an if condition into the expression building the strSQL string and, if so, where’s the syntax error?
Ciao
Btw, are you the same Stewart Tanner mentioned here?

Reply | Quote

I only have access 97 loaded on my PC so couldn’t look at your database, but have tried to provide you with an answer anyway.

Look at the following code.

Private Sub butBuildFilter_Click()
    
    Dim ctl As Control
    Dim varItm As Variant
    Dim MyCriteria As String
    Dim ArgCount As Integer
    'initialise values
    MyCriteria = ""
    ArgCount = 0
    
    Set ctl = lstData
    For Each varItm In ctl.ItemsSelected
        If Not ArgCount = 0 Then ' previous items have been added to the list
            'Substitute your field name for the word FIELDNAME
            MyCriteria = MyCriteria & " or [Fieldname] = '"  _
                               & ctl.ItemData(varItm)  & "'"
            ArgCount = ArgCount + 1
        Else
            'Substitute your field name for the word FIELDNAME
            MyCriteria = "[Fieldname] = '"  _
               & ctl.ItemData(varItm) & "'" 'first item added.
            ArgCount = ArgCount + 1
        End If
    Next varItm
    
    'you now have a where clause that you can use to 
    'open a report, form etc, or add to a SQL
    'string for whatever use.  Or a string that you can 
    'use as a filter.
    MsgBox MyCriteria

End Sub

Your hyperlink asking if I’m the same Stewart Tanner doesn’t go anywhere, so I can’t answer that.

Edited by Charlotte to eliminate horizontal scrolling

Reply | Quote

I used your suggestion to achieve being able to select multiple Company Names.(see attachment)
Problem is I think I need to build strSQL using an if condition to take into account the fact that the Filter1 list box can now have multiple values but I get a Syntax Error (related to the code in red in the Private Sub Set_Filter_Click() below) when I click the “Set Filter” button.
Is it possible to insert an if condition into the expression building the strSQL string and, if so, where’s the syntax error?
Ciao

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer, a
Dim frm As Form, ctl As Control
    Dim varItm As Variant

    Set frm = Forms!frmFilter
    Set ctl = frm!Filter1
    
    For Each varItm In ctl.ItemsSelected
        a = ctl.ItemData(varItm) & "," & a
    Next varItm

  For intCounter = 1 To 5
      If Me("Filter" & intCounter)  "" Then
     strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
            IF intCounter = 1 then
              & " IN(" & Chr(34) & a & Chr(34) & ") And "
              Else
            & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
            End If
              
      End If
  Next

  If strSQL  "" Then
     ' Strip Last " And ".
     strSQL = Left(strSQL, (Len(strSQL) - 5))

     ' Set the Filter property.
     Reports![rptCustomers].Filter = strSQL
     Reports![rptCustomers].FilterOn = True
   End If
End Sub

Btw, are you the same Stewart Tanner mentioned here?

Reply | Quote

ok, this should fix it.

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer, a
Dim frm As Form, ctl As Control
Dim varItm As Variant
Set frm = Forms!frmFilter
Set ctl = frm!Filter1

For Each varItm In ctl.ItemsSelected
    a = ctl.ItemData(varItm) & "," & a
Next varItm

For intCounter = 1 To 5
    If Me("Filter" & intCounter)  "" Then
        strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] "
          If intCounter = 1 Then
            strSQL = strSQL & " IN(" & Chr(34) & a & Chr(34) & ") And "
        Else
            strSQL = strSQL & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) _
& " And "
        End If
    End If
Next

If strSQL  "" Then     ' Strip Last " And ".
    strSQL = Left(strSQL, (Len(strSQL) - 5))     ' Set the Filter property.
    Reports![rptCustomers].Filter = strSQL
    Reports![rptCustomers].FilterOn = True
End If

End Sub

I’m not the same Stewart Tanner, although it was interesting. I’m in Victoria and am a bit to old to be a Uni student.

Reply | Quote

I’ve tried putting a break point at the line
For intCounter = 1 To 5
in Private Sub Set_Filter_Click() to step through the procedure and notice that even if I select a couple of items in the Filter1 list box, the value assumed by Filter1 equals a zero-length string because the line
If Me(“Filter” & intCounter) “” Then
gets skipped.
Why doesn’t VBA acknowledge selecting items in the Filter1 list box?

Reply | Quote

Hi,
I think your problem lies in the fact that the Value property of a multi-select listbox is set to Null. Try something like:
Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer, a
Dim frm As Form, ctl As Control
Dim varItm As Variant

Set frm = Forms!frmFilter
Set ctl = frm!Filter1

For Each varItm In ctl.ItemsSelected
a = Chr(34) & ctl.ItemData(varItm) & Chr(34) & “,” & a
Next varItm
For intCounter = 2 To 5
If Me(“Filter” & intCounter) “” Then
strSQL = strSQL & “[” & Me(“Filter” & intCounter).Tag & “] ”
strSQL = strSQL & ” = ” & Chr(34) & Me(“Filter” & intCounter) & Chr(34) _
& ” And ”
End If
Next
If a “” Then
strSQL = strSQL & “[” & Me.Filter1.Tag & “] in (” & Left$(a, Len(a) – 1) & “)”
Else
strSQL = Left$(strSQL, Len(strSQL) – 5)
End If
With Reports![rptCustomers]
.Filter = strSQL
.FilterOn = True
End With
End Sub
Hope that helps.

Reply | Quote

all your super brain cooks up.

The If a “” Then line in Private Sub Set_Filter_Click() tests for inequality with a zero-length string yet you say the Value property of a multi-select listbox is set to Null. If I don’t select any item in Filter1 list box and click on the “Set Filter” button stepping through code, when I hover the mouse over “a” in the If a “” Then line, Data Tips says “a=Empty”
Are zero-length string, Null and Empty all equivalent?
Ciao

Reply | Quote

Hi,
No they’re not equivalent. But because you only want that code to run if there is a string in a, it has the right effect. A variant variable is empty if it hasn’t been initialised (which is the case if a hasn’t been assigned a value)
In all honesty, I had forgotten that a was a variant and was thinking of it as a string variable (luckily for me it works anyway! )

Reply | Quote

in the line

If Me(“Filter” & intCounter) < "" Then

what is "<". I don't suppose that you can save the attachment as a 97 db so I can look at it?

Reply | Quote

In the meantime Rory solved the problem. Thanks for your effort though because the solution uses your suggestion about the ItemsSelected property.
Ciao

Reply | Quote