Results 1 to 7 of 7
  1. #1
    jtan is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Nov 2013
    Location
    Singapore
    Posts
    22

    Autofill combox with Year


    Need your help to advise how to autofill combo box with years (eg 2018, 2017, 2016).
    I'd tried Year(Date); Year(Date)-1 ; Year(Date)-2 in Row Source. But this does not work.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The easiest way is to use a Value List and just type in those numbers separated by commas as you have done here

  3. #3
    jtan is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Nov 2013
    Location
    Singapore
    Posts
    22
    The numbers in the combo box are this year, last year and year before last year.
    If I type in the numbers, I've to change it every year. In the beginning of next year, I'll have to change it to 2019, 2018 , 2017.
    Any solution ?

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Look at this demo:

    Can change the number of years displayed/select-able:
    Code:
            Case acLBGetRowCount
                ListYears = 4
      '         ListYears = 200
            Case acLBGetValue
                ListYears = intYear - 2 + lngRow
      '         ListYears = intYear - 100 + lngRow
        End Select
    Attached Files Attached Files

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Ah - you didn't make that clear in your first post
    I have a similar situation for a listbox where I list the last 5 years of exams data:

    Click image for larger version. 

Name:	Capture.PNG 
Views:	20 
Size:	4.5 KB 
ID:	31934

    This is automatically updated every year
    However to do this I'm using a query to get the exam years in descending order & selecting the top 5

    I think you need to do something similar.
    Use a table containing dates for the 3 years you want and base a query on that.
    I assume you must have such a table or there'd be no point having the combobox!

    EDIT - posted before seeing ssanfu's solution - very neat!
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    Use a query as RowSource (table name may be of any of existing table in application)
    Code:
    SELECT DISTINCT YEAR(DATE())-2 AS YearNo FROM AnyTable
    UNION
    SELECT YEAR(DATE())-1 AS YearNo FROM AnyTable
    UNION
    SELECT YEAR(DATE()) AS YearNo FROM AnyTable;

  7. #7
    jtan is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Nov 2013
    Location
    Singapore
    Posts
    22
    Thanks ridders52, ssanfu and ArviLaanemets for your fast response and great help...The problem had been solved.

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

Similar Threads

  1. Replies: 2
    Last Post: 04-19-2017, 01:09 PM
  2. Replies: 3
    Last Post: 06-22-2015, 06:36 AM
  3. Replies: 1
    Last Post: 02-14-2015, 02:29 AM
  4. Combox list box
    By tmcrouse in forum Forms
    Replies: 2
    Last Post: 04-28-2014, 06:49 PM
  5. Combox Box Help
    By BLD21 in forum Reports
    Replies: 3
    Last Post: 09-22-2011, 01:06 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