How to close an Access report by pressing Escape (Esc) key

Ryan Chong
CERTIFIED EXPERT
The best way to learn is to teach
Published:
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.

In this case, few things we need to figure it out.


1. How to detect a Escape (Esc) key press in the opened report?

2. How to close the report after the detection of Escape key pressed?


To resolve this issue, some macro scripts can be added to resolve this.


As quoted in this article: Introduction to macros

A macro is a tool that allows you to automate tasks and add functionality to your forms, reports, and controls. For example, if you add a command button to a form, you associate the button's OnClick event to a macro, and the macro contains the commands that you want the button to perform each time it is clicked.


Test 1: Adding macro code in the Report_KeyDown event


First of all, we need to know what's the Report's KeyDown event.


As quoted in this article: Report.KeyDown Event (Access)

The  KeyDown event occurs when the user presses a key while a report has the focus. This event also occurs if you send a keystroke to a report by using the SendKeys action in a macro or the SendKeys statement in Visual Basic.


To add the event, we can:

1. Open the report in design view

2. Go to the Property Sheet and click on Event tab

3. Look for On KeyDown and select [Event Procedure], and then click the "..." button.


4. Enter the following code in the Visual Basic Editor


Private Sub Report_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = 27 Then 'Detect the escape key
        DoCmd.Close acReport, Me.Name
    End If
End Sub

5. Save the report

6. Open the report.

7. You will see that once you pressed the Escape (Esc) key, the report is closed.


This is pretty cool!


Test 2: Adding macro code in the Report_KeyPress event


Similarly for Test 1, we need to know what's the Report's KeyPress event.


As quoted in this article: Report.KeyPress Event (Access)

The  KeyPress event occurs when the user presses and releases a key or key combination that corresponds to an ANSI code while a report has the focus. This event also occurs if you send an ANSI keystroke to a report by using the SendKeys action in a macro or the SendKeys statement in Visual Basic.

To add the event, we can:

1. Open the report in design view

2. Go to the Property Sheet and click on Event tab

3. Look for On Key Press and select [Event Procedure], and then click the "..." button.


4. Enter the following code in the Visual Basic Editor


Private Sub Report_KeyPress(KeyAscii As Integer)
    If KeyAscii = 27 Then 'Detect the escape key
        DoCmd.Close acReport, Me.Name
    End If
End Sub

5. Save the report

6. Open the report.

7. You will see that once you pressed the Escape (Esc) key, the report is closed.


This code is working!


But recently after viewing a question posted in the forum: ESC not closing report after zoom


The Problem:


It seems that Access will throw an error when the report is in Print Preview mode. 


1. For the KeyDown event in Test 1, it will throw with an error stated below.


2. For the KeyPress event in Test 2, if the report is in zooming (not 100%), the code is not responsive and the report is not closed.


To resolve these issues, we will make some amendments to the existing code by introducing a Win API call to simulate the close action to the report.


Test 3: Adding macro code in the Report_KeyDown event with a Win API call


Repeat the same steps as stated in Test 1 and this time we include the following code:


Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
Private Const WM_CLOSE = &H10

Private Sub Report_KeyDown(KeyCode As Integer, Shift As Integer)
    On Error GoTo Err
    If KeyCode = 27 Then 'Detect the escape key
        DoCmd.Close acReport, Me.Name
    End If
    Exit Sub
Err:
    'To capture the error number
    If Err.Number = 2585 Then
        PostMessage Me.hwnd, WM_CLOSE, CLng(0), CLng(0)
    End If
End Sub


Now, the error is gone and the report can be closed successfully in both Report View and Print Preview.


Test 4: Adding macro code in the Report_KeyPress event with a Win API call


Before we do the same by adding the Win API call just like what we did in Test3, it seems that the KeyPress event failed to trigger in Print Preview mode.


Private Sub Report_KeyPress(KeyAscii As Integer)
    On Error GoTo Err
    If KeyAscii = 27 Then 'Detect the escape key
        DoCmd.Close acReport, Me.Name
    End If
    Exit Sub
Err:
    MsgBox Err.Number & ":" & Err.Description
End Sub

There is no error detected in Report_KeyPress event while the report is in Print Preview mode. Hence, it seems that the KeyPress event is not a good choice to close a report that currently in Print Preview mode.


Summary

To draw a conclusion, it seems that the result of Test 3 is best to suit the resolution of closing an Access report, which involves the combination of code from the Report_KeyDown event with a Win API call.


What Else to Consider?

Yes, you may know that the test in Layout View is not included and I will discuss this in my next article.

0
4,024 Views
Ryan Chong
CERTIFIED EXPERT
The best way to learn is to teach

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.