Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Streamlining Form Module Code Part Three


This is the continuation of the last two Articles published earlier on this topic. Hope you have gone through the Title topic: Streamlining Form Module Code.  By now you have a general idea of where we are heading, with the main points on the Event, RaiseEvent, and WithEvents declarations and how they work together. 

There is only one goal: that is to shift the VBA Coding from Form Module to the stand-alone Class Module and use the Form for User Interface Designing only.  Is it possible? Yes, it is possible, that is what we are going to prove through these pages. Besides that when the new VBA Coding procedure is implemented part of the work can be transported to new Projects for ease of Coding there.

With this new method of Coding procedure, we will be able to open the Code Modules independently and work without struggling with the Form Design every time, saving a good deal of manual effort.  This new approach in Coding will serve the purpose of faster completion of Projects. The procedure which we plan to devise will work together with Objects on the Form as they do normally &  efficiently as far as the end user is concerned. Besides that, the backbone of this procedure can be exported into other Projects for ease of Coding there.

But, I think it is interesting to learn first, how we are made to do the Coding always on the Form Module. Once we know that we could implement the procedure the way we plan to do it and make it useful in other Projects also.

For achieving that it needs to lay down a proper procedure so that Access Developers understands them and use them routinely. Before doing that, we should know how the existing method of coding works on the Form Module, and what tricks are kept hidden behind the Form to make us do the coding in the Form Module itself.

The earlier Article Links:

  1. Re-using Form Module VBA Coding for New Projects.
  2. Streamlining Form Module Coding Part-Two.

Do you have any idea as to how and why we are asked to write VBA Code on the Form Module itself for Event Procedures? How the Events are Raised and the Code is executed when an Event occurs. It is interesting to explore and find out these aspects of Coding. Once we know the inner workings of this trick and a few basic rules goes with it, then we can think of refining the existing procedure and doing it differently. 

What happens when we add a control to the Form? How does the code we write in the Event Procedure gets executed when an event occurs?  Is there any other alternative approach that can give us the same result and ease of Coding?

Events and Objects Hierarchy.

Let us study what happens when you add a Textbox Object to the Form. The diagram of a Textbox on the Form and all things associated with it, which we should know about are shown below.

As shown in the above diagram, we have added a Textbox Control in the Detail Section of the Form.  To be more specific and technically correct to say that we have added a Textbox object Instance in the Form. 

We normally create several instances of MS Access Textbox objects on the Form. Every time when we add a Textbox object instance to the Form, Access System redefines the Name Property of the Textbox Object as WithEvents Text0 As TextBox as shown in the diagram above, the Label with a dark background and in grey letters.  We can change the Name Property value Text0, or any other default name inserted by MS Access, to a meaningful name like Quantity.

Why do we need such a declaration of Textbox object instance with the keyword WithEvents, we have already seen that in the earlier episodes of this topic.  It enables capturing the inbuilt Events of the Textbox when fired (RaiseEvent) and executes the Event Procedure Code written on the Form Module.

Two selected Properties of the Textbox are shown in the diagram above: Change and On Change Properties. The Change Event Property will not appear in the Property Sheet of the Textbox object. We have seen how an Event Property is declared in our User-defined Event examples in earlier articles.

The On Change Event Property only appears in the Property Sheet of the Textbox.  When we select the text [Event Procedure] option from the drop-down control in this Property, we could write the Event Procedure Code in the Form Module for the intended task of Change Event. Therefore, this is the RaiseEvent (Announcer) Property for the Event Change

Whenever we type some text in Text0 Textbox, the Change Event fires (or triggers the Subroutine, with the Event name Change and the parent object Text0 name (Msg in the Name Property sample Code given below) as Prefix - Sub Text0_Change()) for each character typed.

Private Sub Msg_Change()
'Announce/Transmit the Event
    RaiseEvent Message(Me!Msg.Text)
End Sub

So both the Event and the WithEvents Property declarations are not normally shown on the Property Sheet of any Object Instance created within the Form, except the Name Property. The WithEvents declaration goes with the Name Property when an object instance is added to the Form.

Text0 object's inbuilt Event-related Procedure runs with its Parent Object name (Text0) as the event name prefix, and the Event Procedure is written in the Form Module, in other words, the Event Procedure Code must be written in the Parent Class Module of the Textbox Object instance. 

So when we visualize the hierarchy of objects and Events, the Form Class Module is the top-level container of other Objects on the Form, like Textbox. Conversely, Textbox's Parent Object is the Form Class Module. The Textbox object is the parent object of its inbuilt Events and captures them when the Events like AfterUpdate, Change, and GotFocus fires.

Events On Form and Class Module.

The Event Procedure name must be written with the Parent Object name as a Prefix like Sub Text0_AfterUpdate(). Following the same rule, the Event Procedure must be coded in the Textbox object's Parent Object - the Form Class Module. When an Event of the Textbox Object is fired the parent object Textbox captures it (the WithEvents declaration enables it to do that) and the Event Procedure, which is written in the Textbox Object's Parent Form Class Module, executes the Event Procedure-based task. Here you can see a pattern forming as to how the object event handling is concerned.

Text0 has its own inbuilt Event Collection. When any of them is Raised, the parent object Text0 captures it. Because the Textbox Object instance Text0 is declared on the Form (or wherever an instance is created) with the WithEvents keyword, then the VBA Code must be written in the Parent Class Module of the Text0 object. Our example is concerned, the Textbox object's parent Class Module is the Form's Class Module.

The Event Procedure Code can be written only on the Text0 object’s Parent Class Module, that is the Form's Class Module. Hence, the Event Subroutines in this case are written in the Form’s Class Module as shown above.

If we create an instance of the same Text0 object in a stand-alone Class Module (say Class1) and qualify it with the WithEvents keyword then you can capture the Text0 Events from the Form Class Module and write the Event Procedures in Class1 Class Module.

Create a Demo Form.

Let us try an example to prove this rule that is applied to the stand-alone Class Module too.

Form1 Class1
  1. Create a new Form.

  2. Add two Text Boxes on the Form, one below the other.

  3. Click on the First Text Box and display its Property Sheet.

  4. Change the Name Property value to Quantity.

  5. Change the Caption of the Child Label to Max Quantity (1 - 10).

  6. Select the Quantity control's Property Sheet and select the [Event Procedure] Option in the After Update Event Property and click on the Build (. . .) Button to open the Form's Class Module.

    The Form Module VBA Code.

  7. Copy the following VBA codes and Paste them into the Form's Class Module, overwriting existing lines.

    Option Compare Database
    Private C As Class1 'Declare a Class1 Object Variable
    Private Sub Form_Load()
      Set C = New Class1 'Instantiate the Class1 Class Module
    Set C.Txt = Me.Quantity 'Assign Quantity Textbox Object to txt Property End Sub Private Sub Form_Unload(Cancel As Integer) Set C = Nothing End Sub Private Sub Quantity_AfterUpdate() 'Code End Sub Private Sub Quantity_GotFocus() 'Code End Sub Private Sub Quantity_LostFocus() 'Code End Sub
  8. Save the Form with the Name Form1 and Close the Form.

    The Stand-alone Class Module.

Now, we need to create a stand-alone Class Module with the name Class1.

  1. Open VBA Editing Window (ALT+F11)

  2. Select Class Module from Insert Menu.

    If the Class Module name is not Class1, Click on the Properties Button in the Toolbar above to display the Property Sheet then change the name to Class1.

    Note: If you already have a Class Module with the name Class1, then do not change the Class Module Name, instead change the Class1 name in Form Module to match the name of the new stand-alone Class Module Name.

    The Class Module VBA Code.

  3. Copy the following VBA Code and Paste it into the Class1 Class Module:

    Option Explicit
    Public WithEvents Txt As TextBox
    Private Sub txt_AfterUpdate()
    Dim i As Integer, msg As String
    Dim info As Integer
    i = Nz(Txt.Value, 0)
    If i < 1 Or i > 10 Then
        msg = "Valid Value Range 1 - 10 Only."
        info = vbCritical
        msg = "Quantity: " & i & " Valid."
        info = vbInformation
    End If
    MsgBox msg, vbOK + info, "txt_AfterUpdate()"
    End Sub
    Private Sub txt_GotFocus()
    With Txt
        .backcolor = &H20FFFF
        .forecolor = 0
    End With
    End Sub
    Private Sub txt_LostFocus()
    With Txt
        .backcolor = &HFFFFFF
        .forecolor = 0
    End With
    End Sub
  4. Select Save from File Menu or click on the Save Toolbar Button.

  5. Select Compile from the Debug Menu to compile the Code and to make sure that everything is in order.

    We will do a test run first and see how it works. Take note of this Point, we have selected the option [Event Procedure] in the Event Properties to add the empty program stubs on the Form Module (for the RaiseEvent action) for After Update, Got Focus, and Lost Focus Events.

    When we Compile the VBA Code the empty Program stubs will be removed from the Form Module and the [Event Procedure] option selected on the Event Properties will be deleted by Access System. If that happens then our idea will not work as planned. To prevent that we have added a Rem line 'Code in between the empty Event Procedure Stub.

