Hi!

I have another question on reports.
I got this piece of code to generate reports from one of the wrox books.

docmd.openreport str1,acviewdesign
reports(str1).inputparameters="@country_name='" & me.cboPickCountry & "'"
docmd.closereport acreport,str1,acsaveno
docmd.openreport str1,acviewpreview

This works fine when I am printing just one report. When I want to print another report, the input parameters are not being updated to the new value if the stmt docmd.closereport..........is included and I get the same data as the first report.

Any suggestions as to how I can get around the problem

Thanks!

What is firing your code to open the report?
Make sure that your str1 variable is being declared inside of your procedure that is calling your code so that it goes out of scope. Do not put it on the general declarations area.

Also, I notice that you are using the value of a combo box named cboPickCountry. Is this the object running the code?
Are you using an Access .mdb or an Access .adp?

Sal


Sal
[email protected]

I am using adp project as front end and sql server as the back end.
The report should show records based on certain criteria selected on a form. Record source of a report is set to a stored procedure where some of the conditional variables are determined during run time(values from a form are the condition criteria). How can I generate report based on this?

Thanks

I use a similar process to generate reports.

I put code on the Open method of the report to collect the input parameters (conditional criteria) using input boxes or forms.

The code calls a pass-through query .

The pass-through query is used as the data source to populate the report.



Rand

I agree with Rand. This is by far the best way to pass parameters into a stored procedure and use it for a report. However, re_anu is using an adp file. for this, if I remember correctly, you can actually write the name of the stored procedure as the datasource on the properties and use a reference to the object of the form on the right side of the name of the stored procedure. If you have multiple parameters, just separate them by commas.





Sal

Sal,

I have the same problem as re_anu. I am developing an application with an adp front end, SQL back end, and would much appreciate the exact syntax required to solve the problem.

In order to try to find the solution I have set up a test report populated by a simple stored procedure.

The stored procedure is called TestFormFPO and it's code is:-

SELECT ForeignPOID, DateInitiated
FROM dbo.TForeignPO
WHERE (ForeignPOID = @PONumber)

My Form is called FTestForeignPO and consists of a Combo box called Combo3 and a Preview Report Command Button called Command2

Combo3 is populated from a Table called TForeignPO by the following code:-

SELECT [TForeignPO].[ForeignPOID]
FROM [TForeignPO]

ForeignPOID is an Integer

In my report I want to see the ForeignPOID and DateInitiated for the ForeignPOID number chosen by the user from Combo3

The wrox code on page 424 of "Professional SQL Server Development with Access 2000" by Rick Dobson is as follows:-

Private Sub cmdprint_report_Click()
Dim str1 AS String

'Specify name of report
str1 = "rptcustomer_labels_for_inputparameters"

'Supress screen feedback
DoCmd.Echo False, "I'm preparing the report."

'Open report in Design view and update
'input parameters property
DoCmd.Openreport str1, acViewDesign
Reports(str1), InputParameters = _
     "@Country_name = '" & Me.cbopick_a_country.Value & "'"
DoCmd.Close acReport, str1, acSaveNo
'Print report or print
If Me.opgpreview_or_print = 1 Then
      DoCmd.OpenReport str1, acViewPreview
Else
      DoCmd.OpenReport str1, acViewNormal
End If

'Restore screen feedback
DoCmd.Echo True

End Sub

My example is simpler and the variable @PONumber is an integer and not a char. I condensed the code as follows:-

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

    Dim str1 As String

    stDocName = "TForeignPO"
    DoCmd.OpenReport str1, acViewDesign
    Reports(str1).InputParameters = _
    "@PONumber = '" & Me.Combo3.Value & "'"
    DoCmd.Close acReport, str1, acSaveNo
    DoCmd.OpenReport str1, acPreview


Exit_Command2_Click:
    Exit Sub

Err_Command2_Click:
    MsgBox Err.Description
    Resume Exit_Command2_Click

End Sub

When I run this code the Stored Procedure still requests manual entry of the Parameter @PONumber. I think it is because the syntax for an integer variable is significantly different than for a char, varchar or nvarchar but can't find the right alternative combination.

I am looking forward to a resolution of this and would very much appreciate your assistance.

Regards,

Alan.

Alan

First thing to note - if the parameter you're passing is a number, don't put quotes around it. Change this: to Secondly, I cannot see how this code can ever work. It looks to me as if you're opening the report in design view, making a change and then closing the report without saving the changes.

Try changing this line: to to see if it makes a difference.

Thirdly, if you have any plans to distribute your database as an ADE file then abandon this method totally. In an ADE you cannot open any object in design view, so it'll never work.


Brian Skelton
Braxis Computer Services Ltd.

Brian,

Your code works. Thank you.
I'm a new boy at this and have no idea what your last sentence meant.
If there is a better way to pass parameters from a form to a report based on a stored procedure, I'm all ears.

Thanks once again.

Alan

Alan

An .ADE file is a semi-compiled version of an .ADP file (similarily, an .mde file is a semi-compiled version of a .mdb file)

To create one, open your database and choose Database Utilities|Make ADE File... from the Tools menu. This produces a new file with an .ADE extension.

ADE Files have the following features:

1) They contain no code. All the VBA is compiled, so no-one can steal all your hard work.

2) Design changes to forms, reports, etc are impossible. So those pesky users have much less ability to mess things up.

There are various reasons for using them: Security, intellectual property, smaller file size, speed and the ability to run using Access runtime are my main ones. The downside is that you cannot run any code that attempts to put an object into design view.

The simplest way to pass a parameter to a report is this:

Change the RecordSource of your report to dbo.TestFormFPO.
Change the InputParameters property to @PONumber=Forms.FTestForeignPO.Combo3
Change the code behind button2 to:

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

    Dim stDocName As String

    stDocName = "TForeignPO"
    DoCmd.OpenReport stDocName , acPreview

Exit_Command2_Click:
    Exit Sub

Err_Command2_Click:
    MsgBox Err.Description
    Resume Exit_Command2_Click

This will take the value of the PO directly of the form.

Oh, one more word of advise - always give your controls a meaningful name. When you come to look at your code six months down the line it'll make your life much, much easier!


Brian Skelton
Braxis Computer Services Ltd.

I'm still having some problems with generating more complex reports from Access 2002 on a SQL Server 2000 Database. The stored procedure behind the report has grouping and calculated fields. The reports run well with manually input parameters but don't work when the parameters are passed from a form in the way described by Braxis. (No disrespect intended, your stuff got me a long way forward).

I would like to see some sample code to show exactly where to place the
Exec usp_My_Stored_Procedure Parameter1, Parameter 2, Paramater 3
code in the Open Method suggested by Rand (rgerald) to see if that method will solve the problems.

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 continued assistance will be greatly appreciated

Alan