User-defined function in combo box

I am trying to the list years in a combo box based on current year:

e.g: current year -2008
combo box values : 2007,2008,2009,2010,2011,2012

I found a function that calculates this value:

Function YearLoop() As String

Dim YearHold As Date
Dim strSQL As String
Dim i As Integer
Dim n As Integer
strSQL = ""

For i = -1 To 5
YearHold = DateSerial(Year(Date) + i, 1, 1)
strSQL = strSQL & Format(YearHold, "yyyy") & "; "
Next i

YearLoop = strSQL
End Function

I tried to call this function from the combo box by setting
RowSourceType to be YearLoop (I also tried =YearLoop) but the combo
box doesn't seem to be calling that function.

Can anyone suggest how to use the function YearLoop() to populate the
combo box.

Thanks
Thangam

You could do it in the form Load event.
Make sure the row source type of the combo box is Value List.

Then to call the loop function:
strList = YearLoop
strList = Left(strList, Len(strList) -1) 'Takes off the last semicolon
Me.MyCombo.RowSource = strList
--
Dave Hargis, Microsoft Access MVP


"Tangz" wrote:

I am trying to the list years in a combo box based on current year:

e.g: current year -2008
combo box values : 2007,2008,2009,2010,2011,2012

I found a function that calculates this value:

Function YearLoop() As String

Dim YearHold As Date
Dim strSQL As String
Dim i As Integer
Dim n As Integer
strSQL = ""

For i = -1 To 5
YearHold = DateSerial(Year(Date) + i, 1, 1)
strSQL = strSQL & Format(YearHold, "yyyy") & "; "
Next i

YearLoop = strSQL
End Function

I tried to call this function from the combo box by setting
RowSourceType to be YearLoop (I also tried =YearLoop) but the combo
box doesn't seem to be calling that function.

Can anyone suggest how to use the function YearLoop() to populate the
combo box.

Thanks
Thangam

It works !! Thank you Dave ! I really appreciate your help.

Thangam