There are other methods we can use for the RaiseEvent action rather than creating empty Subroutine stubs, which we will explore later.

We have written VBA Code in the stand-alone Class Module Class1 to validate the entered Quantity Value (the valid value range is set as 1 to 10 only) and display a message based on the validity of the entered value. This is the trick that you have to watch for, capturing the Event of the Quantity Textbox control on the Form and executing the related Event Procedure Code in the Class1 Class Module.

The GotFocus Event will change the Quantity Textbox's background color to Yellow, and the LostFocus Event will reset the color.

The second Textbox is only a support control, for setting the focus on this control, when the LostFocus event occurs on the Quantity Textbox.

Now, we are all set.

  1. Open Form1 in Normal View.

    You will see the Textbox's background is now in Yellow Color.

  2. Enter the Quantity value 25 in the first Textbox and press Enter Key. You will see the Validation Error message saying that "Valid Value Range 1 - 10 only".

    The first Textbox background color is now reset.

  3. Now enter any value from the range 1 and 10 in the Quantity Textbox again.

    This time you will get the message saying that the Value entered is Valid.

Let us see how this works?

Check the Declaration line of Code for the TextBox Control in the Class1 Class Module:

Public WithEvents Txt As TextBox 

We are creating an Instance of the Textbox object, with the object variable name txt as a Listener object with the declaration of WithEvents Keyword and with Public scope.

The public scope is required to reference this Txt object, outside the Class Module Class1. Class1 Class Module will be Instantiated in the Form1 Class Module. Normally the Public Scope of Class Module Properties is not allowed because it is vulnerable and can get their value changed from outside. 

Class Module Properties are normally declared with Private scope and access to them is allowed through Public Property Procedures. This approach will ensure that the value received through the Property Procedure is valid before assigning it to the Property. But we are on the learning curve and it is ok this way for now. 

The rest of the Event Procedure Code is similar to what we normally write on the Form Module. But, one thing you might have noticed, we are not using the original Textbox name Quantity as the prefix in the Event Procedure subroutine name: Private Sub txt_AfterUpdate()

Because we are capturing the Quantity Textbox Events in the txt Textbox object Instance in Class Module Class1.

  • Creating a Textbox object with the name txt and with the WithEvents keyword alone will not make any automatic relationship with the Quantity Textbox on the Form to capture its Events. We must assign the Quantity Textbox's reference to the txt object in Class1 Module.
  • To do that the Class1 Class Module must be loaded into memory first and then assign the reference of the Quantity Textbox to the txt object, to capture the Events of the Quantity Textbox on the Form. The Class1 Class Module will remain in memory till we close Form1. In our earlier examples, we used two open Forms and their Class Modules for our experiments.

  • This is what we do in the Form's Class Module, in the following Code Segment:

Option Compare Database

Private C As Class1

Private Sub Form_Load()
  Set C = New Class1
  Set C.Txt = Me.Quantity
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Set C = Nothing
End Sub

The statement Private C As Class1 declares a Class1 Object Variable C in the global declaration area of the Form Module.

On the Form_Load() event Procedure the statement Set C = New Class1, the New keyword instantiates the Class1 Object in memory. The next statement Set C.txt = Me.Quantity, assigns the reference of the Quantity Textbox object on the Form to the txt Textbox instance in Class1 Module. The txt Object is declared with the keyword WithEvents so that when an Event, like AfterUpdate, is fired from the Quantity Textbox object it can be captured in Class1 Module and execute the Event Procedure Code written within the Class1 Class Module. To trigger that Event firing action (RaiseEvent) we created the AfterUpdate empty Event Procedure stub on the Form Module.

When the Form is closed the Class1 Class Module Instance in object variable C is cleared from memory.

Next week we will explore other methods to invoke the RaiseEvent action, without keeping the empty Event Procedure stubs on the Form Module. 

Download Demo Database.

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object and Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA-Base Class and Derived Object-2
  5. Base Class and Derived Object Variants
  6. MS-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality

Streamlining Form Module Code Part Two

Understanding Form Controls Event.

I hope you enjoyed last week's Article and demonstration of user-defined custom Events in the Form's Class Module.  We witnessed how Events are Defined, Raised, or fired on the same Form Module. The Raised Events are captured by another open Form's Class Module and trigger the related custom Event Procedure Code. I understand that you likely have many questions regarding this topic of Events and I look forward to exploring it further to gain deeper understanding.

Typically when designing an Access Form, we add various objects, such as TextBoxes, Command Buttons, and ComboBoxes, each with their own pre-defined Events and corresponding event-raising actions. These events are captured by their respective Event Procedures and execute VBA code to accomplish specific task.

In the past, we may have utilized the mechanics of adding objects to an Access form and used their corresponding events without fully understanding how they work. With VBA coding in the form module, we may have become proficient with these features through trial-and-error practice. However, we may not have had the opportunity to delve deeper into the underlying mechanisms and truly understand the inner workings of these processes.

When discussing events on a form, it is important to keep in mind the keywords Event, RaiseEvent, and WithEvents. These keywords are utilized in the following examples, as well as in forthcoming ones.

  1. Defines an Event with the Keyword Event: Public Event QtyUpdate()
  2. Fire the Event with the Keyword RaiseEvent: RaiseEvent QtyUpdate()

  3. Capture the Event in another Form's Class Module: Public WithEvents ofrm as Form. The User-Defined Event cannot be captured in the same Form it is defined?

    The resultant Event Procedure of ofrm_QtyUpdate() Event in the target Form Module:

    Private Sub ofrm_QtyUpdate()
       'VBA Code
    End Sub

User-Defined Event Example-2.

To better understand the usage of the aforementioned keywords, let's consider an example using user-defined events. Suppose we have a textbox that allows users to enter a quantity with certain limitations. We want to pass the entered value to another form via a user-defined event for validation and receive a suitable message in response.

To accomplish this, we can define a public event QtyUpdate() using the Event keyword. We can then raise this event using the RaiseEvent keyword and pass the entered value as an argument. The event can be captured in the class module of another open form using WithEvents and validated using the corresponding event procedure code. Any suitable message can then be displayed to the user based on the validation result.

To put our events into action, let's create two simple forms. In brief, we will define the events in the first form and run the corresponding user event procedure code in the second form's class module.

The events will be defined in the first form, while the second form's class module will capture the events and execute the user event procedure code. By doing so, we can effectively utilize user-defined events to pass values and execute tasks between different forms in Microsoft Access.

  1. Open your Database.

  2. Create a new Form.

  3. Insert a Textbox.

  4. Display the Property Sheet of the Textbox.

  5. Change the Name Property value: OrderQty (stands for Quantity).

  6. Change the child Label Caption value: Quantity  (1 - 5 only).

  7. Insert a Command Button below the Textbox and change its Name Property value to cmdClose.

  8. Change its Caption property to 'Close Form'.

  9. Add another Command Button below the earlier one and change its Name Property Value to cmdOpen.

  10. Change the Caption Property value to 'Open Event Capture Form'

  11. Display the Form's Property Sheet. Set the Record Selectors Property Value No.

  12. Set Navigation Buttons to No.

  13. Set Scroll Bars to Neither.

  14. Right-Click on the TextBox and select Properties from the displayed Menu.

    Event Announcer Form
  15. Select AfterUpdate [Event Procedure] option on the TextBox Property and Click on the Build (. . .) Button to open the VBA Module.

  16. Copy and Paste the following VBA Code into the Form's Module, overwriting existing lines, if any:

    Option Explicit
    'User-Defined Events
    'Captures in Second Form Module.
    Public Event QtyUpdate(mqty As Single)
    Public Event formClose(txt As String)
    Private Sub cmdOpen_Click()
        DoCmd.OpenForm "frmUDCapture", acNormal
    End Sub
    Private Sub OrderQty_AfterUpdate()
    'Announce the Event and pass the OrderQty as Prameter
      RaiseEvent QtyUpdate(Me!OrderQty)
    End Sub
    Private Sub cmdClose_Click()
    'Announce the Event and pass the Text as Parameter
      RaiseEvent formClose("Close the Form?")
    End Sub
    Private Sub Form_Unload(Cancel As Integer)
      DoCmd.Close acForm, "frmUDCapture"
    End Sub
  17. Save the Form with the Name frmUserDefined.

