RowSourceType (User-Defined Function) -- Code Argument Values

Description

The Visual Basic function you create must accept five arguments. The first argument must be declared as a control and the remaining arguments as Variants. The function itself must return a Variant.

Syntax

Function functionname(fld As Control, id As Variant, row As Variant, col As, code As Variant) As Variant

The following are the five required arguments.

Argument	Description
fld	A control variable that refers to the list box or combo box being filled.
id	A unique value that identifies the control being filled; this is useful when you want to use the same user-defined function for more than one list box or combo box and must distinguish between them. (The example sets this variable to the value of the Timer function.)
row	The row being filled (zero-based).

Argument	Description
col	The column being filled (zero-based).
code	A value that specifies the kind of information being requested.

Note Because Microsoft Access calls a list function several times to insert items into a list, often you must preserve information from call to call. The best way to do this is to use Static variables.

Microsoft Access calls the user-defined function repeatedly using different values in the code argument to specify the information it needs. The code argument can have the following values.

Code	Meaning	Function returns
0	Initialize	Nonzero if the function can fill the list; False or Null otherwise.
1	Open	Nonzero ID value if the function can fill the list; False or Null otherwise.
2	(Not used)
3	Number of rows	Number of rows in the list (can be zero); -1 if unknown.
4	Number of columns	Number of columns in the list (can’t be zero); must match the property sheet value.
5	Column width	Width (in twips) of the column specified by the col argument; or -1 to use the default width.
6	List entry	List entry to be displayed in the row and column specified by the row and col arguments.
7	Format string	Format string to be used to format the list entry displayed in the row and column specified by the row and col arguments; or Null to use the default format.
8	(Not used)
9	End (the last call to a list function always uses this value)	Nothing.

Microsoft Access calls your list function once for codes 0, 1, 3, and 4. It initializes the list function, opens the query, and determines the number of rows and columns.

Microsoft Access calls your list function twice for code 5 — once to determine the total width of the list box or combo box and a second time to set the column width.

The number of times your list function is called for codes 6 and 7 to get list entries and to format strings varies depending on the number of entries, the user’s scrolling, and other factors.

Microsoft Access calls the list function for code 9 when the form is closed or each time the list box or combo box is queried.

Whenever a particular value (such as the number of columns) is required, returning Null or any invalid value causes Microsoft Access to stop calling the list function with that code.

Tip You can use the Select Case code structure from the example as a template for your own RowSourceType user-defined functions.

Your user-defined function can use the numeric codes or the corresponding constants. Intrinsic constants have several advantages. They don’t have to be loaded or declared, and they’re more descriptive than the numeric codes. The following table lists the numeric codes and their corresponding intrinsic constants.

Code	Intrinsic Constant
0	acLBInitialize
1	acLBOpen
3	acLBGetRowCount
4	acLBGetColumnCount
5	acLBGetColumnWidth
6	acLBGetValue
7	acLBGetFormat
8	acLBClose
9	acLBEnd

Note You can’t create your own constants with the same names as Microsoft Access intrinsic constants.

Example

The following user-defined function returns a list of the next four Mondays following today’s date. To call this function from a list box control, enter ListMondays as the RowSourceType property setting and leave the RowSource property setting blank.

Function ListMondays(fld As Control,id As Variant,row As Variant, _
        col As Variant,code As Variant) As Variant
    Dim intOffset As Integer
    Select Case code
        Case 0                        ' Initialize.
            ListMondays = True
        Case 1                        ' Open.
            ListMondays = Timer        ' Generate unique ID for control.
        Case 3                        ' Get number of rows.
            ListMondays = 4
        Case 4                        ' Get number of columns.
            ListMondays = 1
        Case 5                        ' Get column width.
            ListMondays = -1            ' -1 forces use of default width.
        Case 6                        ' Get the data.
            intOffset = Abs((9 - Weekday(Now))Mod 7)
            ListMondays = Format(Now() + intOffset + 7 * row,"mmmm d")
    End SelectFunction

The following example uses a static array to store the names of the databases in the current directory. To call this function, enter ListMDBs as the RowSourceType property setting and leave the RowSource property setting blank.

Function ListMDBs (fld As Control, id As Variant, row As Variant, _
        col As Variant, code As Variant) As Variant
    Static dbs(127), Entries
    Dim ReturnVal
    ReturnVal = Null
    Select Case code
        Case LB_INITIALIZE            ' Initialize.
            Entries = 0
            dbs(Entries ) = Dir("*.MDB")
            Do Until dbs(Entries) = "" Or Entries >= 127
                Entries = Entries+1
                dbs(Entries) = Dir
            Loop
            ReturnVal = Entries
        Case LB_OPEN                    ' Open.
            ReturnVal = Timer        ' Generate unique ID for control.
        Case LB_GETROWCOUNT            ' Get number of rows.
            ReturnVal = Entries
        Case LB_GETCOLUMNCOUNT        ' Get number of columns.
            ReturnVal = 1
        Case LB_GETCOLUMNWIDTH        ' Column width.
            ReturnVal = -1            ' -1 forces use of default width.
        Case LB_GETVALUE                ' Get data.
            ReturnVal = dbs(row)
        Case LB_END                    ' End.
            For Entries = 0 to 127
                dbs(Entries) = ""
            Next
    End Select
    ListMDBs = ReturnValFunction