Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Boxes, Applyfilter (97)

    I've just spent a couple of hours doing a search, but couldn't figure out the best way to approach this..

    7 combo boxes above a subreport. How to allow the user to select one, (or more) items to Filter on.

    A) Link the subreport to a query would seem the easiest way....? (But then how to handle blank combo boxes) I tried using '*' earlier today
    [img]/forums/images/smilies/cool.gif[/img] The microsoft suggested way, (But this was only for 2 combo's not 7 or more) and again was complex just for 2 combo's
    C) Francois posted some code in post 99018 (seems complex)

    Anyone know a better way to filter some data, based on 1 or more combo's, from a table. ?

    I believe i can also do, DoCmd.Applyfilter [Field1] = "Like Forms!Combo1]
    but im not sure, of the syntax, whether i can have multiple criteria, and what to do about wanting all records (ie: a blank combo=all records)


    Regards

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Combo Boxes, Applyfilter (97)

    Are you really working with a report, or is this a form set up to look like a report? The reason I ask is you can't change combo boxes on a report.

    The most effective way to do this is to either build your SQL string for the query dynamically in VBA, or do the same with a filter, and then do an apply filter. In either case, VBA is the only effective way of making it work - macros are simply too limited to do text/string manipulations on the fly.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Boxes, Applyfilter (97)

    I assume that the combo boxes are on a form. As Wendell remarked, the user can't do anything with combo boxes on a report. I suppose you want to use to filter a subform of the form, or perhaps a report. If you really want to filter a subreport, it's going to be more complicated.
    If the combo boxes correspond to fields of the same type, the code could be relatively compact; I'll give an example below. But if they correspond to fields of different types (text, numeric, date), you'll have to handle each combo box separately, so the code will be much longer.

    Here is an example for 7 combo boxes named cboFilter1 to cboFilter7. They correspond to text fields; the names of the text fields have been entered (manually) in the Tag property of the combo boxes. The code to set the filter of a report "ReportName" could look like:

    <font face="Georgia">Private Sub cmdApplyFilter_Click()
    Dim strFilter As String
    Dim i As Integer

    On Error GoTo Err_Handler

    ' Build filter String
    For i = 1 To 7
    If Trim(Me("cboFilter" & i)) <> "" Then
    strFilter = strFilter & Me("cboFilter" & i).Tag & " = '" & Me("cboFilter" & i) & "' And "
    End If
    Next i

    If strFilter <> "" Then
    ' Strip Last " And "
    strFilter = Left$(strFilter, Len(strFilter) - 5)
    ' Set the Filter property
    Reports("ReportName").Filter = strFilter
    Reports("ReportName").FilterOn = True
    Else
    Reports("ReportName").Filter = ""
    End If
    Exit Sub

    Err_Handler:
    MsgBox Err.Description, vbExclamation
    End Sub</font face=georgia>

  4. #4
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes, Applyfilter (97)

    AS per usual, Hans, you work out what i Actually want to do, rather than my ramblings <img src=/S/burga.gif border=0 alt=burga width=40 height=15>

    yea, its a subform, and the combo boxes are on a form above.

    I will have another bash tomorrow

    thanks

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes, Applyfilter (97)

    Hans, as per your suggestion, i've linked the combo's to a report. As a test, i have 2 combo's. and have changed the tag property to reflect the name of the combo

    however, im getting an error message on clicking the button.

    'The report named 'Orders' you entered is misspelled or refers to a report that isn't open or doesn't exist'

    yet the report is there. i can open it from another button. I tried the posted way to open the report, and this other way, error message is the same.


    here is the code again:-

    Private Sub Command21_Click()
    Dim strFilter As String
    Dim i As Integer

    On Error GoTo Err_Handler

    ' Build filter String
    For i = 1 To 2
    If Trim(Me("cboFilter" & i)) > "" Then
    strFilter = strFilter & Me("cboFilter" & i).Tag & " = '" & Me("cboFilter" & i) & "' And "
    End If
    Next i

    If strFilter > "" Then
    ' Strip Last " And "
    strFilter = Left$(strFilter, Len(strFilter) - 5)
    ' Set the Filter property
    Reports!Orders.Filter = strFilter
    Reports!Orders.FilterOn = True
    DoCmd.OpenReport "Orders", acViewPreview
    Else
    Reports("Orders").Filter = ""
    End If
    Exit Sub

    Err_Handler:
    MsgBox Err.Description, vbExclamation


    End Sub

  6. #6
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes, Applyfilter (97)

    Also tried :-

    Reports ("Orders").Filter = strFilter

    This is the bit that is causing the problem. I'm using Access xp at home,

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Combo Boxes, Applyfilter (97)

    Your code looks ok in referencing the report "orders", however what the error message is saying is that the report is not open. Your 2 lines of code appear before the OpenReport line of code.

    Can't you just setup a criteria and use this in the OpenReport command?

    Another option is in the OnOpen event of the report you could put that filter code.
    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  8. #8
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes, Applyfilter (97)

    Patt, im trying

    DoCmd.OpenReport "Orders", acViewPreview, , strFilter

    but its not working.... it opens the report. but there's no filtering being done.. its just coming up blank. I'm trying to find a way to see what the string is set to..
    but i don't know how to set a breakpoint in the code, i know i can do the cntrl g thing and do ?strFilter. if i can get it to stop there. or perhaps i can display it.

  9. #9
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes, Applyfilter (97)

    Well, been at it hours now.. [img]/forums/images/smilies/smile.gif[/img]

    Figures out how to stop the code, and it looks like the second part of the filter is not working correctly, instead of the filter being
    Country = France

    its coming up
    Country = 12055 (which is France's key from the Orders table)


    So somehow the last bit of the code isn't working

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Combo Boxes, Applyfilter (97)

    Breakpoint the code when building up the filter string, you will soon find out what's wrong.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  11. #11
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes, Applyfilter (97)

    Well,... very strange... Hans' code would not work correctly at home using Access xp, and the northwind Orders table...
    but works ok on my database at work,. !!!

    anyways, the latest update is that the combo boxes 1 to 7 work fine , but my last box is a date. I've tried all sorts of combinations to make it work, and my current code is this. (ive also been trying to get it to GROUP BY ... but i guess SQL thingamie and me just dont get on ! (or maybe this command only sorts a simple WHERE clause and not the full blow SQL way ?) <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

    Ive tried all sorts of combinations for the Date.. ('# xxxxxx '), and then saw this thing about US date format only, or Datevalue ('xxxx') but it still don't work

    HANS , HANS ! Where for art tho ? <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>


    Private Sub Short_Report_Click()
    Dim strFilter As String
    Dim i As Integer

    On Error GoTo Err_Handler
    strFilter = ""

    For i = 1 To 7
    If Trim(Me("cboFilter" & i)) > "" Then
    strFilter = strFilter & Me("cboFilter" & i).Tag & " = '" & Me("cboFilter" & i) & "' And "
    End If
    Next i

    If strFilter > "" Then
    strFilter = Left$(strFilter, Len(strFilter) - 5)
    End If

    If Len(cboFilter8) > 0 Then
    strFilter = strFilter & "Entered = DateValue('" & Me("cboFilter8") & "');"
    End If
    'strFilter = strFilter & " GROUP BY 'IA_Number'"
    If Me!Combo99 = "Stop" Then
    Stop
    End If

    DoCmd.OpenReport "caSearch", acViewPreview, , strFilter

    Exit Sub

    Err_Handler:
    MsgBox ERR.Description, vbExclamation

    End Sub

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Combo Boxes, Applyfilter (97)

    Some observations.
    You cannot put a GROUP BY into the criteria of the OpenReport command, it is a where clause without the word WHERE.
    Your command:
    strFilter = strFilter & "Entered = DateValue('" & Me("cboFilter8") & "');"
    should be changed to:
    strFilter = strFilter & "Entered = #" & Format(Me("cboFilter8"),"mm/dd/yy") & "#;"

    An alternative way to do this whole thing would be to build a query on the fly and you would not need a Criteria in the OpenReport command. It would satisfy your need to use a GROUP BY and can be created by using the CreateQueryDef function.

    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  13. #13
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes, Applyfilter (97)

    Thanks for the info patt. I will look into this Query thing you mention.. I'm sure i've seen 'something' like it in a book i have. It looked a 'bit' like this:-

    Sqlstring = "Select xxxx FROM xxxx' etc etc
    rst.Sqlstring

    something like that.. i will try to find out some more.

    Now if access was friendly, it would allow any valid SQL string after any command. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Access help is its usual unfriendly self as well. With what this program cost, you should get video instruction on how to do anything <img src=/S/angel.gif border=0 alt=angel width=15 height=21>

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo Boxes, Applyfilter (97)

    No, you didn't see that. A querydef has a SQL property and a DAO recordset has a recordsource property, which may be what your'e thinking of. Alternatively, an ADO recordset allows you to set the CommandText property with a SQL string.

    And Access is no more unfriendly than any other developer's tool. You have to know what you're doing in order to get the most out of it. That's why there is such a huge market in programming books of all sorts, including Access. If you want unfriendly, try doing this stuff in Visual Basic. <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>
    Charlotte

  15. #15
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes, Applyfilter (97)

    Pats Suggestion shown here still generates a syntax error. (and i'm still trying to work out how to do it a different way so i can use GROUP BY sql statement)

    Anyone know the syntax ?

    ------------------------------------------------------------------------
    Some observations.
    You cannot put a GROUP BY into the criteria of the OpenReport command, it is a where clause without the word WHERE.
    Your command:
    strFilter = strFilter & "Entered = DateValue('" & Me("cboFilter8") & "');"
    should be changed to:
    strFilter = strFilter & "Entered = #" & Format(Me("cboFilter8"),"mm/dd/yy") & "#;"

    An alternative way to do this whole thing would be to build a query on the fly and you would not need a Criteria in the OpenReport command. It would satisfy your need to use a GROUP BY and can be created by using the CreateQueryDef function.

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
  •