none
Combo box to contain YYYYMM selections for past 2 years? RRS feed

  • Question

  • Is there a way to dynamically create a selection list to be used by a combo box? I want it to contain all the YYYYMM combinations from the current year/month going back 2 years, so there will be 24 entries. I'd rather not create a temporary table to do this if there is any other way.
    Thursday, June 2, 2016 9:19 PM

Answers

  • Is there a way to dynamically create a selection list to be used by a combo box? I want it to contain all the YYYYMM combinations from the current year/month going back 2 years, so there will be 24 entries. I'd rather not create a temporary table to do this if there is any other way.

    There are several ways to go about this.  Personally, I always have a little table of records numbered 0 to 9 (or 999), and use cross-join queries to create as many records as I like.  There is also a built-in mechanism for providing your own routine to fill a combo box -- Access will call this function to intialize the combo, get each row, and end the loading process.  However, that's a bit complicated for what you need.  Probably the simplest method is to use VBA code to generate a value list for the combo's RowSource property.  Here's example code:

        Dim strMonths   As String
        Dim dt          As Date
        Dim I           As Long
    
        dt = Date
        
        For I = 1 To 24
            strMonths = strMonths & ";" & Format(dt, "YYYYMM")
            dt = DateAdd("m", -1, dt)
        Next I
        
        Me.cboMonths.RowSource = Mid$(strMonths, 2)


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html


    Thursday, June 2, 2016 10:03 PM