User-Defined Events.

In the global declaration area of the form's class module, we have defined two user-defined events that closely resemble functions.

Public Event QtyUpdate(mqty As Single)
Public Event formClose(txt As String)

In addition to user-defined events, Access has built-in events that also resemble functions, such as Text0_Exit(Cancel As Integer), Form_Unload(Cancel As Integer), and Form_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single).

The user-defined event declarations both begin with the keywords Public Event, followed by the event name and any associated parameters enclosed in parentheses. It is important to note that the event name should not contain an underscore character (e.g., Qty_Update). Additionally, the declaration must have a public scope.

The first user-defined event is designed to validate the contents of the OrderQty textbox whenever it is updated on the form. The second event is triggered by the first command button to close the form and passes a text message as a parameter.

After entering a numeric value into the OrderQty TextBox and pressing the enter key, the OrderQty_AfterUpdate event procedure of the textbox is executed as usual. Within this event procedure, the RaiseEvent QtyUpdate(Me!OrderQty) statement is executed, which triggers the first user-defined event and passes the value of the OrderQty textbox as a parameter.

Private Sub OrderQty_AfterUpdate()
'RaiseEvent and pass the OrderQty as a parameter
  RaiseEvent QtyUpdate(Me!OrderQty)
End Sub

In Access, we typically write event procedures for objects such as TextBoxes and Command Buttons in the form module. These objects are part of the Access system and have their own built-in events and mechanisms for capturing those events. We write event procedures for these Objects in the class module of the form where they reside. Later, we will review how this process works and why it differs from the approach we take with user-defined events. You may have questions about these differences, and we will explore them in more detail shortly.

When you declare the Form1 object Instance with the WithEvents keyword in the form2 module, you can capture the events Raised by the Form1-based object in the Event Procedures defined in the Form2 module.  Instead of Form2 Class Module, it can be in a stand-alone Class Module too. This is because the WithEvents keyword allows the module/class to receive notifications about events raised by the form object, and to respond to them by running the corresponding event procedures.

The user-defined events are not related to any object-based events like the built-in events of a TextBox. The only mechanism for raising a user-defined event is to explicitly call the RaiseEvent statement in the code, as we did in the example.

Additionally, a user-defined event declaration doesn't have any inherent mechanism of its own like a TextBox. It simply defines the event's name, any parameters, it may have, and its public accessibility. It's up to the programmer to implement the code that raises the event and the code that handles the event when it's raised.

It's also worth noting that a user-defined event can be raised only from the Class Module of Form or from a stand-alone Class Module. However, as I mentioned earlier, in order to capture the event in another module, that module must use the WithEvents keyword to declare a reference to the module that will Raise the event.

The RaiseEvent statement within the OrderQty_AfterUpdate() event procedure announces the QtyUpdate() user-defined event and passes the OrderQty value as a parameter. This QtyUpdate() event is then captured in the second form's module, where the event procedure defined in the second form's module is executed, which validates the OrderQty value.

The cmdClose_Click() event procedure in the frmUserDefined Form raises the formClose() event with a message text as the parameter. Then, the second Form's Module captures this event and shows the message text to the User to determine whether to close the Form frmUserDefined or not, based on the User's responses.

This is a simple example of how user-defined events can be used to communicate between different Forms or Class Modules in VBA.

Private Sub cmdClose_Click()
'Announce the Event and pass the Text as Parameter
  RaiseEvent formClose("Close the Form?")
End Sub

Private Sub Form_Unload(Cancel As Integer)
  DoCmd.Close acForm, "frmUDCapture"
End Sub

The Form_Unload() event of the frmUserDefined form is triggered first, as the form is closed, and the frmUDCapture Form is closed first. 

 The Form_Close() event is triggered next after the frmUDCapture form is closed and closes the frmUserDefined Form in the cmdClose_Click() event procedure. 

The User-Defined Event Capturing Form.

  1. Create a new Form and open it in Design View.

  2. Insert two labels as shown in the Image of the Form's Design-view given below.

    Event Capturing Form
  3. Change the first label caption to EVENT MESSAGES.

  4. Select the second label and display its Property Sheet.

  5. Change the Name Property Value to Label2, if it is different.

  6. Change the Caption Property Value to Event.

  7. Display the Form's Property Sheet. Set the Record Selectors Property Value: No.

  8. Set Navigation Buttons to No.

  9. Set Scroll Bars to Neither.

  10. Change the Detail Section Area of the Form as small as shown in the above Image.

  11. Display the Form's VBA Module.

  12. Copy the following VBA Code and Paste it over the existing lines, if any, in the Module.

    Option Compare Database
    Option Explicit
    Public WithEvents ofrm As Form_frmUserDefined
    Private Sub Form_Load()
    On Error Resume Next
    Set ofrm = Forms("frmUserDefined")
    End Sub Private Sub ofrm_QtyUpdate(sQty As Single) Dim Msg As String If Nz(sQty, 0) < 1 Or Nz(sQty, 0) > 5 Then Msg = "Valid Qty Range: 1 - 5 Only" Else Msg = "Order Qty: " & sQty & " Approved." End If Me.Label2.Caption = Msg MsgBox Msg, vbInformation, "QtyUpdate()" End Sub Private Sub ofrm_formClose(txt As String) Me.Label2.Caption = txt If MsgBox(txt, vbYesNo, "FormClose()") = vbYes Then DoCmd.Close acForm, ofrm.Name Else Me.Label2.Caption = "Close Action = Cancelled." End If End Sub
  13. Save the Form with the Name frmUDCapture and Close it.

Event Capturing Form Module VBA Code Review.

Public WithEvents ofrm As Form_frmUserDefined

Private Sub Form_Load()
	On Error Resume Next
	Set ofrm = Forms("frmUserDefined")
