Populate a List Box by Function (1 Viewer)

[Rod C]

I am attempting to populate a list box by creating a function and placing the function name in the RowSourceType Property of the List Box. The list that I want in the List Box is a Recordset created with a Select Statement.

I am using function format from a help example. The help reference is called "RowSourceType Property (User-Defined Function) - Code Argument Values" and if you click the Example hypertext you will see the example code.

I can’t seem to get it to use my Recordset to populate the List Box. If you know the syntax to accomplish this I would appreciate the help.

Here is the attempted code:

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

    'Dim rst As Recordset
    Select Case code
        Case acLBInitialize             		' Initialize.
            ListTempProjNums = True
        Case acLBOpen                   		' Open.
            ListTempProjNums = Timer   			' Unique ID.
        Case acLBGetRowCount           			' Get rows.
            ListTempProjNums = -1
        Case acLBGetColumnCount         		' Get columns.
            ListTempProjNums = 1
        Case acLBGetColumnWidth         		' Get column width.
            ListTempProjNums = -1       		' Use default width.
        Case acLBGetValue               		' Get the data. (Help here!)
            Set rst = CurrentDb.OpenRecordset(strSql)
            rst.MoveLast
            'ListTempProjNums = rst

    End Select

End Function

 

[Pat Hartman]

Is there some reason that you don't want to just use the query as the rowsource for the ListBox? NO coding is required for this method.

 

[Rod C]

Yes, I started with a query but due to the number of fields, datatypes, and the fact that any number of the criteria fields may be blank it wouldn’t return the records correctly.

So now I am using a form that has combo and list boxes on it which supply the where criteria of a Select Statement. A procedure in a module tests to see which combo and list boxes have criteria in them and then runs a Select Statement, the form then displays the records that met the criteria. I am using the list box to further refine the record selection by allowing multiple selection then opening a report which displays the selected records. All the above is working, however I want the records in the list box to match the recordset displayed in the form.

If you want to see my code I could send it to you.
rod.r.cathcart@odot.state.or.us