Form / List Box Stored Procedure Problem

[SundancerKid]

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

 

[cjowsey]

There are two options here

1. Is frm_record_find open when you open the form with the InputParameter?

2. Have you tried puting the lastname in a module level variable and accessing it that way?
For example (and this is very quick and dirty), in module Names

Dim strLastName as String

Public Function GetLastName() AS String
GetLastName = "%" & strLastName & "%"
End Function

Public Sub SetLastName (pstrName as String)
strLastName = pstrName
End Sub[/color blue]

The your InputParameter becomes @LName = GetLastName()[/color blue]

In the LostFocus event of Forms![frm_record_find]![FindLastName][/color blue] put SetLastName Forms![frm_record_find]![FindLastName][/color blue]

Personally, I like to use the Get and Set structure (either in a module or class module) because you don't have to rely on users doing the right thing.

Hope this helps,
Clive

 

[SundancerKid]

Hi Clive,

The your InputParameter becomes @LName = GetLastName() the Stored Procedure is getting accessed before the GetLastName is being called. the List box is unbound, should it be bound?? if so to what?

 

[SundancerKid]

I found my answer see
Thread958-616778