passing parameters to stored procedure from report

[Bob]

I'm new to Access projects and SQL server and am not a veteran VB
programmer. There's a cry for help! I'm attempting to print the
current form on screen by using a command button which the user clicks
once they have selected the desired record. The button calls a report
which uses a stored procedure as its record source. The SP has 2 input
parameters, one of which is a datetime data type. In the input
parameters data field of the report I'm referencing the actual form
text boxes as such:

@mrnum nvarchar(12)=[forms]![frm-clt_visit_edit]![MRN],
@visitdate datetime=[forms]![frm-clt_visit_edit]![visit_date]

Here is the stored procedure code:
ALTER PROCEDURE dbo.SP_lookup_visit_rec
(@mrnum nvarchar(12), @visitdate datetime)
AS SELECT dbo.tbl_clients.*, dbo.tbl_client_visit.*
FROM dbo.tbl_clients INNER JOIN
dbo.tbl_client_visit ON dbo.tbl_clients.MRN =
dbo.tbl_client_visit.MRN
WHERE (dbo.tbl_client_visit.mrn = @mrnum) AND
(dbo.tbl_client_visit.Visit_date = @visitdate);

When I click the command button it displays a blank report. When I
remove the form reference for testing, it prompts for the Visit_date
and I can enter the date from the open form - and it prints the current
form without a problem. I know the field is of datetime and the text
box is text, so I've tried converting it by reading the text box into a
varible and using the Cdate function. I've done a debug.print of the
var and it shows up in the immediate window. All of my efforts thus
far have been fruitless. How do I go about passing this date value
into the Stored procedure? Any help is appreciated.

Following is reference to a previous post here that I found when
searching newsgroups.

----------------------------------------------------------------
*If* you are using the stored procedure as a datasource for a report
you could pass parameters from your form to the SP by using the Report
InputParatmeters property:

Example:
@StartDate =[forms]![forms1]![txtBeginnin*gDate], @EndDate =
[forms]![forms1]![txtEndDate]
Your SP would have to be designed to accept @StartDate and @EndDate as
parameters and using them in a where clause:
Between @StartDate and @EndDate

[Lyle Fairfield]

Bob wrote:

I'm new to Access projects and SQL server and am not a veteran VB
programmer. There's a cry for help! I'm attempting to print the
current form on screen by using a command button which the user clicks
once they have selected the desired record. The button calls a report
which uses a stored procedure as its record source. The SP has 2 input
parameters, one of which is a datetime data type.

This is what I do in an ADP

create a public function in a standard module eg

PublicFunctionThatReturnsaStringofInputParameters( ) as String

This function should return a string such as:

@ParameterName int=32, @OtherParamterName varchar(50)='Bizarro' ... etc

set the recordsource of the report to the sproc name: ProcReport

set the input parameters as

= PublicFunctionThatReturnsaStringofInputParameters( )

If you use this you will have to use your own string.

However I have seen a post here that indicates success with

setting the Input Parameters as something like

@ParameterName int=Forms(BlahBlah)!etc, @OtherParamterName
varchar(50)=Forms(YeahYeah)!etc

--
Lyle

To subject an enemy belligerent to an unfair trial, to charge him with
an unrecognized crime, or to vent on him our retributive emotions only
antagonizes the enemy nation and hinders the reconciliation necessary to
a peaceful world.

Justice Frank Murphy
Yamashita v. Styer, 327 U.S. 1 (1946)

[Salad]

Bob wrote:

I'm new to Access projects and SQL server and am not a veteran VB
programmer. There's a cry for help! I'm attempting to print the
current form on screen by using a command button which the user clicks
once they have selected the desired record. The button calls a report
which uses a stored procedure as its record source. The SP has 2 input
parameters, one of which is a datetime data type. In the input
parameters data field of the report I'm referencing the actual form
text boxes as such:

Is your problem with new records? Until a record is committed (saved)
then printing is going to display a blank record. Let's say you have a
command button you may want to enter some code in it like
If Me.Dirty Then Me.Dirty = False
to commit the save prior to printing. IOW, don't print until you've saved.