End Sub

  1. The line of code Public WithEvents ofrm As Form_frmUserDefined is a crucial global declaration in the second Form's Class Module. It declares a new instance of the first Form, frmUserDefined, as an Event Listener Object variable named ofrm.

    This is important because it enables the second Form to capture and respond to events that are raised by the first Form. By the above declaration, the Listener Object in the second Form can intercept and handle user-defined events raised by the frmUserDefined.

    Without this declaration, the second Form would be unable to capture and respond to events raised by the first Form, and the user-defined event functionality would not work as intended.

    To tell a long story short, we are declaring an Instance of the frmUserDefined Form's  Class Module Object Variable name ofrm. The Form_  prefix to the Form Name indicates that we are referring to the Form's Class Module and creates an Instance of the Class Module.

    The Class Module Object Instance will be assigned to the declared ofrm Object Variable through the frmUDCapture Form's Form_Load() Event Procedure.

    The WithEvents keyword declares the ofrm as a Listener Object to Capture the Events Raised from its original Class Module-based user-defined Events.

    Note: Remember the WithEvents classification can be made only when an instance of any Object, like Form, TextBox, ComboBox, etc., is created.

    The general rule of Object Instance creation as a Listener Property is that:

    • The WithEvents keyword is used in the parent Class Module to create a connection between the object and its events. This connection allows the Class Module to capture events fired by the object and call the corresponding event procedure in response.

      The event procedure name must start with the object instance name, followed by an underscore, and then the event name. This naming convention ensures that Access can properly map the event to the event procedure.

      For example, if we have a TextBox object named "txtName" and we want to capture its LostFocus event, we would declare it in the parent Class Module like this:

      Private WithEvents txtName As Access.TextBox
      Private Sub txtName_LostFocus()
          ' Do something when the txtName TextBox loses focus
      End Sub

      In this example, the object instance name "txtName" is prefixed to the event name "LostFocus" to create the event procedure name "txtName_LostFocus".

    • To capture the user-defined event QtyUpdate() from the frmUserDefined form module, the parent form module instance should be declared with the WithEvents keyword. This allows the frmUDCapture form to listen to the event transmission from the frmUserDefined form and execute the corresponding event procedure.

    • When you add a TextBox (or any other control) to a form in Access, the Access system creates an instance of the TextBox object and adds it as a control to the form. The Access system also creates a property for the TextBox in the form's module, which is declared with the WithEvents keyword. The name of this property is the default name of the TextBox control with "As TextBox" appended to it. For example, if the default name of the TextBox control is "Text0", the property declaration in the form's module will be:

      Private WithEvents Text0 As TextBox

      You can change the name of the TextBox control to something more meaningful, and the name of the property declaration in the form's module will automatically update to reflect the new name.

    • Check the last two lines at the bottom of the Object Browser image below and see how the TextBox Object Instance name OrderQty declaration appears on our Form frmUserDefined. Check the declaration starting with the Keyword WithEvents OrderQty As TextBox.

      To inspect the Property of a TextBox in the Object Browser or any other control on the Form, you can follow these steps:

      1. Right-Click on VBA Editing Window and select Object Browser Option, or Click on the Object Browser Button from the Toolbar above to Open the Object Browser.
      2. In the Object Browser, select the Database Name in the <All Libraries> Control.
      3. The Object Browser will display the Properties and Methods of the selected control, including the declaration for the control's Object Instance.
      4. Select the Form Name Form_frmUserDefined from the left panel of the Browser Window.
      5. The Form's Property List appears in alphabetical order in the right-side Panel.
      6. Scroll down and find the OrderQty Property of the TextBox and select it.
      7. For example, the declaration for a TextBox with the name OrderQty on a form named frmUserDefined would look like this:

        WithEvents OrderQty As TextBox

        At the bottom panel of the Object Browser in the detail view of the selected Property.

      8. You may look for the user-defined Event QtyUpdate() declaration and how it appears in the right panel with a lightning icon and check the details view of the event declaration in the bottom panel.
    • Both user-defined events and built-in events of Access objects require an event procedure to be created on their parent VBA module. For built-in events, the event procedure name will be based on the name of the Access object and the event name. For example, if the Access object is a Textbox named "Text0" and the Event is "LostFocus", the event procedure name will be "Sub Text0_LostFocus()".

      For user-defined events, the event procedure name will be based on the name of the event and the parent VBA module name. For example, if the user-defined event is named "QtyUpdate" and the parent Form VBA module Instance is named "objFrm", the event procedure name will be "Sub objFrm_QtyUpdate()".

    • The LostFocus event is an inbuilt event of the TextBox control in Access. When you add a TextBox control to a form, Access creates an instance of the TextBox object and assigns it a default name such as Text0. This TextBox instance is then declared as a property of the form, with the WithEvents keyword, which allows it to capture and handle its inherent events.

      As I mentioned, the Text0 object's parent object is the class module of the form, which means that the event handling code in the LostFocus event (or any other inbuilt event) should be written in the form's class module. The naming convention for event procedures for controls is that they start with the name of the control instance, followed by an underscore, and then the name of the event. In the case of the TextBox control named Text0, the event procedure for the LostFocus event would be named Sub Text0_LostFocus().

      Several Textbox object instances can be created in the same Form with appropriate names, and their Event Subroutines are written in the same Form's Class Module with TextBox names as the prefix. And this is what we do normally on Form Module.

    • The QtyUpdate() event is a user-defined event and is not part of any built-in object like the LostFocus event of a TextBox. It is defined in the Class Module of frmUserDefined Form and the parent object of the event is the form itself.

      In the frmUDCapture form, we have declared an instance of the frmUserDefined form as a listener object using the WithEvents keyword. Since the instance is named "ofrm", the event procedure for the QtyUpdate() event will have the format "Sub ofrm_QtyUpdate()". This allows the frmUDCapture form to capture and respond to the QtyUpdate() event raised by the frmUserDefined form.

    The statement Set ofrm = Forms("frmUserDefined") statement within the Form_Load() Event Procedure of the frmUDCaptured Form Module, if the Form is in an open state, then assigns it to the ofrm Class Module object. If this form is not in the open state, then it will run into Error.  The On Error Resume Next statement will suppress the error message, but the Event capturing will not work as expected. 

    Ensure that you open the frmUserDefined Form first, then the second form frmUDCapture next.

  2. We enter some Numeric Quantity Value into the OrderQty Textbox on the Form frmUserDefined and press the Enter Key to complete the data entry. The RaiseEvent QtyUpdate(Me!OrderQty) is fired from within the OrderQty_AfterUpdate() Event Procedure.

  3. This Event is captured in the frmUDCapture Form's Event Procedure Subroutine Code given below: 

    Private Sub ofrm_QtyUpdate(sQty As Single)
    Dim Msg As String
      If Nz(sQty, 0) < 1 Or Nz(sQty, 0) > 5 Then
         Msg = "Valid Qty Range: 1 - 5 Only"
         Msg = "Order Qty: " & sQty & " Approved."
      End If
      Me.Label1.Caption = Msg
      MsgBox Msg, vbInformation, "QtyUpdate()"
    End Sub
  4. The Valid value Range accepted in the TextBox is 1 to 5 only. Normally we write this validation check procedure in the same Form Module Event Subroutine only. Here, we are running the validity check in the second Form Module, and an appropriate message is written on the Msg String Variable and displayed in the MsgBox. At the same time, it will update the same message text in the caption of Label Control on the second Form also, to view the message after closing the Message Box.

  5. Check the Event Subroutine line: Private Sub ofrm_QtyUpdate(sQty As Single)

    The frmUserDefined Form's Class Module Object Instance name ofrm is used as the Subroutine name prefix combined with the User-Defined Event name QtyUpdate(), both separated with an underscore character. 

  6. Similarly, the FormClose() Event is captured and runs the Private Sub ofrm_formClose(txt As String) Event Procedure and it displays a message to the User, whether to close the Form frmUserDefined (first form) now, or not. If the response is Yes then it closes the First Form.

    Private Sub ofrm_formClose(txt As String)
    Me.Label2.Caption = txt
    If MsgBox(txt, vbYesNo, "FormClose()") = vbYes Then
      DoCmd.Close acForm, ofrm.Name
      Me.Label2.Caption = "Close Action = Cancelled."
    End If
    End Sub 
  7. In the formClose() Event Procedure header line you can see that the ofrm object prefix is appearing. In this subroutine, the parameter text is shown in the message box and the user responds whether the frmUserDefined Form (the first Form) is to be closed now or not.  The Message text is displayed from the second Form's Event Procedure. If the response is Yes then it runs the Code to close the first Form.

    When we issue Docmd.OpenForm  "FormName" Command, a series of Events takes place in a certain order, Form Open, Form Load, Form Current in that sequence. Similarly, when we run the DoCmd.Close acForm, "FormName"the Form_Unload Event runs first before the Form_Close event, if both events are run in VBA.  

  8. So when we run the above Form_Close Event Procedure to close the first Form, the first Form prepares to close down. Since we have written the Form_Unload Event Procedure in the first Form, to Close the second Form, it runs first and closes the second Form, before closing the first Form.

The User-Defined Events Trajectory View.

Events Trajectory View.

From the above graphical image, we can easily see at a glance how the user-defined Event is declared with the Event Keyword, how the Events are Announced with the RaiseEvent Keyword, and how the Event Listener is declared with the Keyword WithEvents that Captures the announced Event, identify it with the Event Procedure, and executes the correct Event Procedure Code. 

If we learn the inner workings of the Events and Event Procedure we can write Event Procedures of one Form in another Form's Class module, or Event Procedures can be in stand-alone Class Modules, rather than writing all the Event Procedures on the same Form Module. We are really heading for that.  But before touching the tricks at that level, we need to know a few basics of Event handling on Forms. How we could do things differently on Form, other than the traditional method we follow now.

Once we know how these tricks work together, our target is to take all the VBA codes out of the Form Module and organize them in such a way that Coding becomes very easy.  We will use the Forms for Visual Designing only. 

Besides that, once we streamline and adopt the new Coding procedure, we can easily take the backbone of the new procedure to other Projects and coding will become easier, enabling faster completion of new Projects.

Download Demo Database


  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object and Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA-Base Class and Derived Object-2
  5. Base Class and Derived Object Variants
  6. MS-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality

Reusing Form Module VBA Code for New Projects.

Streamlining Form Module Event Procedures.

The Existing Form Module Coding Procedure.

Access Forms are designed with several controls like Textboxes, Command Buttons, and other controls, and write Event Procedures for several tasks in Form Class Module for almost all the controls on the Form.  We open this Form in design view numerous times, either to make design changes or to add or to refine the Code already written in the Form Module. After completion of the Project, we leave all the VBA codes with the Project and no part of it is useful for any other Project.  Besides that, the manual effort we put in to develop the Project is in no way become useful to reduce the work of any other Project at all. 

We plan to streamline this whole procedure by taking away the VBA Code into the stand-alone Class Modules leaving the design task of the Form alone on the Form. By doing this we can reduce the manual effort we put into developing the Class Module Code and it can be exported to new Projects and modify them to suit the purposes of the new Database.  For modifying the Event Procedure Code we can go straight into the related stand-alone Class Module and make changes rather than struggling with the Form Design View and with its Control properties.

