change report label depending on query used 1

[cbd]

I currently have over 30 reports in my access 97 database. All these reports have the same layout. The only difference between each report is the record source query used to open the report and a label that give a title for the report. I am trying to use just one general report, changing the query and label for each report depending on which button was pressed. I know how to use DoCmd.OpenReport to open the report with the query I want but I have been unable to change the caption in the label. How do I change a label caption in a report(the label is called "ReportName" and the report is called "repGeneral&quot using VBA or something else.

 

[jgoodman00]

I wrote somthing similar to this:

In a standard module I wrote the following function:

Function PrintSpecificReport(AssetType As String, priority As String)
On Error GoTo Print_Err

DoCmd.OpenReport "rptCivilsGeneral", acViewPreview, , , , AssetType & "_" & priority

Print_Exit:
Exit Function

Print_Err:
MsgBox Error$
Resume Print_Exit

End Function


In the report class module I then wrote teh following sub:

Private Sub Report_Open(Cancel As Integer)
On Error GoTo ReportOpen_Err
Dim AssetType As String, priority As String, pos As Integer

pos = InStr(Me.OpenArgs, "_&quot
AssetType = Left(Me.OpenArgs, pos - 1)
priority = Mid(Me.OpenArgs, pos + 1)
Call CmdBarEnable("Print...&quot
Call CmdBarDisable("Reports&quot


Me.RecordSource = "qryCivils" & AssetType & priority
Me.txtReportTitle.Caption = priority & " Priority " & AssetType
Me.txtAssetCostLabel.Caption = AssetType
Me.txtAssetCost.ControlSource = "[AssetCost]"
Me.txtReportFooterLabel.Caption = "Total Cost for " & priority & " Priority " & AssetType & ":"
Me.txtFooterCalculation.ControlSource = "=Sum([AssetCost]*1000)"


ReportOpen_Exit:
Exit Sub

ReportOpen_Err:
MsgBox Error$
Resume ReportOpen_Exit

End Sub

Finally to execute a report I wrote a custom menu, which calls the first function, & passes in the requried parameters...


James Goodman

 

[cbd]

I am afaid that your code didn't work. The openReport method doesn't allow opening args, only the openForm method allows that. Have you any more ideas?

 

[jgoodman00]

I just checked the Access help file for teh OpenReport method & this is what it said:


OpenReport Method
See Also Applies To Example Specifics
The OpenReport method carries out the OpenReport action in Visual Basic.

expression.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)

expression Required. An expression that returns a DoCmd object.

ReportName Required Variant. A string expression that's the valid name of a report in the current database. If you execute Visual Basic code containing the OpenReport method in a library database, Microsoft Access looks for the report with this name, first in the library database, then in the current database.

View Optional AcView. The view to apply to the specified report.

AcView can be one of these AcView constants.
acViewDesign
acViewNormal default Prints the report immediately.
acViewPivotChart Not supported.
acViewPivotTable Not supported.
acViewPreview

FilterName Optional Variant. A string expression that's the valid name of a query in the current database.

WhereCondition Optional Variant. A string expression that's a valid SQL WHERE clause without the word WHERE.

WindowMode Optional AcWindowMode.

AcWindowMode can be one of these AcWindowMode constants.
acDialog
acHidden
acIcon
acWindowNormal default

OpenArgs Optional Variant. Sets the OpenArgs property.

Remarks
For more information on how the action and its arguments work, see the action topic.

The maximum length of the WhereCondition argument is 32,768 characters (unlike the Where Condition action argument in the Macro window, whose maximum length is 256 characters).

You can leave an optional argument blank in the middle of the syntax, but you must include the argument's comma. If you leave one or more trailing arguments blank, don't use a comma following the last argument you specify.


It makes specific reference to the OpenArgs property. I have also been using this method for many months now without error.

We are using Access XP, perhaps this revision includes the ability to use the OpenArgs property in a report.

However, you should be able to use a global variable instead of the openargs, & read the value stored there instead...



HTH James Goodman

 

[mflancour]

Were you able to get this to work yet? If not, I have a very easy, some what backwords solution.

1. On the form with the buttons to open report place an unbound text box (Ill call it "text0" for my example). On each button place in the onclick something like the followingyou could also call a dinamid aray so you only have to write it once but no bigy)

me.text0 = "table or query name for the button clicked"
docmd.openreport.......

2. On the report (tittle = text1 for this example) in the OnOpen event place something like the following:

me.text1.caption = forms![formname]![text0]
me.recordsource = forms![formname]![text0]

(you may want to use a second test box on your form if you want the tittle on the report to be different than the table/query name)

This of course could be cleaned up to be a bit more efficient but you get the idea. Let me know if it works.

 

[cbd]

I am afraid James Goodman that I am using Access 97 and the OpenReport method does not support Opening Arguments. Thanks for your idea mflancour. I actually did some thing very similar by declaring a public variable, setting that variable when the button in the form was clicked and accessed that variable in the OnOpen event of the report. It was a great help as it managed to reduce 40 reports down to one single general report, reducing my database from 22MB down to 2MB.

Cheers

 

[braindead2]

This may be off track of what you were attempting to do and a little late, But it seems another option would be if all you are tring to do is pass the name of the query that is currently the record source to the label then using this should work

Me.labelname.Caption = "Records from " & [Reports]![reportname].[RecordSource]