Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 29th, 2004, 07:59 AM
Registered User
 
Join Date: Nov 2003
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Access Forms Open Method of Passing Parameters


I'm having some problems with generating complex reports from Access 2002 front end on a SQL Server 2000 Database. The stored procedure behind the report draws its data from several views with grouping and calculated fields. The report itself has further grouping.

The report runs well when run manually from the Database Window with manually input parameters but it won't work when the parameters are passed from a form via the Input Parameters property box in the Report. An error message says that the Report does not permit calculated fields such as =sum([[fieldname]). When I run the report without such group totals it shows a CALC (______Parameter::) error.

I would like to try the Open method using
Exec usp_My_Stored_Procedure Parameter1, Parameter 2, Paramater 3 suggested in Topic 4785 and would appreciate some help with some sample code to show exactly where to place this line in a Private Sub.

My stored procedure is called "udsp_Job_Activity_Report_By_Employee_And_DateZone " and the Parameters are @Employee_Number, @Start_Date and @End_Date.
The form is called "FEmployee Job Time Selector" with three text boxes Text2, Text4 and text6. (I promise to name them properly when I get them to work, honest.)

For example, what would be the correct syntax for the suggested Exec command and parameter information and where should it be placed in the following Private Sub?

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click

    Dim stDocName As String

    stDocName = "Employee Job Activity Report"
    DoCmd.OpenReport stDocName, acPreview

Exit_Command10_Click:
    Exit Sub

Err_Command10_Click:
    MsgBox Err.Description
    Resume Exit_Command10_Click

End Sub

I've tried a few variations but am obviously missing something.

Your assistance will be greatly appreciated

Alan



 
Old January 29th, 2004, 10:54 AM
Authorized User
 
Join Date: Jun 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Alan,
   I have the same setup Access 2002 FE with a SQL Server 2K BE. Here is what I do when I want to base a report on a stored proc.

   Create a pass-through query with the name of the stored proc and the parameters. Use DAO when in the open event of the report to set the SQL property of the pass-through query to correct parameter values. Then set the Record Source of the Report to the name of the pass-through query. I make the name of the pass-through query the same as the stored proc for readability purposes only.

   I have found a Microsoftism with ADO record sets and the report recordset property. The help file says that the recordset propertyof the report object can hold either a DAO or ADO recordset just like the form object (which does work), but in reality you cannot set the recordset property of the report to a recordset, you will get a 2593 error. This is a known bug in Access. See white paper Q287437 from Microsoft.

Hope this helps,
Mike (aka Dataman)
 
Old February 2nd, 2004, 09:16 PM
Registered User
 
Join Date: Nov 2003
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Mike,

Thanks for responding. Much appreciated. Unfortunately, I am not having much luck with the pass-through query and must be missing something fundamental (and probably very simple.

Does one write this query in SQL Server or in Access. I read Access Help and it describes how to create a pass-through query for Access Databases but the menu item is not available for Access Project.

I have tried to write

execute usp_MyStoredProcedure Parameter1, Parameter 2, Parameter 3
in the SQL Code pane in both Access and SQL Server to no avail.

Would be good enough to explain to me how to create one of these.

 
Old February 2nd, 2004, 10:08 PM
Authorized User
 
Join Date: Jun 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Alan,
   I thought it was an Access database (mdb) you were talking about not an Access project which has the extension (adp). I'm not an expert with adp's...I work more with mdb's using Access as a Frontend to SQL Server. I'm not sure I would be the one to help solve an ADP problem. If you are using an mdb...I can steer you in the write direction.

Regards,
Mike
 
Old February 3rd, 2004, 05:18 PM
Registered User
 
Join Date: Nov 2003
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

OK Mike, Thanks for the help.

Would anybody else have any suggestions?

 
Old February 3rd, 2004, 06:51 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

 
Quote:
quote:The form is called "FEmployee Job Time Selector" with three text boxes Text2, Text4 and text6.


Name the textboxes
Text2 = Employee_Number
Text4 = Start_Date
Text6 = End_Date

Your controls must have the same names as the parameters in the stored procedure.

Let me knokw if that works. And, you are using an .adp. Right???



Sal
 
Old February 8th, 2004, 10:08 PM
Registered User
 
Join Date: Nov 2003
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sal,

Sorry for the delay in responding.

Regretfully that didn't work. It gave the same errors as before. I am using an .adp.

Would it be possible for you to explain the open method using the exec command?

 
Old September 21st, 2004, 05:53 PM
Registered User
 
Join Date: Sep 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Alan, Did u find the answer to ur query?
 
Old September 22nd, 2004, 09:04 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 120
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Having something along the lines of:

@Parm1 = Forms!frm.txt1, @Parm2 = Forms!frm.txt2, @Parm3 = Forms!frm.txt3

in your InputParameters will usually work - if you're trying to pass calculated values, then calculate them on your form in hidden text boxes.


Brian Skelton
Braxis Computer Services Ltd.





Similar Threads
Thread Thread Starter Forum Replies Last Post
passing parameters to Access Report murtazasif Access VBA 2 November 6th, 2007 12:32 PM
Passing parameters to Access query pcassistnw Access 0 March 2nd, 2007 11:07 PM
Passing Parameters Between Two Forms sjf905 C# 2 January 13th, 2007 10:44 PM
How to check the parameters passed to web method? karveajit ASP.NET 1.0 and 1.1 Professional 3 December 20th, 2006 05:55 PM
Passing Multiple Parameters into Access Query rit01 Classic ASP Databases 1 October 26th, 2005 04:00 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.