The Streamlining of  Class Module Code.

This is a very complex topic and needs your close attention to all these series of examples presented on these pages, to understand the concept, and procedures, and try them out yourself to understand them fully before proceeding to the next level.

Those who are not familiar with Microsoft Access Class Module and building Class Objects can go through the earlier Blog Posts starting from MS Access Class Module and VBA series of Articles. The earlier Articles were meant for beginners to learn the fundamentals of Class Module Object building Tutorials. The earlier Article Links are given at the end of this Page if you would like to take a look at them.

This topic will be presented in a Series of Articles spread over several weeks. External Class Module VBA Code Samples, Event flow diagrams, and Demo-database Downloads with ready-to-run examples will be provided for readers in the coming weeks.

Control's Event Procedures on the Form. 

But, let us try to understand a few things we take for granted, like writing Event Procedures for controls on the Form.  When we want to do some action on the data of a Text Box we take the GotFocus or LostFocus or some other Event Property and use one of the three following choices:

1. Gives the name of a Macro in the Event Property to run the Macro when the Event (like LostFocus) takes place.

2. Call a Function from the Event Property.

3. Or write an Event Procedure for the Control in the Form Module, and the Event is generated by the Control (say a Textbox) on the Form.

If one of the first two options is used, then the Form doesn't need a Class Module at all. But, when the [Event Procedure] option is selected, the MS Access System automatically adds a Class Module to the Form.

It is interesting to know how the System invokes the Event action from the Form's controls and captures it in the Form's Class Module and executes the VBA Code written for the task.

All the Objects or Controls, like TextBox, ComboBox, ListBox, and others are designed with Class Module Objects.  All of these Objects have Properties, which give them shapes, and colors, and have their own built-in Events.  When the Control like Textbox instance on the Form Raises the Event (like LostFocus) this is captured on the Form Module and we write VBA Code for the task in the Form Module. The Control's name is always prefixed in the Event Procedure Subroutine declaration, like:

 Sub Quantity_LostFocus()
 End Sub

Since; Access Objects are designed with Class Module they have their own self-defined built-in Events. We don't know exactly how Microsoft Access does this for the controls on the Form.  But, we know that the On LostFocus Event Property is set with the text [Event Procedure] set, then the Object's Event Announcer is made to announce the Event (RaiseEvent) and the Form Module Listener captures it and opens an empty subroutine stub (Sub Quantity_LostFocus()), and we write VBA Code within the empty Procedure stub presented in the Form Module. 

Do you ever think of finding out how the Events are Defined and what to do to fire an Event to execute a  small block of VBA Code to do some task?

We will try a simple example to understand how the object Events are Defined,  Raised, Captured in Form Module, and executes the Code written for the task.  We will be discussing a lot about this single topic of Event firing and Event capturing in various ways in the coming weeks.  Our aim is to devise a new approach to VBA Coding in the stand-alone Class Module, rather than in the Form Module, so that we will be able to do the Coding quickly and effortlessly.  Moreover, we can export and use the VBA Code for other Projects to reduce the Database development time.

User-Defined Custom Events.

Key Words: Event, RaiseEvent, and WithEvents.

  1. Open your Database.

  2. Create a new Form.

  3. Insert a Textbox.

  4. Display the Property Sheet of the Textbox.

  5. Change the Name Property value to Msg.

  6. Select the On Change Event Property and select [Event Procedure] from the drop-down list.

  7. Click on the build (. . .) Button to open the Form Module. 

  8. Copy and Paste the following VBA Code into the Form Module:

    'Define user-defined Event Message
    Public Event Message(txt As String)
    Private Sub Msg_Change()
    'Announce/Transmit the Event
        RaiseEvent Message(Me!Msg.Text)
    End Sub

    The first statement in the VBA Code above is a User-defined Event. The Event must be defined by Public scope followed by the keyword Event followed by the event Name (Name should not contain the underscore character like txt_Message) and parameter list, if any, in parenthesis.

    Next, in the OnChange Event Procedure of the Msg TextBox, we generate the Message Event Procedure with the statement RaiseEvent Message(Me.Msg.Text). Since; we have run the Event on the OnChange Event Procedure, every time we type a Character in the Textbox on the Form the Event is fired.  We will capture this action in another Form and display the textbox contents there to see that our User-defined Event is working correctly. 

  9. Change the Text Box's child Label Caption value to Msg:.

  10. Save the Form with the name Form1 and close the Form.

  11. Create a new Form with the Name Form2 and open it in Design View.

  12. Change the size of the form to as small as Form1.

  13. Insert a Label control on the Form, and enter some text like Message or my text in the label's Caption.

  14. Change the Popup property value of the Form to Yes

  15. Select the Form Load Event Property and select [Event Procedure] and click on the build (. . .) Button to open the Form Module.

  16. Copy and Paste the following VBA Code into the Form2 Module overwriting the existing Code Lines:

    Option Compare Database
    Option Explicit
    'Define Listener object frm, with reference to Class Module of Form1.
    Private WithEvents frm As Form_Form1  
    Private Sub Form_Load()
    On Error Resume Next
        Set frm = Forms("Form1") 'assign open Form Form1 object
    End Sub
    'Execute frm_Message Event, with Listener frm object as prefix
    Private Sub frm_Message(str As String) 
        Me.Label0.Caption = str
    End Sub

    Check the line with the WithEvents Keyword. On this declaration line, we are creating a Form object with the name frm and setting a reference to Form1's Class Module, with the prefix Form_(Form_Form1). You cannot reference a Form like this in VBA or elsewhere if the Form doesn't have a Class Module.

    The WithEvents keyword is known as a Listener of Events that takes place on Form1.

    Defining a form object with the Keyword WithEvents alone (like a Dim statement) will not be enough, we have to initialize the frm object variable with the active Form1 object in memory.

    In the Form_Load () Event Procedure we are doing that with the statement Set frm = Forms("Form1"). If Form2 is opened first, before opening Form1, then it will end up with an Error. To avoid that we have added an error trap line to ignore that error and continue with the next line.

    The next subroutine is our actual user-defined event's action-packed Code.

    Every time we type a character in the text box on Form1; this will immediately appear on the Label Control on Form2.

    The user-defined Event Message() will be fired every time we type a character in the Text box on Form1 and will be captured in Form2 and displayed in the Label Control.

  17. Save and Close Form2. Close Form1, if it is kept open.  Let us test our user-defined Event Message().

  18. Open Form1 in Normal View.

  19. Open Form2 in Normal View and drag it away from Form1.

  20. Type Hello World or anything else you like.  The typed text should appear in the Label control on Form2, each character as you type them in the Text box.

Hope you understood the concept of how an Event is defined in Form1 and how it is invoked and captured in Form2 and executes the related Subroutine Code to do some task. 

Note: On Form2 we are keeping a watch on Form1 by setting up a reference to it in frm object with the Listener keyword WithEvents.  When the Message Event is fired (RaiseEvent) on Form1, Form2 based frm object captures it immediately and the related Subroutine with the frm_ prefix (Private Sub frm_Message()) is executed immediately. 

You may try this or some other similar procedure with two different Forms to understand the relationship of declaring Event, RaiseEvent, and WithEvents. 

Next week we will try how to RaiseEvent of TextBox's predefined events like LostFocus (not user-defined) at Run-time. Capture the Event in our own user-defined Textbox Objects and run the Event Procedure on the same Form Module, as we normally do, but with our own Textbox object on behalf of the Textbox on the Form.

Download the Demo Database.

    MS Access Class Module Object.

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Object-2
  5. Base Class and Derived Object Variants
  6. MS-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality Transformation


Exporting All Access Tables into Excel WorkBook


We already learned how to use MS Access live data in Excel earlier. Another VBA-based procedure was created to export the Table/Query data in the current version of the Excel Workbook.  The main advantage, when the exported data is saved in the current version of Excel Workbook, the default Theme of data formatting features is automatically applied, rather than the Excel 2003 formatting used by the acSpreadSheetTypeExcel3 to 9 range of Parameters, by default.

We could export filtered data through multiple query criteria and save the data sets in separate Worksheets in a single WorkBook.  A modified version of this method filters data on multiple criteria and exports the records into separate Workbooks too. 

Excel Tools are superb for Worksheet analysis, Charts, and Reports with automation features. But, Excel is not made as a Database Management System.  When the history of data becomes crucial for decision making, budget projections, business target settings and so on Excel users will look for a better database management system, like MS Access with flexibility.

But, exporting the complete set of Tables from Access to Excel Workbook may not be an everyday requirement.

