passing a form value into a report filter

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

xilbus - 24 Mar 2006 23:37 GMT

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?

Reply to this Message

giorgio rancati - 24 Mar 2006 23:57 GMT

Hi ,

look at this Kb.
----
How to dynamically set the input parameters of a report at run time in an
ADP
http://support.microsoft.com/kb/300693/en-us
----

bye

Signature

Giorgio Rancati
[Office Access MVP]

> Need help:
>
[quoted text clipped - 25 lines]
> parameters and is not getting passed successfully into the store procedure.
> Can anyone please help me get that value into the stored procedure?

Reply to this Message

Vadim Rapp - 25 Mar 2006 14:20 GMT

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

Reply to this Message

xilbus - 27 Mar 2006 19:01 GMT

Thanks for the help Vadim, that worked

Reply to this Message