By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,122 Members | 1,641 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,122 IT Pros & Developers. It's quick & easy.

pass form parameter to Sp to Run the the report

P: 11
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

Expand|Select|Wrap|Line Numbers
  1. @UID=Forms!RPM_Audit_Trail!CboUser,@SDate=Forms!RPM_Audit_Trail!TxtSdate,@EDate=Forms!RPM_Audit_Trail!txtEdate

And My Stored procedure is

Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE dbo.Sp_UserActivity
  2. @UID INT , @SDate Datetime , @EDate Datetime
  3. AS
  4.  
  5. SELECT dbo.AuditTrailT.AuditiD, dbo.UserT.FullName + ' [ ' + dbo.UserT.LoginName + '] - ' + dbo.DepartMentT.DepartMent AS UserDetails,
  6. dbo.AuditTrailT.EventDate, dbo.ATrailEventT.EventType, dbo.AuditTrailT.ObjectiD
  7.  
  8. FROM dbo.DepartMentT RIGHT OUTER JOIN
  9. dbo.UserT ON dbo.DepartMentT.DepartMentiD = dbo.UserT.DepartMent RIGHT OUTER JOIN
  10. dbo.AuditTrailT ON dbo.UserT.UseriD = dbo.AuditTrailT.UseriD LEFT OUTER JOIN
  11. dbo.ATrailEventT ON dbo.AuditTrailT.EventObject = dbo.ATrailEventT.EventID
  12.  
  13. WHERE UserT.Userid=@UID AND dbo.AuditTrailT.EventDate BETWEEN @Sdate AND @EDate
  14. GO

Here is the form's click event

Expand|Select|Wrap|Line Numbers
  1. Private Sub BtnPrint_Click()
  2. DoCmd.Close
  3. DoCmd.OutputTo acOutputReport, "rptaudittrail", "Snapshot Format", "D:\MyADPProject\App\Reports\rptaudittrail.snp", True
  4. End Sub
  5.  

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)
Apr 26 '10 #1
Share this Question
Share on Google+
4 Replies


Jim Doherty
Expert 100+
P: 897
@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
Apr 26 '10 #2

P: 11
@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 . .
Apr 26 '10 #3

Jim Doherty
Expert 100+
P: 897
@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
Apr 27 '10 #4

P: 11
@Jim Doherty
Jim, now it works and once again thanks for great suggestion
Apr 27 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.