Results 1 to 14 of 14
  1. #1
    Blaze83 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    May 2016
    Posts
    7

    Filter Query using VBA

    I would really appreciate any help on this.

    I have a form which has a sub form with multiple tabs. I want to filter the query that runs the sub form each time a new tab is click.


    The query is " AVL Query" and I want to filter the "Audit Schedule Date" field by Jan-2017.

    I want to put the event on the on click event.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What you wrote doesn't make a whole lot of sense to me. Why apply a filter multiple times, much less the same filter on every page view?
    The pages of a tab control have an On Click event. Use that to apply your filter to the form rather than the underlying query.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Blaze83 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    May 2016
    Posts
    7
    Quote Originally Posted by Micron View Post
    What you wrote doesn't make a whole lot of sense to me. Why apply a filter multiple times, much less the same filter on every page view?
    The pages of a tab control have an On Click event. Use that to apply your filter to the form rather than the underlying query.
    Thank you for the reply.

    I want to be able to click on a tab for each month and only see assigned audits for that month. So each tab would be a given month and that would cause the query to display only information for that month.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Ok, but your post implied a fixed date; Jan-2017, applicable to all pages.
    I'd say you don't need to filter at all if I'm correct in assuming you have 12 tabs (pages) one for each month. Each page should have its own set of controls to display the required fields in say, datasheet view, based on one month per page. There would be no additional filtering involved. However, it is not an efficient design. There should only be one page, filtered by a month combo selection on the main form. A page for each month is 12 times the overhead that is necessary. Perhaps I have misinterpreted the design...
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Blaze83 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    May 2016
    Posts
    7
    Quote Originally Posted by Micron View Post
    Ok, but your post implied a fixed date; Jan-2017, applicable to all pages.
    I'd say you don't need to filter at all if I'm correct in assuming you have 12 tabs (pages) one for each month. Each page should have its own set of controls to display the required fields in say, datasheet view, based on one month per page. There would be no additional filtering involved. However, it is not an efficient design. There should only be one page, filtered by a month combo selection on the main form. A page for each month is 12 times the overhead that is necessary. Perhaps I have misinterpreted the design...
    Would this be done by the properties section "Filter on load"? I'm not sure how to get that to work.

    Once again, thanks for the help.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If using the combo box selection as suggested, your combo box would need a list of months. This can be a value list or a table (tblMonths). However, unless you have other reasons why you still need a tab control, I see no point in having it for just one page. You might want to remove it and use only a subform control and a subform. Either way, when user selects a month, you use the combo AfterUpdate event to set the record source for the subform to a sql statment and refresh the form. I don't have enough info to go much deeper into this since I know nothing of the recordsource for that subform or even if you'll take that route. Nor do I know how you were going to control the year criteria for your month pages. If that was going to be another control on the main form, the sql statement has to concatenate the year and month control data into date criteria.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Blaze83 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    May 2016
    Posts
    7
    Quote Originally Posted by Micron View Post
    If using the combo box selection as suggested, your combo box would need a list of months. This can be a value list or a table (tblMonths). However, unless you have other reasons why you still need a tab control, I see no point in having it for just one page. You might want to remove it and use only a subform control and a subform. Either way, when user selects a month, you use the combo AfterUpdate event to set the record source for the subform to a sql statment and refresh the form. I don't have enough info to go much deeper into this since I know nothing of the recordsource for that subform or even if you'll take that route. Nor do I know how you were going to control the year criteria for your month pages. If that was going to be another control on the main form, the sql statement has to concatenate the year and month control data into date criteria.
    So I took your advice; I now have on Form with two combo boxes that filter the subform based on those combo boxes (Query field "Auditor":[Forms]![AuditScheduler]![Auditor] & Query field "Audit Schedule Date":[Forms]![AuditScheduler]![AuditScheduleMonth]).
    The "Auditor" box filters the subform but the "Audit Schedule Date" does not. The "Audit Schedule Date" combo box has a value field "Jan-2017";"Feb-2017";"Mar-2017" etc. I suspect that the because "Audit Schedule Date" combo box is text, it will not match up with the dates, which the query is pulling from the table.

    Do you have any suggests on how to get the "Audit Schedule Date" combo box to filter the query?

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How is the date represented in the query? Access, by default uses MM/DD/YY. Your Mar-2017 format needs to be adjusted so that a comparison can be made.

    Can you show us some SQL?

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    to set the record source for the subform to a sql statment and refresh the form.
    This is not the same thing as filtering a subform based on main form controls. Since I believed you were using two controls (auditor and date info) I suggested this route because I thought it would be easier than instructing how to create a form filter based on multiple controls, then applying that. Orange is correct in asking for the sql behind the subform (I think subform was meant) and the fact that Jan-2007 is not a literal date in Access, whether the data type is text or not. One way to coerce June-17 to a date is DateValue("June-07"). The current year will be the default if one is not specified, since the year part of a date is necessary for it to be a date, regardless of whether or not you make it visible. You need to understand how that will affect your desired outcome. The date separator (-, /) resulting from the above conversion will likely be according to your system regional settings.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Blaze83 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    May 2016
    Posts
    7
    Quote Originally Posted by orange View Post
    How is the date represented in the query? Access, by default uses MM/DD/YY. Your Mar-2017 format needs to be adjusted so that a comparison can be made.

    Can you show us some SQL?
    The format on the properties page for, query field, "Audit Schedule Date" is set to mmm-yyyy. How do i show you sql data? (forgive me if this is a silly question)Click image for larger version. 

