Create listbox values based on array

[tristap]

I am trying to create a listbox on a form that contains a list of years based on values I have automated in an array. The first year in the list will always be 2005 and then I want to add each year after that until the current year ie. 2006. So the list should display 2005 & 2006 and next year 2005 & 2006 & 2007.

Not sure how to display the array values within the actual field. Currently I get a "subscript out of range" error message on the last line. I have investigated the REDIM method and I am not sure I am using it correctly.

Can anyone help?

Here is the code I am trying to use...


Private Sub Form_Load()
Me.cboYear2 = fncYear(Year(Date))
End Sub

Function fncYear(CurrYear) As String

Dim myYearArray() As String
Dim myFirstYear
Dim myCount As Integer
Dim myConv, cnt As Integer

myFirstYear = 2005

myConv = Format(CurrYear, "General Number")
cnt = myConv - myFirstYear

myCount = 0

Do Until myCount = cnt + 1
ReDim myYearArray(myCount)
myYearArray(myCount) = myFirstYear
myFirstYear = myFirstYear + 1
myCount = myCount + 1
Loop

fncYear = myYearArray(CurrYear)

End Function

 

[PHV]

Why not simply something like this ?
Private Sub Form_Load()
Dim y As Integer, s As String
For y = 2005 To Year(Date)
s = s & ";" & y
Next y
Me!cboYear2.RowSource = Mid(s, 2)
Me!cboYear2 = Year(Date)
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tristap]

PH,

Thanks, the code supplied is much simpler and does exactly what I wanted.