Access VBA: THE THING YOU NEED TO BE AWARE OF WHEN CREATING EVENT LISTENER BY USING WITHEVENTS FOR FORM CONTROLS

Nov 8, 2020 · 6 min read · Access VBA HOMEMADE object-oriented ·

Overview

Help me improve my English!

As you probably see, I'm not a native English speaker.
If you find an English expression that feel incorrect or awkward, please let me know.

Message box of Disqus is under the article.
Or my E-Mail is here.

ABOUT THIS ARTICLE

I tried to create a event listener class by using WithEvents statement to standardize event processing for controls on a forms in MSAccess.
At that time, when using the code which went well in MS Excel, I've gotten the result that the class side events that I've set up won't fire.

I would like to describe what happened and the two types of measures taken.

You can download the tool and view its source code from here!

WHAT I CREATED FIRST

I made the combobox control surrounded by a circle as follows not be input manually with the keyboard.

For standardization, the processing of combobox KeyDown event is delegated to a newly created event listener class (in case the number of controls increased in the future).

For verification, I have some of the combobox events pass a textbox its event information as a log.

SCREEN

Screen

THE ROLE OF EACH OBJECT

Name	Type	Explanation
cmb_withEventsTest	Combobox	Allow input only from the pullDown. Prohibit manual input
txt_EventLog	TextBox	display event log of the combobox

CODE

The code below does not work as myComboBox_KeyDown function is not fired.