Exporting All Access Tables Into Excel Workbook.

But, for some reason or the other, if it becomes necessary, you can use the VBA Program given below.

The VBA Program ExportAllTables2Excel()

Copy and Paste the Code given below into the Standard Module of your Project.

Public Sub ExportAllTables2Excel()
'Program : ExportAllTables2Excel
'Purpose : Export All Access Tables into Excel WorkBook
'Author  : a.p.r. pillai
'Rights  : All Rights Reserved by www.msaccesstips.com
'Remarks : Creates separate WorkSheets for each Table
'        : in a single WorkBook.
'        : Table Name is Worksheet Name
Dim db As Database
Dim xlsFileLoc As String
Dim xlsName As String
Dim xlsPath As String
Dim Tbl As TableDef
Dim tblName As String
Dim j As Integer
Dim wrkBook As Excel.Workbook

On Error GoTo Export2Excel_Err

xlsFileLoc = CurrentProject.Path & "\"
xlsName = "AllTables.xlsx"

xlsPath = xlsFileLoc & xlsName
If Len(Dir(xlsPath)) > 0 Then
    Kill xlsPath
End If

    Set wrkBook = Excel.Workbooks.Add
    wrkBook.SaveAs xlsPath
Set db = CurrentDb

j = 0
For Each Tbl In db.TableDefs
    tblName = Tbl.Name
    If Left(tblName, 4) = "MSys" Then 'System Tables
        GoTo nextstep
        j = j + 1
On Error Resume Next
        DoCmd.TransferSpreadsheet acExport, _
        acSpreadsheetTypeExcel12Xml, tblName, xlsPath, True
        If Err > 0 Then
          debug.print tblName
            j = j - 1
            Resume nextstep
        End If
End If

On Error GoTo Export2Excel_Err

   MsgBox j & " Table(s) Exported to File:" & vbCr & xlsPath, , "Export2Excel()"
Set wrkBook = Nothing
Set db = Nothing

Exit Sub

MsgBox Err & " : " & Err.Description, , "Export2Excel()"
Resume Export2Excel_Exit
End Sub

Caution: You may compare the worksheet's Maximum Rows available (press End-Key then Down-Arrow Key) in your version of Excel Worksheet to the Access Table with the maximum number of records. If any of the tables have more than the Excel worksheet Rows, then there are chances that the extra records may overflow into a second Worksheet or may get lost in the process.  The above code is not tested for this and use it at your own risk.

Before going through the Code you may attach the Microsoft Excel Object xx.0 Library File to your Access Project, before attempting to compile the above VBA Code.

  1. Open the VBA Editing Window (Alt+F11).

  2. Select References from Tools Menu

  3. Look for Microsoft Excel 16.0 Object Library file in the list.

  4. Put a check mark, on the left side of the file, to select it.

  5. Click OK to close the Object Library files list.

The VBA Code Review.

At the beginning of the Code, the required Variables are declared. 

The xlsFileLoc variable is initialized with the Database Path, and we will be creating a new Workbook, in the current version of Excel you have, in this location to save the Access Tables.

The Workbook name will be AllTables.xlsx, if you would like to give a different name then change it.

The xlsPath Variable is initialized with the Workbook Path and filename.

In the next three steps, we create a new Excel Workbook with the name AllTables.xlsx, save it in the specified location, and close the file. If the file already exists there, then a warning message is displayed. You may either choose to overwrite the existing file or Cancel the export procedure and retain the existing Excel Workbook contents. 

NB: But, the Excel file should not be kept open while exporting the Access Tables. If it is kept open then the export operation will fail. Check the following TransferSpreadsheet Command:

DoCmd.TransferSpreadsheet acExport, _
     acSpreadsheetTypeExcel12Xml, TblName, xlsPath, True

The parameter xlsPath value is where we give a specific Excel Workbook File reference to save the output data. If the Workbook File already exists, then the output will be written into a separate Worksheet in that file.  If you omit this parameter, then it creates a target Work Sheet File based on the second parameter acSpreadsheetTypeExcel12Xml selection, one out of several options available to select from.

Creating a Workbook in the current version of Excel and giving it as the Target Workbook for the output will save all the Tables in separate Worksheets, in the same Workbook, rather than creating every table output creating in a separate Workbook. Besides that, the output data will be formatted with the current Excel version default Office theme.

Note: The Table Export option normally creates an Excel 2003 file automatically, based on one of the export options acSpreadsheetTypeExcel3 - 9 selected and the output data formatting may not be as pleasing as the current version of Excel that you have.  This topic was discussed in detail in an earlier post with the Title: MS Access And Transfer Spreadsheet Command and you may go through it for a better understanding of this procedure, different versions of options available and their output formats, and other important points to note.

In the next step, the Current Database object is assigned to the Object Variable db. The Variable j is used to take a count of Tables, exported into the Excel Workbook.

We can get all the Table names from the TableDefs collection of our Database Object and can pass these names to the Docmd.TransferSpreadSheet Command to transfer the records.

But, besides the user-created Tables, there are some System Tables also (even though they are normally kept hidden) in this collection of TableDefs.  Fortunately, all System Table names start with the letters MSys and we can identify them easily and stop outputting their contents into Excel.

j = 0
For Each Tbl In db.TableDefs
    tblName = Tbl.Name
    If Left(tblName, 4) = "MSys" Then 'System Tables
        GoTo nextstep
        j = j + 1
On Error Resume Next
        DoCmd.TransferSpreadsheet acExport, _
        acSpreadsheetTypeExcel12Xml, tblName, xlsPath, True
        If Err > 0 Then
          Debug.Print tblName
            j = j - 1
            Resume nextstep
        End If
End If

In the above For Each ... Next statement reads each Table definition from the TableDefs collection in the TableDef object Tbl.

The next statement reads the name of the Table from the Name Property into the String Variable tblName.

The next statement checks whether the first four characters of the table name match the text MSys or not. If it does, then it is a System Table and the program skips the remaining part of the Code and goes for taking the next table to validate.

If it is a valid user-created Table then the counter Variable j is incremented by one.  The next Error capture line was added as a precaution to check if anything goes wrong with the TransferSpreadSheet Command.  

Next, the TransferSpreadSheet command is executed and the Records from the Table are transferred into the Excel WorkSheet, if it is a valid table, with the Field Names placed on the first row of the worksheet. 

If the above operation failed due to some unforeseen Error, we ignore that table and go to the next table in line, after the table counter variable is reset to the earlier successful operation count. The name of the Table in Error is dumped into the Debug Window and you may check and take corrective actions on it later. If something happened to a single table, we don't want to stop our operation and expect other tables will output normally.

When finished with the exporting operation, a message is displayed with the count of Tables exported successfully into Excel WorkBook.

You may download the Demo Database attached here and try it right away.

  1. MS Access And Transfer SpreadSheet Command.
  2. Access And Windows API ShowWindow
  3. Database Backup/Restore From Desktop
  4. Get Disk Free Space - Windows API
  5. Access And Windows API ShowWindow

Database Backup Restore From Desktop


We had an extensive discussion on the following database backup topics earlier:

1. Database Daily Backup.

2. Repairing Compacting Databases from a dedicated database with VBA Programs.

Besides the above, we already have an option in Access to Repair and Compact the current Database, while closing it.

The first topic 'Database Daily Backup' procedure runs from within the database when it is open for the first time in a day. Subsequent opening/closing events will not repeat the backup process on the same day. 

The second procedure is run from within a dedicated database to Compact and Repair several databases given in a list, one by one.

The new method that we are going to try out is the VB Script, saved in textile, and run from desktop Shortcuts, for backup and Restore operations, manually. It is not an automated procedure, as far as invoking the program is concerned.  You can run the code manually by double-clicking on the shortcut, whenever you think it is time to take a backup of your database.  

Restore the corrupted database from its earlier backup(s), using another desktop shortcut, when you find the current database lost its consistency. 

These programs are in VB Script, a light version of Microsoft Visual Basic, mostly used on web pages, and you will not have any difficulty in understanding the Code if you have some familiarity with the VBA language.

  1. Create a Notepad text file on the Desktop.

  2. Rename the desktop shortcut name to CreateBackup.vbs. The shortcut name extension vbs indicates that it is a Visual Basic Script file.

  3. Right-click on the shortcut and select Edit from the displayed menu.

  4. When the text file opens in Notepad, Copy and Paste the code given below into the File.

  5. Select the Save option from the File Menu to save the Code, and select Exit to close the File.

The CreateBackup VB Script:

Call CreateBackup()

