Form / List Box Stored Procedure Problem (Detailed) ADP

[Rick Phillips]

I have been able to use the Input Parameter to use with a Stored Procedure
and run a report.

I am using a Form with a Tab Control and a List Box on one of the tabs.

I can not pass parameters to the Stored Procedure that I have in the Row
Source.

The Row Source type = Tables / Views / StoredProcedures

The stored procedure looks like the following:

Alter Procedure FindLastName
@LName as varchar(30)
As
SELECT DISTINCT
buyers.buy_lname, buyers.buy_fname, stores.store_name,
stores.store_id
FROM buyers INNER JOIN
stores ON buyers.store_id = stores.store_id
WHERE (buyers.buy_lname LIKE @LName)
ORDER BY buyers.buy_lname
return

I am getting prompted for the value of LName just as the form opens. I have
the InputParameter = @LName = lname()

The lname() function is shown below:
Private Function lname()
Dim LN As String
LN = Forms![frm_record_find]![FindLastName]
LN = "%" + LN + "%"
lname = LN
End Function
The function has been tested and it works.

How can I pass a parameter to the Stored Procedure in a List Bow???

 

[Rick Phillips]

I found my answer InputParameter will not work on a list box use the
following.

listbox1.RowSource = "Exec sp1 @param1 = " & tb1 & _
", @param2 = " & tbl2

in the change event or wherever.

If tbl1 & tbl2 are text (not numeric) then you'll need quotes around them:

listbox1.RowSource = "Exec sp1 @param1 = '" & tb1 & _
"', @param2 = '" & tbl2 & "'"

I got help from www.tek-tips.com