MAIN FOMR MODULE

  1Option Compare Database
  2Option Explicit
  3
  4'**************************
  5'*MainForm
  6'**************************
  7
  8'Const
  9
 10
 11'Variable
 12Private objCmbListener As clsCmbListener
 13
 14
 15Private Sub Form_Load()
 16    
 17    'Const
 18    Const FUNC_NAME As String = "Form_Load"
 19    
 20    'Variable
 21    Dim dicInfo As Object
 22    
 23    On Error GoTo ErrorHandler
 24
 25    'set Event Class
 26    Set objCmbListener = New clsCmbListener: Set objCmbListener.ComboBox = Me.cmb_withEventsTest
 27    
 28    'set Event Log
 29    Set M_EventLog.targetTxtBox = Me.txt_EventLog
 30    
 31ExitHandler:
 32
 33    Exit Sub
 34    
 35ErrorHandler:
 36
 37    MsgBox "An error has occurred and the macro will be terminated." & _
 38           vbLf & _
 39           "Func Name:" & FUNC_NAME & _
 40           vbLf & _
 41           "Error No." & Err.Number & vbNewLine & _
 42           Err.Description, vbCritical, "Access-Control-WithEvents"
 43        
 44    GoTo ExitHandler
 45        
 46End Sub
 47
 48
 49Private Sub cmb_withEventsTest_BeforeUpdate(Cancel As Integer)
 50    
 51    'Const
 52    Const FUNC_NAME As String = "cmb_withEventsTest_BeforeUpdate"
 53    
 54    'Variable
 55    
 56    On Error GoTo ErrorHandler
 57
 58    'do logging
 59    If Not M_EventLog.writeEventLogs(FUNC_NAME) Then GoTo ExitHandler
 60
 61ExitHandler:
 62
 63    Exit Sub
 64    
 65ErrorHandler:
 66
 67    MsgBox "An error has occurred and the macro will be terminated." & _
 68           vbLf & _
 69           "Func Name:" & FUNC_NAME & _
 70           vbLf & _
 71           "Error No." & Err.Number & vbNewLine & _
 72           Err.Description, vbCritical, "Access-Control-WithEvents"
 73        
 74    GoTo ExitHandler
 75        
 76End Sub
 77
 78Private Sub cmb_withEventsTest_AfterUpdate()
 79    
 80    'Const
 81    Const FUNC_NAME As String = "cmb_selectedRcd_AfterUpdate"
 82    
 83    'Variable
 84    
 85    On Error GoTo ErrorHandler
 86    
 87    'do logging
 88    If Not M_EventLog.writeEventLogs(FUNC_NAME) Then GoTo ExitHandler
 89    If Not M_EventLog.writeEventLogs("""" & Me.cmb_withEventsTest.Value & """" & "Selected") Then GoTo ExitHandler
 90
 91ExitHandler:
 92
 93    Exit Sub
 94    
 95ErrorHandler:
 96
 97    MsgBox "An error has occurred and the macro will be terminated." & _
 98           vbLf & _
 99           "Func Name:" & FUNC_NAME & _
100           vbLf & _
101           "Error No." & Err.Number & vbNewLine & _
102           Err.Description, vbCritical, "Access-Control-WithEvents"
103        
104    GoTo ExitHandler
105        
106End Sub

COMBOBOX EVENT LISTENER CLASS

 1Option Compare Database
 2Option Explicit
 3
 4'**************************
 5'*Combobox Event Listener
 6'**************************
 7
 8'Const
 9
10'Variable
11Private WithEvents myComboBox As Access.ComboBox
12
13'******************************************************************************************
14'*getter/setter
15'******************************************************************************************
16Public Property Set ComboBox(ByRef cmb As Access.ComboBox)
17    Set myComboBox = cmb
18    myComboBox.OnKeyDown = "[Event Procedure]"
19End Property
20
21
22
23
24
25'******************************************************************************************
26'*Function ：disable keyboard input
27'*Arg(1)   ：key code
28'*Arg(2)   ：shft key pressed or not
29'******************************************************************************************
30Private Sub myComboBox_KeyDown(KeyCode As Integer, Shift As Integer)
31    
32    'Const
33    Const FUNC_NAME As String = "myComboBox_KeyDown"
34    
35    'Variable
36    
37    On Error GoTo ErrorHandler
38    
39    'prohibit entering (except fror Enter/Tab/Esc)
40    If KeyCode = vbKeyReturn Then GoTo ExitHandler
41    If KeyCode = vbKeyTab Then GoTo ExitHandler
42    If KeyCode = vbKeyEscape Then GoTo ExitHandler
43    
44    KeyCode = 0
45    
46    If Not M_EventLog.writeEventLogs(FUNC_NAME) Then GoTo ExitHandler
47    
48ExitHandler:
49
50    Exit Sub
51    
52ErrorHandler:
53
54    MsgBox "An error has occurred and the macro will be terminated." & _
55           vbLf & _
56           "Func Name:" & FUNC_NAME & _
57           vbLf & _
58           "Error No." & Err.Number & vbNewLine & _
59           Err.Description, vbCritical, "Access-Control-WithEvents"
60        
61    GoTo ExitHandler
62        
63End Sub
64

EVENT LOG MODULE

 1Option Compare Database
 2Option Explicit
 3
 4
 5'**************************
 6'*Event Log Module
 7'**************************
 8
 9'Const
10
11
12'Variable
13Public targetTxtBox As Access.TextBox
14
15
16'******************************************************************************************
17'*Function ：write the event log into the textbox specified in a module variable
18'*Arg(1)   ：the written string
19'*Return   ：True > normal termination; False > abnormal termination
20
21'******************************************************************************************
22Public Function writeEventLogs(ByVal logTxt As String) As Boolean
23    
24    'Const
25    Const FUNC_NAME As String = "writeEventLogs"
26    
27    'Variable
28    
29    On Error GoTo ErrorHandler
30
31    writeEventLogs = False
32    
33    If Nz(targetTxtBox.Value, "") <> "" Then targetTxtBox.Value = targetTxtBox.Value & vbNewLine
34    targetTxtBox.Value = targetTxtBox.Value & _
35                         Now & _
36                         " : " & _
37                         logTxt
38    
39    writeEventLogs = True
40    
41ExitHandler:
42
43    Exit Function
44    
45ErrorHandler:
46
47    MsgBox "An error has occurred and the macro will be terminated." & _
48           vbLf & _
49           "Func Name:" & FUNC_NAME & _
50           vbLf & _
51           "Error No." & Err.Number & vbNewLine & _
52           Err.Description, vbCritical, "Access-Control-WithEvents"
53        
54    GoTo ExitHandler
55        
56End Function
57

TROUBLE

The combobox KeyDown event is Supposed to be captured by myComboBox_KeyDown function in clsCmbListener class through the mechanism of WithEvents,
and Keystrokes should be prohibited except for some keys such as Enter and Tab.

However, I saw the combobox allowing manual input.

Furthermore, since the log which indicates myComboBox_KeyDown has called is not displayed in the textbox, WithEvents is not working as expected in the first place.

In the case of MS Excel, above went well. So I pondered what to do for a while.

THE SOLUTION

i. ADD KEYDOWN EVENT FUNCTION INTO THE FORM MODULE AS WELL

EXPLANATION

I added a function whose processing is empty.

 1Option Compare Database
 2Option Explicit
 3
 4'**************************
 5'*MainForm
 6'**************************
 7
 8'Const
 9
10
11'Variable
12Private objCmbListener As clsCmbListener
13
14
15Private Sub cmb_withEventsTest_KeyDown(KeyCode As Integer, Shift As Integer)
16'empty
17End Sub

By doing so, the KeyDown event turned to be called.

ONE FURTHER PROBLEM

However, this way contains one further problem.

When above cmb_withEventsTest_KeyDown function is truly empty, VBE mechanism automatically delete the function during compiling phase because it's not necessary.

So, it has to have one comment row and escape the deletion, that makes the tool less maintainable.
Moreover, when others see this code, they might consider it unnecessary and delete it.

ii. SET [EVENT PROCEDURE] TO THE ONKEYDOWN PROPERTY OF THE COMBOBOX INSTANCE

The solution is taken from this stackoverflow

As above articel says, [Event Procedure] is the key of the solving the problem.

1listener.ct.OnClick = "[Event Procedure]"  '<------- Assigned the event handler

I applied this logic to my own code.

 1'**************************
 2'*Combobox Event Listener
 3'**************************
 4
 5'******************************************************************************************
 6'*getter/setter
 7'******************************************************************************************
 8Public Property Set ComboBox(ByRef cmb As Access.ComboBox)
 9    Set myComboBox = cmb
10    myComboBox.OnKeyDown = "[Event Procedure]"
11End Property
12
13

After that, my code started to work fine!

Translations:

"Japanese" |