Sub CreateBackup()
'Desk-top Shortcut Name: CreateBackup.vbs
'Language              : VBScript - Creates File Backup
'Remark                : Run from Desktop Shortcut
'                      : Edit Backup Path
Dim s, t, p, a
Dim objFSO

Set objFSO = CreateObject("Scripting.FileSystemObject")

' Backup is taken in the File Folder itself
' with Day and Month added (-dd-mm) to the File Name.
' Example: D:\AccTest\Testdb-16-07.accdb

p = "D:\AccTest\NorthWind.accdb" 'Edit

s = InputBox("File PathName:","CreateBackup()" , p)

If objFSO.FileExists(s) Then
  'File Name changes appending with -dd-mm (-day-month) values.
    a = Left(Now(), 5)
    t = Left(s, InStrRev(s, ".") - 1) & "-" & a & Mid(s, InStrRev(s, "."))

'Create the File backup
    a = objFSO.CopyFile(s, t, True)

    MsgBox "Backup Successful!" & vbCr & vbCr & "Source: " & s & vbCr & "Backup: " & t,vbInformation,"CreateBackup()"
    MsgBox "Source PathName: " & s & vbCr & "Not Found, Program Aborted!",vbCritical,"CreateBackup()"
End If
Set objFSO = Nothing
End Sub

Database CreateBackup() Code Line-By-Line.

The Backup procedure is very simple, let us go through it.  The first statement Call CreateBackup() runs the Subroutine given below. This approach is necessary because when you right-click on the desktop shortcut and select Open or double-click on it, the code written within a Subroutine or Function will be ignored by the script running procedure. An explicit call of the subroutine is required to start executing the VBScript enveloped within a subroutine structure.

Note: You may write the Script in a Notepad Text File with vbs extension, without enveloping the code within the Subroutine/Function structure. It will execute the VBScript straight through what is written in the Text File with the vbs file extension when you double-click on the desktop shortcut.  

The Function classification is acceptable only if you omit the return value part after the function name like:

Function CreateBackup() as Integer - not acceptable

Function CreateBackup() - acceptable

 The required variables are declared at the beginning of the subroutine. The Variable Type declarations, like db_Pathname As String, are not allowed in VBScript. It can be compared with the VBA Variant Data type.  The variable Type is changed based on the first value type assigned to it. If you are using VBScript within a VBA Subroutine/Function you can declare variables as we do normally in VBA.

Set objFSO = CreateObject("Scripting.FileSystemObject")

The above statement creates a File System Object and assigns it to the objFSO Variable. 

The variable p is initialized with a pathname that will appear as the default database pathname in the Inputbox() function prompt. If you are using this Desktop Shortcut to backup the same database every time, then set this variable to the required file pathname otherwise change the database pathname appearing in the InputBox() function TextBox to use the required database pathname for backup.

If objFSO.FileExists(s) Then

The FileExists() method of the objFSO script object checks whether the source file pathname given as the parameter exists or not. If it is found, then the backup file name is prepared by adding the current Day  and Month values, in -dd-mm format (example: D:\AccTest\NorthWind-16-07.accdb) by the following statements:

    a = Left(Now(), 5)
    t = Left(s, InStrRev(s, ".") - 1) & "-" & a & Mid(s, InStrRev(s, "."))

The following statement calls the CopyFile() method of the objFSO object and creates a copy of the Source File with the new name in the same folder:

a = objFSO.CopyFile(s, t, True)

The first parameter, s is the source file pathname, and the second parameter t is the target file pathname. The last parameter True indicates that if the target file exists, then overwrite it without warning. After the copy operation, a message is displayed announcing the successful completion of the backup operation. 

If the source pathname is not found then the program will be aborted with a critical message.

Note: If you take more than one backup of the same file on a particular day in the same folder, then the earlier backup file will be overwritten without warning because the third parameter setting is True.

Database RestoreFile() VBScript Code. 

Follow the same procedure for creating the VB Script file with the Notepad explained above, and create the RestoreFile.vbs Desktop Shortcut with the following VBScript code copying and pasting them into it.
Call RestoreFile()

Sub RestoreFile()
'Language              : VBScript
'Desk-top Shortcut Name: RestoreFile.vbs 
'Remarks               : Restore File from Backup
Dim db_Current
Dim db_Save
Dim db_Backup
Dim f_bkSource,f_Current, f_Save
Dim objFSO

'The following three demo lines can be replaced
'in the InputBox statement, to Input
'File Pathnames directly.
db_Current = "D:\AccTest\NorthWind.Accdb"      'The file needs replacement
db_Save = "D:\AccTest\NorthWind-Save.Accdb"    'save the [dbReplace] file with a new name
db_Backup = "D:\AccTest\NorthWind-17-07.Accdb" 'Restore from this Backup File

f_bkSource = InputBox("Backup File PathName:","Restore()",db_Backup)
f_Current = InputBox("Restore File PathName:","Restore()",db_Current)
f_save = InputBox("Save Current before replace:","Restore()",db_save)

'Create File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")

If objFSO.FileExists(f_Current) then
' Save the existing/corrupt file with a new name
' Third Param:TRUE, overwrites the existing file without warning.
    a = objFSO.CopyFile(f_Current, f_save, True)
    a = objFSO.DeleteFile(f_current) 'delete original file after copying with a new name
End If

'Check the Backup file Exists or Not
If objFSO.FileExists(f_bksource) then 
	a = objFSO.CopyFile(f_bkSource, f_Current, True) 'Restore the original file from backup.

  If objFSO.FileExists(f_Current) then 'check restore operation was successful or not.
   	MsgBox "File: " & f_Current & vbcrlf & " Successfully Restored.",vbInformation,"RestoreFile()"
Else MsgBox "Oops! Something went wrong.",vbCritical,"RestoreFile()"
End If Else MsgBox "Backup File: " & f_bksource & vbcrlf & " Not Found!",vbCritical,"RestoreFile()"
End If Set objFSO = Nothing End Sub

RestoreFile() Code Line-by-Line.

The first three variables: db_Current, db_Save, and db_Backup will be initialized with the current database pathname (the file that needs replacement from backup), and before replacing it is saved with the modified pathname xxxxxx-save.accdb, and the backup file pathname respectively.

Next three variables: f_bkSource, f_Current, and f_Save will accept the pathnames given by the user for the above values.  objFSO will be initialized with the File System Object. 

db_Current = "D:\AccTest\NorthWind.Accdb"      ' db_Current needs replacement
db_Save = "D:\AccTest\NorthWind-Save.Accdb"    'save the [db_current] file with a new name
db_Backup = "D:\AccTest\NorthWind-17-07.Accdb" 'Restore file from this Backup database 

The first three variables are initialized with the sample database pathnames as above which will appear in the InputBox() function Text Box as default Pathnames.  If these are the files involved in the RestoreFile() program's input values, then you can press Enter Key to accept them as it is, or modify them as per your requirements.

Set objFSO = CreateObject("Scripting.FileSystemObject") 

After the InputBox functions are run the above statement creates the File System Object and assigns it to the objFSO object.

If objFSO.FileExists(f_Current) then
' Save the existing/corrupt file with a new name
' Third Param:TRUE, overwrites the existing file without warning.
	a = objFSO.CopyFile(f_Current, f_save, True)
        a = objFSO.DeleteFile(f_current) 'delete the original after copying with a new name
 End If

 The above code segment checks whether the current file name given for replacement exists or not. If found, then it is copied with the modified name for safe keep.  If the file doesn't exist, then bypass the above code segment. There is no status value returned in variable a, indicating the copy operation's success or failure. This Syntax simply allows us to use parenthesis around the function parameter values. The following statement is equally valid.

objFSO.CopyFile f_Current, f_save, True

Next, the corrupt file is deleted using the DeleteFile() method of the objFSO Object.

The following code segment runs a validation check with the FileExists() method on the Backup database file, if found, then makes a copy of it with the original database name, overwriting it, if already exists, but we have deleted it in the earlier operation:   

'Check the Backup file Exists or Not
If objFSO.FileExists(f_bksource) then 
	a = objFSO.CopyFile(f_bkSource, f_Current, True) 'Restore the original file from backup.

  If objFSO.FileExists(f_Current) then 'check restore operation was successful or not.
   	MsgBox "File: " & f_Current & vbcrlf & " Successfully Restored.",vbInformation,"RestoreFile()"
   	MsgBox "Oops! Something went wrong.",vbCritical,"RestoreFile()"
End If Else MsgBox "Backup File: " & f_bksource & vbcrlf & " Not Found!",vbCritical,"RestoreFile()"
End If

