list box using stored procedure

[belovedcej]

I have a stored procedure that provides names based on user entry.

I have a list box that is based on that procedure.

I have a form with three fields, the list box, and a few buttons.

The goal is this:

1. user enters data in any one of the three fields (if the fields are empty they default either zero or zero length strings, depending on type.

2. User presses "Search" button which runs the stored procedure using their values and the list box is populated with results.

3. User can then select on of the options and press another button to assign that person to a claim in three possible different capacities.

3 is no problem for me. and 1 is not difficult - I know how to get the data and store it and even pass it into a stored procedure.

The problem is this - I don't know how to open the form without the list box trying to run. Then the user gets boxes asking for the variables.

Then, when I have the user entered data, I don't know how to indicate that the listbox should refresh using the indicated data for the variables.

Any ideas?

 

[danvlas]

You may let the listbox without any rowsource and use the recordset returned by the stored procedure to populate it either by using AddItem method in a loop or setting the .Recordset property in the OnClick event of the button.

AddItem allows you to remove items from the list, if necessary. Recordset is easier to employ.

Set rst=CurrentProject.Connection.Execute "StoredProc(" & Me.Param1Box & ", " & me.Param2Box & ", " & Me.Param3Box & ")"

Using AddItem (RowSourceType = ValueList):

With rst
  While rst.EOF
     Me.ListBox.AddItem rst.Field1 & ", " & rst.Field2
     .MoveNext
  Wend
End With
Set rst= Nothing

Using Recordset (RowSourceType = Table/View/StoredProc):

Set Me.ListBox.Recordset = rst
Set rst=Nothing

HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[belovedcej]

Thanks for the help!

I'm trying to do the recordset way and I'm getting a syntax error on the first line. It says "expects end of statement" after the name of the stored procedure. Should I list the parameters separately?

Set rst = CurrentProject.Connection.Execute "dbo.CSF_Search_People_SP(" & intAttorneyNumber & ", " & strFirstName & ", " & strLastName & ")"

I tried the following also, but it is saying that my rst is not a valid recordset. There must be something I'm not understanding.

Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command

With cmd
    .ActiveConnection = CurrentProject.BaseConnectionString
    .CommandType = adCmdStoredProc
    .CommandText = "dbo.CSF_Search_People_SP"
    .Parameters.Refresh
    .Parameters(1) = intAttorneyNumber
    .Parameters(2) = strFirstName
    .Parameters(3) = strLastName

Set rst = .Execute

Me.listPeople.Recordset = rst

End With

Set rst = Nothing

 

[ItIsHardToProgram]

Try it without ( )

 

[belovedcej]

Do you mean like this?

Set rst = CurrentProject.Connection.Execute "dbo.CSF_Search_People_SP" & intAttorneyNumber & ", " & strFirstName & ", " & strLastName & "

I still get an error saying that it expects the end of the statement and it highlights "dbo.CSF_Search_People_SP".

 

[danvlas]

Strings should be enclosed between '

Assuming 'intAttorneyNumber' is a number:
Set rst = CurrentProject.Connection.Execute "dbo.CSF_Search_People_SP(" & intAttorneyNumber & ", '" & strFirstName & "', '" & strLastName & "')"

Set Me.listPeople.Recordset = rst


And make sure your stored proc returns a recordset, not output parameters...

Not to mention 'Execute' permissions.



HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[belovedcej]

Okay - I figured out the error!!

I needed parenthesis around the command line.

Set rst = CurrentProject.Connection.Execute ("dbo.CSF_Search_People_SP(" & intAttorneyNumber & ", '" & strFirstName & "', '" & strLastName & "')")

It works correctly. Thanks so much for the help!!