How does the code work?

[aziz rasul]

I've picked up some code from an Access example for setting a combo/list box RowSourceType property.
The function code is listed below....

Function ListYears(fld As Control, id As Variant, _
    row As Variant, col As Variant, code As Variant) _
    As Variant
     
    Const intPreviousYears As Integer = 5
    
    Select Case code
        Case acLBInitialize            ' Initialize.
            ListYears = True
        Case acLBOpen                    ' Open.
            ListYears = Timer        ' Unique ID.
        Case acLBGetRowCount            ' Get rows.
            ListYears = intPreviousYears
        Case acLBGetColumnCount    ' Get columns.
            ListYears = 1
        Case acLBGetColumnWidth    ' Get column width.
            ListYears = -1            ' Use default width.
        Case acLBGetValue                ' Get the data.
    
            ListYears = Year(Now) - intPreviousYears + row + 1
            
    End Select
    
End Function

This particular example uses a combo box and sets the combo box's values to the past five years.
On the property sheet I set the RowSourceType property to the function name (ListYears) and leave the RowSource property blank.
My problem is that I don't understand how the code works! The function seems to be called several times, and the value of the code parameter controls program flow.
What are the other parameters for and where do they come from? Can anyone explain this user-defined function to me?

 

[cable]

urm for starters fld, id and col aren't used at all in that function!
If you give a code that equals on of those constants then it returns on of those cases. The last one being the one that gives you a year that = now-4+row

tbh the whole lot looks like gibberish.

 

[Cly]

Listbox /ComboBox Rowsource:

I'll tell you what. I don't know what that code is supposed to be, but if you're trying to change the rowsource property for a combo/listbox here is some sample code: Keep in mind this is just a generic sample.

Hope This Helps!

Private Sub lstOne_DblClick(Cancel As Integer)

dim SQL as string
SQL = "Select * From tblMaster"

me.lstOne.rowsource = sql

end sub

 

[Jon K]

.... I don't understand how the code works! The function seems to be called several times, and the value of the code parameter controls program flow.
What are the other parameters for and where do they come from? Can anyone explain this user-defined function to me?

Besides Table/Query, Value List and Field List, we can also set the Row Source Type property of a list box or combo box with a User-Defined Function like the one in your example.

Access runs the function several times to carry out actions specified by the constants acLBInitialize, acLBOpen, acLBGetRowCount etc. listed in the function to populate the list box or combo box.


In your example, acLBGetRowCount is set to 5, so Access will run the acLBGetValue expression 5 times:-

Year(Now) - intPreviousYears + row + 1

When the function is run in the year 2005, for the first row (i.e. Row = 0), the expression is equivalent to:
2005 - 5 + 0 + 1, which is equal to 2001

For the second row (i.e. Row = 1), the value is
2005 - 5 + 1 + 1, which is equal to 2002

So when the function is run in the year 2005, the combo box will eventually be populated with these 5 rows:
2001
2002
2003
2004
2005


Besides the six actions listed in your example, there are also other actions that you can define in the User-Defined Function. You can see Access's help file for fuller explanation and more examples.
.

 

[aziz rasul]

The code does seem a bit weird. I'll do it your way.

 

[Mile-O]

Hope This Helps!

Private Sub lstOne_DblClick(Cancel As Integer)

dim SQL as string
SQL = "Select * From tblMaster"

me.lstOne.rowsource = sql

end sub

I know this ain't related to the actual question but why such a long way when all that's needed said here is:

Me.lstOne.RowSource = "Select * From tblMaster"

One line instead of three.

 

[Cly]

Force of Habit

I know this ain't related to the actual question but why such a long way when all that's needed said here is:

Me.lstOne.RowSource = "Select * From tblMaster"

One line instead of three.

It's a force of habit. I define my sql strings in variables because I use the same variable/sql string over and over. Saves me on typing in the long run. Not only that, I can read my code easier.

 

[Mile-O]

Since the SQL doesn't change, there's little point in hardcoding it - it will only add to database bloat.

Two things: i) you could just declare it as a Constant instead of a Variable; or ii) You could just have a proper query and set the RowSource to the query name.

 

[EMP]

The code does seem a bit weird. I'll do it your way.

If what you want is to show in the combo box
2001
2002
2003
2004
2005

you'll need to set the RowSourceType as Value List, not Table/Query, unless of course you have a table field containing the 5 records 2001, 2002, ... 2005.


The user-defined function, though it may take you a little while to understand how it works, is more flexible in that it changes as the year changes.

,