Results 1 to 8 of 8
  1. #1
    Battlescar is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    18

    ComboBox on Form to Select Select first Fiscalyear and next Fiscalyear records and filter

    Hi
    I am trying to have a combo box that selects FY 16/17 and also pick up FY 17/18 and filter all the records in those two years. Also if you select 19/20 you also pick up Fiscal year FY 20/21. then filter the records for both years. Attached a screen shot. This is coming from a query. If you need more info please let me know. This is my first time posting.

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    ComboBox on Form to Select Select first Fiscalyear and next Fiscalyear records and filter

    Are the fiscal years stored anywhere with their own id number? (Or the year in question eg 2016 for fy16/17)

  3. #3
    Battlescar is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    18
    Yes the combo box is sorted by FY its coming from a table. I have attached the table.Attachment 31324

  4. #4
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    ComboBox on Form to Select Select first Fiscalyear and next Fiscalyear records and filter

    Oops. Just realised. In the onchange event of the combo something like

    Dim myfilter as string
    myfilter =“fiscalyear = ‘“&cboname.column(1) & “‘ or fiscalyear = ‘“ & cboname.column(4) & “‘“
    Me.filter = myfilter
    Me.filteron= true
    (Me.requery might be necessary too)

    No computer to test with this end though.

  5. #5
    knarfreppep is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Feb 2015
    Location
    Adelaide, Australia
    Posts
    106
    Am I missing something here ... if the FYs are always contiguous with the 2nd being the next, then if you pick the one you know the other.

  6. #6
    Battlescar is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    18

    I have attached the database to look at.

    Quote Originally Posted by knarfreppep View Post
    Am I missing something here ... if the FYs are always contiguous with the 2nd being the next, then if you pick the one you know the other.
    I have attached the database for you to look at when selecting the survey year it has to pick up data from Fiscalyear for both years such as FY 18/19 and FY 19/20.
    Please look at the form and you will see what I am trying to do.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    There are many things that could/will cause problems.
    <minor>
    1) "FIELD" (in table "tblsurveylist") is a reserved word in Access and shouldn't be used for a field name.
    2) In table "tblSurveylistSub", the field "SurveyName" should NOT be in this table. It is redundant.
    3) Should not have spaces in object names.
    4)The related fields between "tblsurveylist" and "tblSurveylistSub" is wrong.
    5) Tables with PK fields named "ID" should be renamed.

    <major>
    6) The combo box "cboFY" has two event procedures: the Afterupdate event filters the form record set (to 488 records when FY 18/19 is selected) and the Change event procedure removes the filter form record count becomes 1818).(I don't think the form needs a record set - maybe)
    7) The Count() function is the wrong function to use.
    8) I wrote a UDF to get the counts you provided. (took a while to partially understand what you wanted)


    Take a look and see if this is close to what you are trying to do.....


    BTW, I changed a couple of dates to get an output recordset, so I might have messed up your data....
    Attached Files Attached Files

  8. #8
    Battlescar is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    18
    Thank you ssanfu it looks perfect. I thank you so much. Well done.

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

Similar Threads

  1. Replies: 9
    Last Post: 01-31-2017, 05:13 PM
  2. Replies: 3
    Last Post: 01-20-2017, 02:25 PM
  3. Using a combobox on a form to select table
    By rzardo in forum Access
    Replies: 5
    Last Post: 01-23-2014, 09:33 PM
  4. Use combobox to select form to open
    By gemadan96 in forum Forms
    Replies: 11
    Last Post: 11-15-2012, 01:42 PM
  5. Form ComboBox Filter - Select ALL records
    By jhrBanker in forum Forms
    Replies: 2
    Last Post: 08-02-2012, 08:57 AM

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