passing a form value into a report filter

[xilbus via AccessMonster.com]

Need help:

I have a form setup to run a report based off of data on that report. The
report needs to know which record its looking at based off of the primary key,
an int value autonumber. The report is setup to run based off of a stored
procedure:

SELECT [Case number]
FROM dbo.Support
WHERE ([Case number] = @Key)

Im using a command button to open the report with this code:

Private Sub Command504_Click()
Dim stDocName As String
stDocName = "report1"
the_key = Me.text1.Value
DoCmd.OpenReport stDocName, acViewPreview
End Sub

the_key is a module item: Public the_key As Integer

from here the report takes over, when it opens it runs this code:

Me.InputParameters = "@Key INT=" & the_key

Here is the problem: The value of @key is going only as far as the input
parameters and is not getting passed successfully into the store procedure.
Can anyone please help me get that value into the stored procedure?

 

[giorgio rancati]

Hi ,

look at this Kb.

 

[Vadim Rapp]

xvA> Here is the problem: The value of @key is going only as far as the
input
xvA> parameters and is not getting passed successfully into the store
xvA> procedure. Can anyone please help me get that value into the stored
xvA> procedure?

throw away stored procedure (it's good idea to keep the pieces of the
program in minimum places, especially when the piece is as trivial)

make recordsource of the report
SELECT [Case number] FROM dbo.Support

call the report as

openreport report1,,,"[Case number] = " & the_key

 

[xilbus via AccessMonster.com]

Thanks for the help Vadim, that worked