pass form parameter to Sp to Run the the report

[sknaina]

I have a parameter form to populate the report whose control source "Stored procedure", Form has 3 control and one click event as well as as Set on Report's Input parameter property is

@UID=Forms!RPM_Audit_Trail!CboUser,@SDate=Forms!RPM_Audit_Trail!TxtSdate,@EDate=Forms!RPM_Audit_Trail!txtEdate

And My Stored procedure is

CREATE PROCEDURE dbo.Sp_UserActivity
@UID INT , @SDate Datetime , @EDate Datetime
AS

SELECT dbo.AuditTrailT.AuditiD, dbo.UserT.FullName + ' [ ' + dbo.UserT.LoginName + '] - ' + dbo.DepartMentT.DepartMent AS UserDetails,
dbo.AuditTrailT.EventDate, dbo.ATrailEventT.EventType, dbo.AuditTrailT.ObjectiD

FROM dbo.DepartMentT RIGHT OUTER JOIN
dbo.UserT ON dbo.DepartMentT.DepartMentiD = dbo.UserT.DepartMent RIGHT OUTER JOIN
dbo.AuditTrailT ON dbo.UserT.UseriD = dbo.AuditTrailT.UseriD LEFT OUTER JOIN
dbo.ATrailEventT ON dbo.AuditTrailT.EventObject = dbo.ATrailEventT.EventID

WHERE UserT.Userid=@UID AND dbo.AuditTrailT.EventDate BETWEEN @Sdate AND @EDate
GO

Here is the form's click event

Private Sub BtnPrint_Click()
DoCmd.Close
DoCmd.OutputTo acOutputReport, "rptaudittrail", "Snapshot Format", "D:\MyADPProject\App\Reports\rptaudittrail.snp", True
End Sub

Problem is when Click the click_event it prompts access default parameter form" enter parameter value". Would you please anyone suggest me , what's wrong with the above scenario or What's missing there . . .

(Application Based on A2003 ADP with Sql 2000 Server)

[Jim Doherty]

I have a parameter form to populate the report whose control source "Stored procedure", Form has 3 control and one click event as well as as Set on Report's Input parameter property is

@UID=Forms!RPM_Audit_Trail!CboUser,@SDate=Forms!RPM_Audit_Trail!TxtSdate,@EDate=Forms!RPM_Audit_Trail!txtEdate

And My Stored procedure is

CREATE PROCEDURE dbo.Sp_UserActivity
@UID INT , @SDate Datetime , @EDate Datetime
AS

SELECT dbo.AuditTrailT.AuditiD, dbo.UserT.FullName + ' [ ' + dbo.UserT.LoginName + '] - ' + dbo.DepartMentT.DepartMent AS UserDetails,
dbo.AuditTrailT.EventDate, dbo.ATrailEventT.EventType, dbo.AuditTrailT.ObjectiD

FROM dbo.DepartMentT RIGHT OUTER JOIN
dbo.UserT ON dbo.DepartMentT.DepartMentiD = dbo.UserT.DepartMent RIGHT OUTER JOIN
dbo.AuditTrailT ON dbo.UserT.UseriD = dbo.AuditTrailT.UseriD LEFT OUTER JOIN
dbo.ATrailEventT ON dbo.AuditTrailT.EventObject = dbo.ATrailEventT.EventID

WHERE UserT.Userid=@UID AND dbo.AuditTrailT.EventDate BETWEEN @Sdate AND @EDate
GO

Here is the form's click event

Private Sub BtnPrint_Click()
DoCmd.Close
DoCmd.OutputTo acOutputReport, "rptaudittrail", "Snapshot Format", "D:\MyADPProject\App\Reports\rptaudittrail.snp", True
End Sub

Problem is when Click the click_event it prompts access default parameter form" enter parameter value". Would you please anyone suggest me , what's wrong with the above scenario or What's missing there . . .

(Application Based on A2003 ADP with Sql 2000 Server)

If you are using the input parameter as stated to populate the form then the same value has to be inserted into the reports InputParameter property. Make sure both are present and it should work.

In an ADP data project file Both a Form AND a report have an InputParameter section just make sure the two mirror each other. What essentially happens here is that the report runs 'independant' to the form and seeks the input parameters which in your instance is being provided by the form input parameters

[sknaina]

If you are using the input parameter as stated to populate the form then the same value has to be inserted into the reports InputParameter property. Make sure both are present and it should work.

In an ADP data project file Both a Form AND a report have an InputParameter section just make sure the two mirror each other. What essentially happens here is that the report runs 'independant' to the form and seeks the input parameters which in your instance is being provided by the form input parameters

Thanks to reply, Yes I already use Same Input parameter for form as well as report(forget to mention) though form is unbound. AND Second thing , when run the report independently it prompts access default form . .

[Jim Doherty]

Thanks to reply, Yes I already use Same Input parameter for form as well as report(forget to mention) though form is unbound. AND Second thing , when run the report independently it prompts access default form . .

Look at your code closely... you are closing the form before the report is run. How can it pick up on the parameters on an open form if it is closed? just hide the form until the report has opened and then close the form when the report has formatted

Regards

Jim

[sknaina]

Look at your code closely... you are closing the form before the report is run. How can it pick up on the parameters on an open form if it is closed? just hide the form until the report has opened and then close the form when the report has formatted

Regards

Jim

Jim, now it works and once again thanks for great suggestion