After the restore operation, it runs a check for the original file pathname, to ensure that the restore operation was successful and displays a message to that effect. If something went wrong with the copy operation, then a critical message is displayed.  

If the backup filename itself is not found, then a critical message is displayed and the objFSO object is released from memory before the program ends. 

Tip: You can back up and restore any type of file besides databases.

  1. Get Disk Free Space - Windows API
  2. Access And Windows API ShowWindow
  3. ListView Control Drag Drop Sort Events.

Get Disk Free Space Windows API


Last week we learned the usage of ShowWindow and PostMessage Windows APIs.  We could easily change the window state of Forms and Reports using their built-in windows-handle the hwnd Property values. We have not used the FindWindow API to retrieve the Form's windows identification number.

Here, we will learn another simple and useful Windows API Function, the GetDiskFreeSpace Function. The Windows API declaration is as follows:

Private Declare PtrSafe Function GetDiskFreeSpace Lib "kernel32" Alias "GetDiskFreeSpaceA" _
    (ByVal lpRootPathName As String, _
    lpSectorsPerCluster As Long, _
    lpBytesPerSector As Long, _
    lpNumberOfFreeClusters As Long, _
    lpTotalNumberOfClusters As Long) As Long

The GetDiskFreeSpace API needs five parameters:

  1. The first parameter is the RootPathName of the Disk Drive like C:\ or C:.

    The next four Parameter Variables must be declared as Long Integer Type and pass their references as parameters while calling the GetDiskFreeSpace API.

    If the API call was successful and was able to retrieve the disk information then:

  2. The second parameter Variable lpSectorPerCluster will contain the count of Sectors in a single Cluster on the Disk. 

    The disk capacity is measured in bytes, the number of bytes in a Sector, the number of Sectors in a Cluster, free space, and total space on the disk in Clusters.

    The GetDiskFreeSpace API returns free space on the disk in Clusters only.

  3. The lpBytesPerSector variable will have the quantity of data saved in a Disk-Sector, normally about 512 Bytes.

  4. The 4th parameter lpNumberOfFreeClusters will have the free space left on the disk, returned in Clusters.

  5. The last parameter lpTotalNumberOfClusters has the total capacity (the partition capacity) information in Clusters. 

The DiskFreeSpace Wrapper Function.

We have created a wrapper function DiskFreeSpace to call the Windows API GetDiskFreeSpace Function from within, to retrieve and calculate the free space on the disk. The disk or computer memory capacity-related quantitative terms that we normally use are Gigabytes, Megabytes, or KiloBytes for communicating. So, we need to convert the cluster values into these measurements.

  1. Create a new Standard Module in your Database.

  2. Copy and Paste the above Windows API Code into the global declaration area of the Module.

    The DiskFreeSpace Function Code.

  3. Next, Copy and Paste the following User function code below the Windows API code in the same Module:

    Private SectorPerCluster As Long
    Private BytesPerSector As Long
    Private FreeClusters As Long
    Private TotalClusters As Long
    Private gbf As Double
    Private mbf As Double
    Private kbf As Double
    Private tb As Double
    Private gb As Double
    Private mb As Double
    Private kb As Double
    Private fmt As String
    Private msg As String, msg2 As String
    Public Function DiskFreeSpace(ByVal strPath As String) As String
    Dim Rtn As Long
    Dim ClusterBytes As Double
    On Error GoTo DiskFreeSpace_Err
    Rtn = GetDiskFreeSpace(strPath, SectorPerCluster, BytesPerSector, FreeClusters, TotalClusters)
    fmt = "#,##0"
    msg = ""
    msg2 = ""
    If Rtn Then
    'Bytes in a Cluster = Sectors * BytesPerSector
        ClusterBytes = SectorPerCluster * BytesPerSector 'Bytes per cluster
    'msg2 = "        Disk Drive: " & UCase(strPath) & vbCrLf & _
            "Sector Per Cluster: " & SectorPerCluster & vbCrLf & _
            "  Bytes Per Sector: " & BytesPerSector & vbCrLf & _
            "     Free Clusters: " & FreeClusters & vbCrLf & _
            "    Total Clusters: " & TotalClusters
    'Debug.Print msg2
        gbf = ClusterBytes / (1024# ^ 3) 'GB Factor per Cluster
        mbf = ClusterBytes / (1024# ^ 2) 'MB Factor     "
        kbf = ClusterBytes / (1024#)     'KB Factor     "
    'free Space
        tb = Int(TotalClusters * gbf) ' Total Space in Gigabytes
        gb = Int(FreeClusters * gbf)  ' Free Space  in     "
        mb = Int(FreeClusters * mbf)  '       "     in Megabytes
        kb = Int(FreeClusters * kbf)  '       "     in Kilobytes
    msg = " Disk Drive: " & UCase(strPath) & vbCrLf & _ "Total Space ( GB ): " & Format(tb, fmt) & vbCrLf & _ " Free Space ( GB ): " & Format(gb, fmt) & vbCrLf & _ " Free Space ( MB ): " & Format(mb, fmt) & vbCrLf & _ " Free Space ( KB ): " & Format(kb, fmt) Else MsgBox "Disk Drive PathName: " & UCase(strPath) & vbCrLf & _ "NOT FOUND!", vbOKOnly + vbCritical, "DiskFreeSpace()" DiskFreeSpace = "" Exit Function End If DiskFreeSpace = msg DiskFreeSpace_Exit: Exit Function DiskFreeSpace_Err: MsgBox Err & " : " & Err.Description, , "DiskFreeSpace()" DiskFreeSpace = "" Resume DiskFreeSpace_Exit End Function

Our new function needs only one parameter the disk's Root Pathname.  If you look at the Windows API GetDiskFreeSpace Parameter declarations the first Parameter is declared with ByVal qualification, other parameters are not qualified as such because they are declared as ByRef parameters by default.

The first parameter can be passed directly, like "C:\" or a variable initialized with the disk Root Pathname.  Other parameter variables are declared as Long Integer Types in the Global declaration area. These variable References are passed to the Windows API and the retrieved information is saved directly into those Variables.

All Variables except Rtn and ClusterBytes I have declared at the global declaration area so that our own Function DiskFreeSpace Code looks better and less crowded.

The user-defined function DiskFreeSpace needs only one parameter: the Root Pathname of the disk, like "C:\".  The GetDiskFreeSpace Windows API is called with all five parameters, from our function DiskFreeSpace().

The Disk Space Value Conversion Calculations.

If the API call was successful, then the variable Rtn will have the Value 1 otherwise 0.

So testing the variable Rtn is necessary to proceed with converting the disk information into Gigabytes, Megabytes, or Kilobytes.

When the API is run successfully the returned values are in Bytes per Sector, Sectors per Cluster, Disk Free Space in Clusters, and Disk's total capacity in Clusters.  

The disk capacity is logically grouped into Sectors of 512 Bytes (characters) and a group of 8 Sectors or more known as a Cluster. This is the amount of data the computer can read/write at one attempt. This may change depending on the type of disk drives like Hard Disk, SSD, Zip-Drive, or MicroSD Drive and their formatting type: NTFS, FAT32, etc. 

The Bytes per Sector found 512 on most of the disk types but the Sectors per cluster may change, like 8, 16, or 32.  You may test this function on your own machine with different Disk Types to find out.

We are familiar with the terms like Gigabytes (GB), Megabytes (MB), or Kilobytes (KB) the terms which we normally use to communicate the disk's capacity.  So we will convert the Clusters into bytes and then convert them into GB, MB, or KB.

[Total Bytes per Cluster] = [Bytes Per Sector] * [Sectors Per Cluster] = 512 * 8 = 4096 bytes.

GBF = [Total Bytes per Cluster] / (1024#^3) : Gigabytes Factor.

MBF = [Total Bytes per Cluster] / (1024#^2) : Megabytes Factor.

KBF = [Total Bytes per Cluster] / (1024#) : KiloBytes Factor.

With these values, we can easily convert the Free Cluster Values into any of the above three Values, like:

GB = [Free Space Clusters] * GBF will give the Disk Free Space value in Gigabytes.

Our Function DiskFreeSpace() returns a String Value containing information formatted in such a way that the returned value can be displayed in a MsgBox, printed in Debug Window, or displayed in a Label Control on Form that is wide enough to display 5 lines of text.

The sample output Image in Debug Window and in MsgBox side-by-side is given below for information:

The DiskFreeSpace() function can be run from the Debug Window directly, from within some other function, or from an Event Procedure on Form.

The Demo Database is attached for Download and ready to Run.

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Object-2
  5. Base Class and Derived Object Variants
  6. MS-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality Transformation



Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Android App Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code