listbox function

[Jason]

Hi,

Could somebody help me make the following code to be used as a function? I
want to call it in every form where i have listboxes or comboboxes. The
source of the recordset should also come from a function.

Public Function Filler()

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strValueList As String

Set cnn = GetConnection(True)

Set rst = New ADODB.Recordset

With rst
.ActiveConnection = cnn
.Source = <function?>
.Open
Do Until .EOF
strValueList = strValueList & rst.Fields("field1") & ";" & _
rst.Fields("field2") & ";"
.MoveNext
Loop
End With

With lstcmb
.RowSourceType = "Value List"
.RowSource = strValueList
End With

rst.Close

Set rst = Nothing

cnn.Close

Set cnn = Nothing

End Function

 

[Malcolm Cook]

Jason,

Is this for an adp project?

Do you need to pass parameters to your .source function?

Is it really a function (i.e. a table valued user defined function)?

Did you know that you can set the recordset of a combobox and listbox?

This may interest you

Private Sub Form_Load()
With Me.cboTestThis
.RowSource = "Table/View/StoredProc"
Set .Recordset = Access.CurrentProject.AccessConnection.Execute("select *
from dbo.my_UDF()")
End With
End Sub



Or, you can instead write:

.RowSourceType = "Value List"
.RowSource = Access.CurrentProject.AccessConnection.Execute("select *
from dbo.dbo.my_UDF()").GetString(, , ";", ";")

 

[Jason]

Hi Malcolm,

It is for a access 2000 mdb. But thanks, i figured it out already.

Jason