Report appears behind other forms 1

[ShabanaHafiz]

I am using Microsoft Access 2003.

I created a selection criteria form. This form has two combo boxes; cboFromDate and cboToDate. Report is opened when the user clicks cmdPreviewReport. cmdPreviewReport has the following code:

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

    Dim stDocName As String

    stDocName = "VHRunningMaint"
    DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreviewReport_Click:
    Exit Sub

Err_cmdPreviewReport_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreviewReport_Click
    
End Sub

I have used the code provided in faq705-2562 to Hide The Access Window. This FAQ says:

find the PopUp property for each form and set it to yes. In the OnOpen event of your startup form (if you don't have a startup form, just pick the first form you open when you open the database), put the following code:

DoCmd.RunMacro "mcrHide"

Finally, to allow reports to be previewed...in every report you will need to put:

In the OnOpen: DoCmd.RunMacro "mcrRestore"
In the OnClose: DoCmd.RunMacro "mcrHide"

So the code for open and close events are as follows:

Private Sub Report_Close()
DoCmd.RunMacro "mcrHide"
End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.RunMacro "mcrRestore"
End Sub

Selection Criteria form, rpt1Select, has its PopUp property set to Yes.

When Selection Criteria form is opened and cmdPreviewReport is clicked, report appears behind the two forms; rpt1Select and Switchboard. When the report is clicked, it does not get the focus. When closeform button is clicked on rpt1Select form, still the report does not get focus upon clicking. When Exit button is clicked on Switchboard, everything closes and an error message is displayed:
The RunMacro action was canceled.

What I need is: When cmdPreviewReport is clicked, to show report above all form windows and to have focus on report.

 

[SgtJarrow]

You need to do one of two things in addition to your current code. The first is to set the PopUp property for both the forms to False when the report is opened, and set the PopUp property back to True for each of the forms when the report is closed. The other option is to hide the forms when the report is opened and redisplay them when the forms closes. This can be added to the Report_Open and Report_Close code you already have.

Private Sub Report_Close()
Forms![Switchboard].Visible = True
DoCmd.RunMacro "mcrHide"
End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.Close acForm, "rpt1Select" ' You can close this since you have "selected" the report to view and don't need it anymore
Forms![Switchboard].Visible = False
DoCmd.RunMacro "mcrRestore"
End Sub

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[ShabanaHafiz]

Thanks.

I tried both options.

In the first option, the code was as follows:

Private Sub Report_Close()
Forms![rpt1Select].PopUp = True
Forms![Switchboard].PopUp = True
DoCmd.RunMacro "mcrHide"
End Sub

Private Sub Report_Open(Cancel As Integer)
Forms![rpt1Select].PopUp = False
Forms![Switchboard].PopUp = False
DoCmd.RunMacro "mcrRestore"
End Sub

In this option, it stopped at the following line of Report_Open event:
Forms![rpt1Select].PopUp = False

with the following error message:
You can't assign a value to this object

In the second option, DoCmd.Close did not work and Enter Parameter dialog box appeared asking for cboFromDate and cboToDate.

Finally, the following code worked:

Private Sub Report_Close()
DoCmd.RunMacro "mcrHide"
Forms![Switchboard].Visible = True
DoCmd.Close acForm, "rpt1Select"
End Sub

Private Sub Report_Open(Cancel As Integer)
Forms![rpt1Select].Visible = False
Forms![Switchboard].Visible = False
DoCmd.RunMacro "mcrRestore"
End Sub

 

[SgtJarrow]

Glad you got it to work. Sometimes the suggestions we provide here are not exact for your scenario. I am glad you found a working solution.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[ShabanaHafiz]

Thank you.

I encountered one more issue. All forms open as one window without having any Access Menu bar. But when cmdPreviewReport is clicked, report opens with an Access Menu bar above it. This Access Menu bar has options of File, Window and Help.

If I click the upper right x of report, report closes and Switchboard becomes visible again. But if I click upper right x of the window with the caption Microsoft Access, program stops with the following error message:
Microsoft Office Access can’t find the form ‘Switchboard’ referred to in a macro expression or Visual Basic code.

I changed the Modal property of Report to Yes. But still, upper right x of Microsoft Access window can be clicked, while the report is open.

 

[SgtJarrow]

This is an issue. This error occurs because by clicking the Access X you are closing Access. While closing Access, the report is closed and the report has code to open the Switchboard back up when it closes. But Access can't open the Switchboard because the whole application is closing down.

You have a couple options here...none of which are a "simple" solution. The first, and probably most effect in your case, is the disabling/removing of the X in the Access window. I personally haven't done this but there are a couple examples here on Tek-Tips. Do a search and if you can't find any, let me know and I will see if I can help you locate one. The second is to handle the error yourself and try to override it. Again not something I have personally done. In VB.Net, I would check for an open instance of a form or check against a declared variable. If they do not match what I am looking for, I would cancel the application's exit. I know some of this is available in Access, but I don't know which parts as Access is not my primary language much anymore. Finally, the last option and they one I have used the most, is educating the user. Basically my users here know not to click the Access X when they are viewing reports. Again, I don't use Access much anymore so this is not so much an issue for me, but they just know if they do it they will get an error and have to reopen the app.

Maybe someone else can help you specifically with this error better than I can, but I will try as you let me know you are having problems.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[ShabanaHafiz]

Thanks.

I used the code provided in this thread; thread702-1330802
I created a new module, Module2. Module2 has the following code:

Option Compare Database

'Calvin Smith
'[URL unfurl="true"]http://www.CalvinSmithSoftware.com/codedisk/sneakpeek.htm[/URL]
'Tip # 19
'**********************************************************
'************ Code provided by CodeDisk II ****************
'**********************************************************

'We need the following API declarations first

Private Declare Function apiEnableMenuItem Lib "user32" Alias _
    "EnableMenuItem" (ByVal hMenu As Long, ByVal wIDEnableMenuItem As Long, _
     ByVal wEnable As Long) As Long
     
Private Declare Function apiGetSystemMenu Lib "user32" Alias _
         "GetSystemMenu" (ByVal hwnd As Long, ByVal flag As Long) _
         As Long
Const MF_BYCOMMAND = &H0&
Const MF_DISABLED = &H2&
Const MF_ENABLED = &H0&
Const MF_GRAYED = &H1&
Const SC_CLOSE = &HF060&

Public Function EnableDisableControlBox(bEnable As Boolean, _
    Optional ByVal lhWndTarget As Long = 0) As Long

On Error GoTo ErrorHandling_Err

    ' ----------------------------------------------------------------------
    ' Purpose: Example of how to disable or enable the control box of
    '          a form, report, or the Access parent window.
    '
    ' Accepts: bEnable, which determines whether to disable or enable
    '          the control box
    '
    '          Also accepts lhWndTarget (which is optional), if you want
    '          to use a window handle other than the Access parent window.
    '
    ' Returns: N/A
    '
    ' Example usage: lRetVal = EnableDisableControlBox(True) to enable -OR-
    '                lRetVal = EnableDisableControlBox(False) to disable
    '
    ' NOTE: If no hWnd is passed in for a specific form, then the code
    '       assumes you want to enable/disable the Access parent window
    ' ----------------------------------------------------------------------
Dim lhWndMenu As Long
Dim lReturnVal As Long
Dim lAction As Long

lhWndMenu = apiGetSystemMenu(IIf(lhWndTarget = 0, Application.hWndAccessApp, lhWndTarget), False)

If lhWndMenu <> 0 Then
     If bEnable Then
        lAction = MF_BYCOMMAND Or MF_ENABLED
     Else
        lAction = MF_BYCOMMAND Or MF_DISABLED Or MF_GRAYED
     End If
     lReturnVal = apiEnableMenuItem(lhWndMenu, SC_CLOSE, lAction)
End If

EnableDisableControlBox = lReturnVal

ErrorHandling_Err:
    If Err Then
        'Trap your error(s) here, if any!
    End If
End Function

I added call to this function in the SwitchBoard Open event:

Option Compare Database
Dim RetVal As Variant

Private Sub Form_Open(Cancel As Integer)
' Minimize the database window and initialize the form.

    ' Move to the switchboard page that is marked as the default.
    Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
    Me.FilterOn = True
    [blue]RetVal = EnableDisableControlBox(False)[/blue]
    
End Sub

Now when cmdPreviewReport is clicked, report opens with an Access Menu bar above it. But X in the upper right corner of Access Menu bar is disabled and user cannot close Access.