Using Code for Combobox Rowsource

[simon_marklar]

I cant figure out whats going wrong here. im using the example in the help for a base and it refuses to work. Its ment to populate the combo box with all the years from the current year down to 2006 (or just 2006 in the case of this year)

here's the code:

Public Function ListYears(fld As Control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant
    Static myYears(), Entries As Integer
    Static ReturnVal As Variant
    Debug.Print "in the years list code"
    
    ReturnVal = Null
    'Entries = 0
    Select Case code
        Case acLBInitialize                ' Initialize.
            Debug.Print "acLBInitialize"
            ListYears = True
        Case acLBOpen                        ' Open.
            ' Generate unique ID for control.
            Debug.Print "acLBOpen"
            ReturnVal = Timer
        Case acLBGetRowCount            ' Get number of rows.
            Debug.Print "acLBGetRowCount"
            ReturnVal = DateDiff("y", year(Now), year("1/1/2006")) ' num of rows = diff between now and 2006)
.            If ReturnVal = 0 Then   ' not sure if this is needed
.                ReturnVal = 1
.            End If
            Debug.Print "rows=" & ReturnVal
        Case acLBGetColumnCount    ' Get number of columns.
            Debug.Print "acLBGetColumnCount"
            ReturnVal = 1
        Case acLBGetColumnWidth    ' Column width.
            Debug.Print "acLBGetColumnWidth"
            ' -1 forces use of default width.
            ReturnVal = -1
        Case acLBGetValue                    ' Get data.
            Debug.Print "acLBGetValue"
            ReturnVal = year(Now()) - row
            
        Case acLBEnd                        ' End.
            Debug.Print "acLBEnd"
    End Select
    ListYears = ReturnVal
End Function

the debug window prints "in the years list code", then "acLBInitialize" but nothing else, and no years appear in the combo box. can anyone suggest what i've done wrong? It just wont execute any other code no matter what i click!

im doing it this way because i thought it would be an easy way to learn how the combo/list box functions worked. i can do it easy in other code, but i want to know what i've done wrong here.

thanks for your help people

%simon

 

[raskew]

Hi -

It needn't be that complicated. Try copying/pasting the following to a module and to test, from the debug (immedate) window, type:
? yearloop <enter>

Function YearLoop() As String
'*******************************************
'Name:      YearLoop (Function)
'Purpose:   Create a dynamic string (based on
'           the current year) of years to be
'           used as a combo-box's value list,
'           starting with current year -10
'           through current year + 10
'*******************************************

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

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

End Function

HTH - Bob

 

[simon_marklar]

im doing it this way because i thought it would be an easy way to learn how the combo/list box functions worked. i can do it easy in other code, but i want to know what i've done wrong here

thanks anyways (for the record i had nearly the same code before i deceided to do it with a function.)