Form / List Box Stored Procedure Problem

[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???

Please Help.

I am a visual person examples would be great

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Is the form "frm_record_find" the form that is opening and contains
the ListBox? If so, then there isn't a value in the control
"FindLastName" when the ListBox is initializing. You should update
the ListBox's RowSource property in the form's OnCurrent event. E.g.:

Private Sub Form_Current()

Me!MyListBox.RowSource = "exec FindLastName '" & Me!FindLastName &
"'"

End Sub

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQEeZgoechKqOuFEgEQIiZQCgvC/ek6N9Y7R5Dj0g+yRfMrSz0UYAoJFr
/QFjeWOt+wT2C9NHoQt782BH
=C7/Y
-----END PGP SIGNATURE-----


Rick Phillips wrote:

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)

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

Private Function lname()
Dim LN As String
LN = Forms![frm_record_find]![FindLastName]
LN = "%" + LN + "%"
lname = LN
End Function

< SNIP >

 

[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