Passing variable value

[Alain]

Hi to all,

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.

Is there a way to find if a report is open, I have tried the IsLoaded
function but it works only on forms:

Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet
view.
Const conObjStateClosed = 0
Const conDesignView = 0
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If
End Function

Thanks

Alain

 

[Marshall Barton]

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

 

[Alain]

Marsh,

I have already tried passing the sort order thru the OpenArg many times and
could not succeed at all, I found out that the ORBER BY cannot be accepted
thru this, that is the reason I use the With Report....... .OrberBy =
strSort
Although, setting the grouping and sorting is best done with the Group and
Sorting window, I have to keep in mind that the report is sorted by the end
user, therefore not being the same everytime the report is being run .....

I think I will keep it much simpler, not something I want to do but......,
the report is still gonna show with a msgbox saying that there is no data to
report to and let the end use close the report himself

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

 

[Marshall Barton]

You are confusing two different things here. OpenArgs is
just a way to get infomation to the report. Using the
GroupLevel is **the** way to dynamically modify a report'd
sorting. Just make sure that the group level exists in
design view by assigning something in the sorting and
grouping window. I doesn't much matter what, so long as
there's an entry that can be modified when the report is
opened.
--
Marsh
MVP [MS Access]

I have already tried passing the sort order thru the OpenArg many times and
could not succeed at all, I found out that the ORBER BY cannot be accepted
thru this, that is the reason I use the With Report....... .OrberBy =
strSort
Although, setting the grouping and sorting is best done with the Group and
Sorting window, I have to keep in mind that the report is sorted by the end
user, therefore not being the same everytime the report is being run .....

I think I will keep it much simpler, not something I want to do but......,
the report is still gonna show with a msgbox saying that there is no data to
report to and let the end use close the report himself

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.

[]

"Marshall Barton" wrote
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