How to show a value from a closed form in a report header?

[NielsE]

I have a form where I through three parameters, chosen by combos, show a list
of records. A code at the same time populates the label for the list showing the parameters plus some text to make it understandable. A print button closes the form and open a report with records from the form list (using a query that uses the parameters from the form).
Now I want to make an unbound text box in the report header, which shows the content of the label in the form ( because it actually shows the criteria for the record selection).
How do I do this? I have tried to set the control source for the text box to =DLookUp, but it does not work.
Niels

 

[RuralGuy]

Why not pass the data to the report in the OpenArgs argument of the OpenReport command?

 

[Rich]

Just keep the Form open until the Report closes

 

[NielsE]

Sorry about my ignorance, Rural Guy, but how do I do this?
I wish to take the value of the label "lblList" in the form "frmParam3Test" and put it in the text box "lblSelectPro" in the report "rptSelectPro".
Niels

 

[RuralGuy]

Assuming you will be opening the Report from the frmParam3Test form then your OpenReport code will look something like:

stDocName = "rptSelectPro"
DoCmd.OpenReport stDocName, acPreview, , , , , Me.lblList.Caption

And the code in your report will look something like:

Private Sub Report_Open(Cancel As Integer)
Dim Args As Variant
If Not IsNull(Me.OpenArgs) Then
    '-- Report is being opened from a form passing Data
    Args = Split(Me.OpenArgs, ";")  '-- You could pass more than one argument
    Me.lblSelectPro = Args(0)         '-- Get the 1st argument from the Args() array
End If
End Sub

 

[NielsE]

Thanks for your help RG. However, I get the Compile Error: "Wrong number of arguments or invalid property assignment" on the DoCmd.OpenReport in the below code:

Private Sub PPrint_Click()
On Error GoTo Err_PPrint_Click

Dim stDocName As String
stDocName = "rptSelectPro"
DoCmd.OpenReport stDocName, acPreview, , , , , Me.lbllist.Caption
DoCmd.Close acForm, "frmParam3Test"
Exit_PPrint_Click:
Exit Sub

Err_PPrint_Click:
MsgBox Err.Description
Resume Exit_PPrint_Click
End Sub

Any idea why?

 

[RuralGuy]

I put in one too many ,'s - change:
DoCmd.OpenReport stDocName, acPreview, , , , , Me.lbllist.Caption - to:
DoCmd.OpenReport stDocName, acPreview, , , , Me.lbllist.Caption

Sorry

 

[NielsE]

Thanks again, RG.
However, now I get the following Run-time error on the Report_Open:

Run-time error' - 2147352567(80020009)'
You can't assign a value to this object.

The debugger steps in at Me.lblSelectPro = Args(0) in the Report_Open event procedure.
Niels

 

[RuralGuy]

You are correct. The open event is too early to move values into controls on the report. Move the code to the Format event where the control resides and the error should go away. I keep forgetting I put these values in public variables in the open event and assign them to controls in the format events. Sorry I'm being so slipshod with directions here. It really does work.

 

[NielsE]

Thanks again RG.
It works beautifully now.
Niels

 

[RuralGuy]

Outstanding! Glad you got things working. I'll try to be more careful with my directions in the future.

 

[ray147]

hi..

i've seen this thread coz i'm trying to do the same thing of passing the criteria on a form to a text box on a report.. however am getting the error of 'wrong number of arguments or invalid property assignment' on the OpenReport statement...

i have as follows:

DoCmd.OpenReport "ReturnsDiary_DateRange", acViewPreview, , , ,Me.DateFrom.Text,Me.DateTo.Text

i'm running Access 2000, if this could be an issue

any help appreciated..tnx

 

[RuralGuy]

There are only 6 arguments in the OpenReport command. You need to combine your OpenArgs parameters into 1 string:
Dim MyArgs As String
MyArgs = Me.DateFrom & ";" & Me.DateTo
DoCmd.OpenReport "ReturnsDiary_DateRange", acViewPreview, , , ,MyArgs

You can not reference the .Text property of a control unless the control has the focus so use the .Value property instead, which is the default property so you do not need to declare it.

You can use the Split() function in the report to separate the passed arguments into an array.

 

[ray147]

Tnx for yr answer..
however still getting the same compile error on the OpenReport statement..I have as follows:

Dim MyArgs As String
MyArgs = Me.DateFrom & ";" & Me.DateTo
DoCmd.OpenReport "ReturnsDiary_DateRange", acViewPreview, , , , MyArgs


In the report VBA code I have the following:

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim MyArgs As Variant
If Not IsNull(Me.OpenArgs) Then
MyArgs = Split(Me.OpenArgs, ";")
Me.rptDateFrom = MyArgs(0)
Me.rptDateTo = MyArgs(1)
End If
End Sub


Could it be an issue with Access 2000? When I start writing the OpenReport statement, Access points out only four properties ..i.e. ReportName, View, FilterName,WhereCondition

views?! tnx

 

[RuralGuy]

Hmm, could be. I use ac2K2. I don't know when MS implemented the OpenArgs argument. If you don't have it you will need to keep your form open and reach back into it through the Forms collection. Your VBA help on OpenReport should tell you how many arguments it will take.

 

[ray147]

tnx for your help!

i tried exactly the same code in Access 2003 at home and it works perfect! But unfortunately can't use this at work coz we have 2K everywhere...however now i went to the other option of leaving my form open and retrieving the data through the Forms collection, works just the same..so it's cool...

tnx again

 

[RuralGuy]

Glad you got it working. Now we both know when MS put in the OpenArgs argument.