Table of contents
TOC
Collapse the table of content
Expand the table of content

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

office 365 dev account|Last Updated: 6/12/2017
|
1 Contributor

Applies to: Access 2013 | Access 2016

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.

Functionfunctionname ( ** fld As Control, ** id As Variant, ** row As Variant, ** col As Variant, ** code As Variant** ) As Variant The Function procedure has the following five required arguments.

ArgumentDescription
fldA control variable that refers to the list box or combo box being filled.
idA 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.)
rowThe row being filled (zero-based).
colThe column being filled (zero-based).
codeAn intrinsic constant that specifies the kind of information being requested.

Note Because Microsoft Access calls a user-defined 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 by repeatedly using different values in the code argument to specify the information it needs. The code argument can use the following intrinsic constants.

ConstantMeaningFunction returns
acLBInitializeInitializeNonzero if the function can fill the list; False (0) or Null otherwise.
acLBOpenOpenNonzero ID value if the function can fill the list; False or Null otherwise.
acLBGetRowCountNumber of rowsNumber of rows in the list (can be zero); -1 if unknown.
acLBGetColumnCountNumber of columnsNumber of columns in the list (can't be zero); must match the property sheet value.
acLBGetColumnWidthColumn widthWidth (in twips) of the column specified by the col argument; -1 to use the default width.
acLBGetValueList entryList entry to be displayed in the row and column specified by the row and col arguments.
acLBGetFormatFormat stringFormat string to be used to format the list entry displayed in the row and column specified by the row and col arguments; -1 to use the default format.
acLBEndEnd (the last call to a user-defined function always uses this value)Nothing.
acLBClose(Not used)Not used.

Microsoft Access calls your user-defined function once for acLBInitialize, acLBOpen, acLBGetRowCount, and acLBGetColumnCount. It initializes the user-defined function, opens the query, and determines the number of rows and columns. Microsoft Access calls your user-defined function twice for acLBGetColumnWidth — 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 user-defined function is called for acLBGetValue and acLBGetFormat 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 user-defined function for acLBEnd 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 user-defined function with that code.

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

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 acLBInitialize ' Initialize. 
 ListMondays = True 
 Case acLBOpen ' Open. 
 ListMondays = Timer ' Unique ID. 
 Case acLBGetRowCount ' Get rows. 
 ListMondays = 4 
 Case acLBGetColumnCount ' Get columns. 
 ListMondays = 1 
 Case acLBGetColumnWidth ' Get column width. 
 ListMondays = -1 ' Use default width. 
 Case acLBGetValue ' Get the data. 
 intOffset = Abs((9 - Weekday(Now))Mod 7) 
 ListMondays = Format(Now() + _ 
 intOffset + 7 * row,"mmmm d") 
 End Select 
End Function

The next 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) As String, Entries As Integer 
 Dim ReturnVal As Variant 
 ReturnVal = Null 
 Select Case code 
 Case acLBInitialize ' Initialize. 
 Entries = 0 
 dbs(Entries ) = Dir("*.MDB") 
 Do Until dbs(Entries) = "" Or Entries >= 127 
 Entries = Entries+1 
 dbs(Entries) = Dir 
 Loop 
 ReturnVal = Entries 
 Case acLBOpen ' Open. 
 ' Generate unique ID for control. 
 ReturnVal = Timer 
 Case acLBGetRowCount ' Get number of rows. 
 ReturnVal = Entries 
 Case acLBGetColumnCount ' Get number of columns. 
 ReturnVal = 1 
 Case acLBGetColumnWidth ' Column width. 
 ' -1 forces use of default width. 
 ReturnVal = -1 
 Case acLBGetValue ' Get data. 
 ReturnVal = dbs(row) 
 Case acLBEnd ' End. 
 Erase dbs 
 End Select 
 ListMDBs = ReturnVal 
End Function

ACCESS SUPPORT RESOURCES
Access for developers forum on MSDN
Access help on support.office.com
Access help on answers.microsoft.com
Search for specific Access error codes on Bing
Access forums on UtterAccess
Access wiki on UtterAcess
Access developer and VBA programming help center (FMS)
Access posts on StackOverflow

© 2018 Microsoft