Name:	AVL Query.jpg 
Views:	21 
Size:	140.5 KB 
ID:	24786

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Right click in the top part of query design, then select sql view, copy and paste i your post.

  12. #12
    Blaze83 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    May 2016
    Posts
    7
    Quote Originally Posted by orange View Post
    Right click in the top part of query design, then select sql view, copy and paste i your post.
    So simple thanks

    SELECT AVL.[NEW AUDIT CONTROL NUMBER], AVL.[DAYS TILL DUE], AVL.[RII CAPABLE], AVL.[AIR AGENCY CERT], AVL.ADDRESS, AVL.REGION, AVL.VENDOR, AVL.[Vendor Type], AVL.AUDITOR, AVL.[Audit Schedule Date], AVL.[DUE DATE]
    FROM AVL
    WHERE (((AVL.AUDITOR)=[Forms]![AuditScheduler]![Auditor])) OR (((AVL.[Audit Schedule Date])=[Forms]![AuditScheduler]![AuditScheduleMonth]));

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What is current status?

    What are the values in these
    (AVL.[Audit Schedule Date]) and [Forms]![AuditScheduler]![AuditScheduleMonth]

  14. #14
    Blaze83 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    May 2016
    Posts
    7
    Quote Originally Posted by orange View Post
    What is current status?

    What are the values in these
    (AVL.[Audit Schedule Date]) and [Forms]![AuditScheduler]![AuditScheduleMonth]
    (AVL.[Audit Schedule Date])= Comes from the Table AVL: Data Type Date/Time. Format: mmm-yyyy
    [Forms]![AuditScheduler]![AuditScheduleMonth]= Row Source: "Jan-2017";"Feb-2017";"Mar-2017"

    Here is what i have so far. However, i think i want to have the Combo box just for the year and then have a option group for the month.
    Click image for larger version. 

Name:	AVL2.jpg 
Views:	19 
Size:	112.8 KB 
ID:	24788

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 05-27-2015, 12:45 PM
  2. Replies: 9
    Last Post: 02-24-2015, 11:19 AM
  3. Query, Look-up with filter?
    By CocoPuente in forum Queries
    Replies: 1
    Last Post: 10-08-2014, 11:00 PM
  4. Replies: 2
    Last Post: 01-30-2013, 07:34 PM
  5. Filter or Query? Help!
    By Niki in forum Access
    Replies: 2
    Last Post: 04-26-2011, 02:11 PM

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