Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q208/5/29.ASP>This article</A> 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
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

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

    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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) & <font color=red>"" _
    And ""</font color=red>

    What I did was remove the line break so that " And " was all on the same line and it worked fine.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    All right!!

    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

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: All right!!

    I changed that in my code to acViewPreview as well because I figured it was an error.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    You know...

    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
    Attached Files Attached Files

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: All right!!

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    It wouldn't be a thread without your presence...

    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

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: It wouldn't be a thread without your presence...

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hawk eyes

    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
    Attached Files Attached Files

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: It wouldn't be a thread without your presence...

    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?
    Attached Files Attached Files

  12. #12
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: It wouldn't be a thread without your presence...

    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

  13. #13
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sparked my imagination...

    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.

    <pre>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
    </pre>


    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

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sparked my imagination...

    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?
    Attached Files Attached Files

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorry, let's do it again:

    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
    <pre>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
    <font color=red>strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
    IF intCounter = 1 then
    & " IN(" & Chr(34) & a & Chr(34) & ") And "</font color=red>
    Else
    <font color=red>& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "</font color=red>
    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</pre>


    Btw, are you the same Stewart Tanner mentioned <A target="_blank" HREF=http://www.uq.edu.au/media/download/uni-news/un498.pdf> here</A>?
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •