How do I create a listbox that accepts a paramater

[Jeff via AccessMonster.com]

I have a form with a stored procedure as the record source. In that form, I
have a list box that requires a paramater but I can't seem to get it
working. I tried:

listbox.recordsouce = "EXEC StoredProcName '" & ParamaterName & "'"

 

[Sylvain Lafontaine]

For ListBox and ComboBox, the name of the property is RowSource and not
RecordSource. You must also set the RowSourceType property.

 

[Jeff via AccessMonster.com]

I tried using ADO to run the Stored Procedure but I can't get it to work.
On the forms current event I call a pulic function using:

Me!ListControl.RowSource = myGetList("[MyStoredProcedure]", MyParamater)

Below is the Function. It crashes at "Parameters.Append prm"

Public Function myGetList(strCommandText As String, Optional varParam)

Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Const DELIM As String = ";"

Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection

If Not IsMissing(varParam) Then
Set prm = .CreateParameter("Param1", adVarChar, adParamInput, 50,
varParam)
Parameters.Append prm
End If

.CommandText = strCommandText
.CommandType = adCmdStoredProc
End With

Set rst = New ADODB.Recordset
rst.Open cmd

myGetList = ""
If Not rst.EOF Then
myGetList = rst.GetString(adClipString, , DELIM, DELIM)
End If

End Function