Help with combo box and years

[gojets1721]

I have a combo box which lists out all years going back to 2014. It's a value list with the row source of:

"2023";"2022";"2021";"2020";"2019";"2018";"2017";"2016";"2015";"2014"

So when it turns 1/1/2024, I need to manually add "2024" as a value. Is there an automated way to do this instead? So that when it hits 1/1/2024, "2024" is automatically included as a value?

 

[June7]

Could have VBA code that builds this list every time form opens.

Dim x As Integer, y As Integer, s As String
y = Year(Date)
For x = y To 2014 Step -1
    s = s & x & ";"
Next
Me.cbxYrs.RowSource = Left(s, Len(s) - 1)

Otherwise, need to open form in Design Mode, modify combobox, save. This can be done programmatically but I don't really see any benefit to that.

Do you have a table with date field that these years could be extracted from? Never mind, would have to first add a record for 2024.

Don't need the quote marks in that list.

 

[Ranman256]

never hard values on combos.
either pull year value from the table that hold your data sets
or
make a dedicated table, tYears, and enter the year value there so the combo can use it.

 

[gojets1721]

Could have VBA code that builds this list every time form opens.

Dim x As Integer, y As Integer, s As String
y = Year(Date)
For x = y To 2014 Step -1
    s = s & x & ";"
Next
Me.cbxYrs.RowSource = Left(s, Len(s) - 1)

Otherwise, need to open form in Design Mode, modify combobox, save. This can be done programmatically but I don't really see any benefit to that.

Do you have a table with date field that these years could be extracted from? Never mind, would have to first add a record for 2024.

Don't need the quote marks in that list.

Where I put that code? The On Click event? (sorry I'm not on my main device so I can't test it at the moment)

 

[June7]

Probably form Open or Load event.

 

[Uncle Gizmo]

You can do this with a callback function. See my video here:-

Nifty Date Picker - WITH YEAR SELECTOR - Nifty Access​

 

[Uncle Gizmo]

RowSourceType Callback Function in Microsoft Access​

This is @strive4peace Crystal's video explaining how it works...