Howto: make reports top-most

[Carl Colijn]

Hi all,

My situation:
I have a maximized main form with a "Reports" button and several subforms on
it. The Reports button needs to open a dialog asking the users what reports
to generate. This report-generating form needs to be modal to the main
form, and may not get maximized; acDialog seems like the perfect match. The
report dialog has some selection controls, a "Generate" and an "Exit"
button. The report form should stay open after generating a report, since
the user might want to generate more than one report.

In this setup, the reports open below the report form (also maximized),
which is fine by me. But when the report form closes, the main form is
lifted up in the z-order and so hides the freshly generated reports; a bit
of an awkward situation to explain to my users. Since reports do not have a
SetFocus method, I cannot re-apply their topmost position in the Report
button code. Relying on a ShowWindow API call with their hWnd is a bit
unnerving to me here (does Access know afterwards which is the object with
the focus in all cases?).

I have tried calling a macro in the Report button that in turn opens the
Reports form in acDialog mode, hoping to loosen the connection of the
Reports form from the main form: it didn't work. Another thing I tried was
opening a dummy form in normal mode, that in turn opens the Reports form in
acDialog mode in it's Open event and then terminates via Cancel = True; same
results again. Using the same dummy form, this time not relying on the
Open/Load/Activate chain events (since they might get called synchronously
in the Report button's context?) but on the Timer event it works, but now I
cannot get this dummy form not to show itself (opening it acHidden just
makes all code stop working and Me.Visible doesn't do a thing, even with
DoEvents).

Another approach might be not to use true Maximized forms but to use the API
to make the forms fill up the whole MDI client area. However, I imagine
that when the user resizes the main Access app in this case, the forms and
reports stay at their old size, which is also not desireable.

Does anyone have a suggestion?

Kind regards,
Carl Colijn

 

[Guest]

Try using

DoCmd.SelectObject acReport, strReportName

Where strReportName as the name of the report.

Hope this helps.

 

[Carl Colijn]

In

Try using

DoCmd.SelectObject acReport, strReportName

Where strReportName as the name of the report.

Hi Andrew,

Thanks for the suggestion, but I create multiple copies of the same report
in VBA via New (Set NewReport = New Report_MyReportName). I cannot
distinguish between these copies using SelectObject, since all copies
register themselves under the same name (MyReportName) in the Reports
collection.

Carl

 

[Carl Colijn]

In

Hi all,

My situation:
I have a maximized main form with a "Reports" button and several
subforms on it. The Reports button needs to open a dialog asking the
users what reports to generate. This report-generating form needs to
be modal to the main form, and may not get maximized; acDialog seems
like the perfect match. The report dialog has some selection
controls, a "Generate" and an "Exit" button. The report form should
stay open after generating a report, since the user might want to
generate more than one report.
In this setup, the reports open below the report form (also
maximized), which is fine by me. But when the report form closes,
the main form is lifted up in the z-order and so hides the freshly
generated reports; a bit of an awkward situation to explain to my
users. Since reports do not have a SetFocus method, I cannot
re-apply their topmost position in the Report button code. Relying
on a ShowWindow API call with their hWnd is a bit unnerving to me
here (does Access know afterwards which is the object with the focus
in all cases?).
I have tried calling a macro in the Report button that in turn opens
the Reports form in acDialog mode, hoping to loosen the connection of
the Reports form from the main form: it didn't work. Another thing I
tried was opening a dummy form in normal mode, that in turn opens the
Reports form in acDialog mode in it's Open event and then terminates
via Cancel = True; same results again. Using the same dummy form,
this time not relying on the Open/Load/Activate chain events (since
they might get called synchronously in the Report button's context?)
but on the Timer event it works, but now I cannot get this dummy form
not to show itself (opening it acHidden just makes all code stop
working and Me.Visible doesn't do a thing, even with DoEvents).

Another approach might be not to use true Maximized forms but to use
the API to make the forms fill up the whole MDI client area. However, I
imagine that when the user resizes the main Access app in
this case, the forms and reports stay at their old size, which is
also not desireable.
Does anyone have a suggestion?

Hi all,

I have solved the problem! Since I've seen posts like this pop up every now
and then, I thought maybe others could benefit from this as well. After
all: it's not such a strange design to want to have a popup dialog shown
from your form, from which you can enter parameters to generate your reports
with. You should be able to generate more than one report of the same kind
simultaneously, having different parameters. It would be nice if these
reports would stay on top after the parameter dialog was closed. It would
be super-nice if (after dismissing the parameter form) the reports would
co-exist with the main form in a nice way (all being normal windows, so you
could select the report and then the form and vice versa). And it would be
even nicer if we would not have to rely on the Windows API to force Access
into performing the way we want it to.

The only drawback to the current setup is that the reports initially open
below the parameter form, but then you could see this as an advantage as
well: the user can instantly generate another one if they like, but they
still see something happening in the back so they know it works.

There are two forms and one report in this setup. Their properties are:

Main form:
- not modal, not popup, sizeable, maximized (the normal Access way via
DoCmd.Maximize)
- has a "Generate reports" button, which opens the Reports form

Reports form:
- popup is set but modal is NOT set; this is crucial! If you do set modal,
then the reports will fall below the main form.
- has a "Generate report" button, which opens the report

Report:
- not modal, not popup, sizeable

The VBA code I used is:
- The "Generate Reports" button in the main form opens the "Reports" form in
a normal way, so not hidden and especially not in acDialog mode;
Call DoCmd.OpenForm("Reports")

- The "Generate Report" button in the "Reports" form creates a new report
via VBA:
Dim sSelection As String
< set sSelection to the selection you would like to use >
Dim sSorting As String
< set sSorting to the sorting you would like to use >
Dim oNewReport As Report
Set oNewReport = New Report_MyReportName
Call oNewReport.Show(sSelection, sSorting)

- In a global module you add some code to maintain a collection in which you
store the reports so they do not auto-destruct when the above variable
"oNewReport" goes out of scope;
' All open reports
Private m_aoReports As Collection

' Open report management
Public Sub LockReport(oReport As Report)
' Look if we already have a reports collection
If m_aoReports Is Nothing Then
' No -> create one now
Set m_aoReports = New Collection
End If

' And lock the given report into staying open
Call m_aoReports.Add(oReport, "" & oReport.hWnd)
End Sub
Public Sub UnlockReport(oReport As Report)
' Release the lock on the given report so it can close properly
On Error Resume Next
Call m_aoReports.Remove("" & oReport.hWnd)
End Sub

- The report has code in both it's Close and Open event so as to lock it
into staying open. The reason I do not lock it with "LockReport" in the
routine that creates the reports itself is that if you apply a sorting or
filtering on the report via code, it will close and re-open out of itself;
it thus better also re-lock itself via the routine "LockReport";
Private Sub Report_Close()
' Properly release the lock on us
Call UnlockReport(Me)
End Sub

Private Sub Report_Open(bCancel As Integer)
' Lock us into existance
Call LockReport(Me)

' And maximize the report
Call DoCmd.Maximize
End Sub

- Lastly, the report has a public function you can call to set it's sorting
and/or filtering, since we cannot pass those options in via a
DoCmd.OpenReport call. And since the report will open invisible, this is
also a nice time to show it as well;
' Shows the report
Public Sub Show(sSelection As String, sSorting As String)
' Only show the selection
Me.Filter = sSelection
Me.FilterOn = True

' Apply the sorting
Me.OrderBy = sSorting
Me.OrderByOn = True

' And show the report
Me.Visible = True
End Sub

I hope this is of any use to anyone! So far I have only tested it on Access
2003, Dutch edition, on Windows XP. Since it was quite a balancing act
between modal/popup settings, form opening code and VBA code to get this
working, I wouldn't be too surprised if it wouldn't work on another Access
version though... If anyone can test it out for me, I would be very
gratefull! (especially on Access XP)

Kind regards,
Carl Colijn

--
TwoLogs - IT Services and Product Development
A natural choice!
http://www.twologs.com
TimeTraces: the powerfull and versatile time registration system!
http://www.twologs.com/en/products/timetraces.asp