Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Phred is offline Competent Performer
    Windows XP Access 2010 (version 14.0)
    Join Date
    Dec 2009
    Posts
    246

    Urgent - Filter Sub Form in VB by button Filter Property

    Access 2007, not compiled, not split, Windows 7, Filter Property, I have never used filtering before, VBA skill levels moderate.



    I have a main form where the end user will schedule several hundred meals, or thousands, by date, usually a month at a time. The meals appear in the sub form once they are scheduled.

    Click image for larger version. 

Name:	Filter.JPG 
Views:	42 
Size:	56.0 KB 
ID:	6599

    The user might be in the middle of scheduling meals for a particular month and get interrupted. When they come back there are so many records they have a hard time finding where they left off. They want to be able to filter the sub form by date range. They would be filtering against the date field in the sub form "DateAssigned".

    As the sub form shows they started scheduling for March. But there may be 1,000 records and it is hard to find where they left off. What I need to do:

    The end user selects a date range in the two fields that they want to filter for. They click Filter Menus button. My code is a guess. The Filter Property in help or articles is not clear for me. Here is my code so far:


    Private Sub CmdFilterMeals_Click()

    Dim FilterStartDate As Date, FilterEndDate As Date

    FilterStartDate = Me.FilterDateStart

    FilterEndDate = Me.FilterDateEnd

    'Filter The Sub Form for DateAssigned between FilterStartDate and FilterEndDate. The nex line is what I need help with.

    me.filter = "[DateAssigned] >= #" & FilterStartDate & "# AND [DateAssigned] <= "# & FilterEnddate & ");"'"

    'It could also be a between statement.

    Me.Filter = True

    I have read every post I can find but nothing is clear so far.

    I have never asked for quick help before but it would be really appreciated.

    Phred

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You got the # delimiters around the first date value correct, not so much around the second.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Phred is offline Competent Performer
    Windows XP Access 2010 (version 14.0)
    Join Date
    Dec 2009
    Posts
    246

    Still doesn't filter.

    Wow! I was actually that close? Astounding! I must actually be learning.

    Ok, I implemented the corrected code below. On click it executes without any error, but the sub form does not filter. My assumption is I am not addressing the DateAssigned field in the subform correctly or I am still missing something that has the subform show the filtered state. With the two records showing on the .Jpg I filtered for 3/1/2012 - 3/1/2012. It should have filtered to just one record but it still shows the two records. If I select a date range in February the records still show and they should vanish.

    Dim FilterStartDate As Date, FilterEndDate As Date
    FilterStartDate = Me.FilterDateStart
    FilterEndDate = Me.FilterDateEnd
    Me.Filter = "[DateAssigned] >= #" & FilterStartDate & "# AND [DateAssigned] <= #" & FilterEndDate & "#);"
    Me.Filter = True

    Me.Refresh 'does not work
    Me.Requery 'does not work

    Thanks again Fred

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You're overwriting the filter with the second line. It should be

    Me.FilterOn = True
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    By the way, I don't think you want the ");" at the end of the filter
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Phred View Post
    Wow! I was actually that close? Astounding! I must actually be learning.

    Ok, I implemented the corrected code below. On click it executes without any error, but the sub form does not filter. My assumption is I am not addressing the DateAssigned field in the subform correctly or I am still missing something that has the subform show the filtered state. With the two records showing on the .Jpg I filtered for 3/1/2012 - 3/1/2012. It should have filtered to just one record but it still shows the two records. If I select a date range in February the records still show and they should vanish.

    Dim FilterStartDate As Date, FilterEndDate As Date
    FilterStartDate = Me.FilterDateStart
    FilterEndDate = Me.FilterDateEnd
    Me.Filter = "[DateAssigned] >= #" & FilterStartDate & "# AND [DateAssigned] <= #" & FilterEndDate & "#);"
    Me.Filter = True

    Me.Refresh 'does not work
    Me.Requery 'does not work

    Thanks again Fred

    So close!!
    Once you set the filter string, you have to turn on the filter.....

    The example from Help:
    Me.Filter = "Country = 'USA'"
    Me.FilterOn = True

    Code:
    Dim StartDate As Date, EndDate As Date
    StartDate = Me.FilterDateStart
    EndDate = Me.FilterDateEnd
    Me.Filter = "[DateAssigned] >= #" & StartDate & "# AND [DateAssigned] <= #" & EndDate & "#;"
    Me.FilterON = True
    NOTE: I renamed your variables for the dates. I don't like having a variable in code with the same name as a control on a form or field in a table/query. Less confusion if they have different names.

    To remove a filter:

    Code:
    Me.Filter = "" 
    Me.FilterOn = FALSE

  7. #7
    Phred is offline Competent Performer
    Windows XP Access 2010 (version 14.0)
    Join Date
    Dec 2009
    Posts
    246

    Still doesn't filter.

    It's just not functioning. I'm not getting any errors but it just doesn't refresh the data.

    Dim StartDate As Date, EndDate As Date
    StartDate = Me.FilterDateStart
    EndDate = Me.FilterDateEnd
    Me.Filter = "[DateAssigned] >= #" & StartDate & "# AND [DateAssigned] <= #" & EndDate & "#;"
    Me.FilterOn = True

    The immediate window shows it picking up the correct dates from the variables.

    Any other thoughts?

    Fred

  8. #8
    Phred is offline Competent Performer
    Windows XP Access 2010 (version 14.0)
    Join Date
    Dec 2009
    Posts
    246
    When I click in a DayAssigned field and go up to the strip and click selection --=> Between and enter the data range to filter it works perfect and filters the form. I would call that little Between box if I could. Or grab the code out of it.

  9. #9
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Phred View Post
    When I click in a DayAssigned field and go up to the strip and click selection --=> Between and enter the data range to filter it works perfect and filters the form. I would call that little Between box if I could. Or grab the code out of it.
    Do you know how to single step through the code? Set a break point on the "StartDate = Me.FilterDateStart" line.

    On the form, set the dates, then click the CmdFilterMeals button. When the IDE appears, there should be a yellow line on the "StartDate = ....." line. Pressing the "F8" key will execute one line of code each keypress.

  10. #10
    Phred is offline Competent Performer
    Windows XP Access 2010 (version 14.0)
    Join Date
    Dec 2009
    Posts
    246
    I'm familiar with single stepping in code. I can single step through it all the way and it finished without error. Still nothing refreshes.

    Fred

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you post the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    Phred is offline Competent Performer
    Windows XP Access 2010 (version 14.0)
    Join Date
    Dec 2009
    Posts
    246
    Sorry, it's to large. About 9 MB. This site restricts to 500 KB. I can't chop it down enough to get it even close.

    Any other ideas.

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    How about exporting the relevant objects necessary for this function out to a new db, and zip that? All we really need is whatever is required to duplicate this problem.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    Phred is offline Competent Performer
    Windows XP Access 2010 (version 14.0)
    Join Date
    Dec 2009
    Posts
    246
    I will see what I can do on this in the morning. Thanks. I'll take a useable backup and strip out everything else and see how low I can get the size. Thanks John.

  15. #15
    Phred is offline Competent Performer
    Windows XP Access 2010 (version 14.0)
    Join Date
    Dec 2009
    Posts
    246
    Hi: I was thinking about establishing a SugarSync account (like box.com) where I can upload the entire database. I could then send you an invite to look at it. One of the Admins told me the upload size here was 2MB. I have tried uploading once before when I carved it down to 512KB and the system would not accept it. When I got it below that it wouldn't work. If your willing to look at it at SugarSync you will need to send me any email address you want to use, for me to send the invitation to. Even a temp email is fine. I will set up the account as I need it anyhow. If your not comfortable with this I will try carving it down in size but I'm not hopeful. Thanks, Fred.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Filter form with command button
    By NISMOJim in forum Forms
    Replies: 6
    Last Post: 10-22-2011, 02:29 AM
  2. Filter button w/ pop-up dialog?
    By 10 Gauge in forum Forms
    Replies: 1
    Last Post: 07-19-2011, 07:04 AM
  3. Replies: 3
    Last Post: 10-22-2010, 06:53 PM
  4. Button hiding when filter on
    By rstonehouse in forum Forms
    Replies: 2
    Last Post: 08-25-2010, 06:24 AM
  5. Filter button
    By tomeem in forum Access
    Replies: 0
    Last Post: 08-28-2009, 12:50 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums