Variables in Combo Boxes

Variables in Combo Boxes

Occasionally, you might wish to have a combo box that varies according to the time. For example you might wish to store a year value in a field, but to have the values automatically change according to the current date, so that the values available for selection might be Previous Year, Current Year, Next Year.

Access Help shows a user defined function ListMondays that is used to show the next 4 Mondays from the current date, which illustrates one way of doing this. Another option would be to create a small look up table, and then check the values in the Switchboard form's OnLoad event, and amend as necessary.

To my mind, both of the above options seem rather like overkill. My easy way of doing this is to:

Create a combo box;
Set its RowSourceType to Value List;
Set its ControlSource to the required field.

And then in the combo box's OnEnter event:

Private Sub cboName_Enter()
    Dim strRowSource As String
    strRowSource = Chr(34) & Year(Date) - 1 & Chr(34) & ";" & Chr(34) & Year(Date) & Chr(34) & ";" & Chr(34) & Year(Date) + 1 &Chr(34)
    Me!cboName.RowSource = strRowSource
    Me!cboName.DefaultValue = Me!cboName.ItemData(1)
End Sub

This starts by creating a string that is made up of the previous year, the current year and next year, and then sets the RowSource of the combo box to this string. Finally, it sets the default value of the combo box to the current year, which is the second row in the combo box (rows are 0-indexed in combo boxes).

Top

Last modified at 06/06/2006 14:54:06