Re: Passing variable value

Tech-Archive recommends: Fix windows errors by optimizing your registry

Alain wrote:
>I have build a report that is dynamic build by the user. My problem is the
>Order By criteria of the report.
>On my form command button to open the report I have a strSort variable that
>is used to build my Order By string.
>On the command button I use the following to open the report:
>
> DoCmd.OpenReport stDocName, acPreview, , strCond
> If Err.Number = 2501 Then Err.Clear
>
> 'application du sorting au rapport
> With Reports![rpt-GlobalCieList]
> .OrderBy = strSort
> .OrderByOn = True
> End With
> Reports![rpt-GlobalCieList]![Label2].Caption = "( Sorted by: " &
>strShow & " )"
>
>On that report, I have the NoData event being fired when there is no data to
>display since there was new fields added for the criteria selection. I close
>the report at the NoData event (cancel = true) so the user is being send
>back to the form to make modification to the report. Now when I do that I
>get error "2501 The open report action was Cancelled" at he
>DoCmd.OpenReport.
>
>After some testing I was able to find a solution for this, I close the
>report window at the Report_Activate or at the Report_Page which is working
>just fine but I run into another error "2451 Report is not open or does not
>exist......"
>
>Can passing my variable string to the Report_Open or Report_Activate ( where
>I apply the OrderBy) solve my problem ??? or is there anything else I can do
>to correct this.
[]


The Activate event is not useful for this kind of thing.

You need to pass the sort field info to the report by using
the OpenReport method's OpenArgs argument (or some other
technique). Trying to set report properties in the form
after the OpenReport line is problematic at best.

On top of that, the report's Order By property is only
useful in trivial reports. The proper place to specify how
the report should be sorted is in the report's Sorting and
Grouping window(View menu).

This can all be accomplished by using this kind of logic:

In the form button's Click event:
DoCmd.OpenReport "reportname", . . . , _
OpenArgs:="sortfieldname"

In the report's Open event:
Me.GroupLevel(0).ControlSource = Me.OpenArgs

Check Help for details on any of these concepts that you are
not familiar with.

When you cancel the report using the NoData event, the form
is notified of that action via error 2501. If you do not
want the standard error message, then the form button's
Click event should use error handling to trap and ignore the
error:

On Error GoTo ErrHandler
DoCmd.OpenReport "reportname", . . . , _
OpenArgs:="sortfieldname"
ExitHere:
Exit Sub
ErrHandler:
Select Case Err.Number
Case 2501
Resume ExitHere
Case Else
MsgBox Err.Number & " - " & Err.Description
Resume ExitHere
End Select
End Sub

--
Marsh
MVP [MS Access]
.

Quantcast