Streamline Zoom-in Control Data Editing

 Streamlining Form Module Code in Standalone Class Module.

Editing Large Text Box Contents in Zoom-in Control.

This topic was originally published in August 2007 under the title ‘Edit Data in Zoom-in Control.’ In that example, a custom shortcut menu was created and linked to the form to activate the zoom-in control, allowing users to edit the textbox contents containing multiple lines of data, similar to the Notes field in the Employees table.

The earlier version of the customized Shortcut Menu for the Employees Form is shown below for reference.

Right-click to open Large Image in New Tab/Window.

The built-in Shortcut Menu underwent customization by designing a new button image, highlighted in the Form image by a red oval shape. This button, resembling a CRT screen, is programmed with the necessary code to execute upon clicking. However, the process of designing a new button image within the existing shortcut menu proved to be very difficult.

 Opening the Zoom Control is a two-step process: first, right-click on the TextBox to display the Shortcut Menu, then select the Zoom option to open the Zoom-in Form with the active TextBox contents. 

Now, we have a more efficient method: simply right-click on a TextBox to open the Zoom-in Form prefilled with its data. After editing the contents, click the [Save] command button on the Zoom Form to update the original TextBox with the revised text.

The New Version of Zoom-in Form Image.

The New Employees Form with the Zoom-in Control with the Notes Field Data is given below:

Right-click to open Large Image in New Tab/Window.

In addition to copying the data from the Employees Notes TextBox into the larger TextBox on the Zoom Form, the formatting attributes from the original TextBox on the Employees Form are also applied, ensuring consistency in appearance.

The Main Public Functions.

There are two Public Functions that run this procedure. 

The ZoomOpen() Function.

Public Function ZoomOpen()
'------------------------------------------------------
'Function : Edit Data in Zoom-in Control.
'Author   : a.p.r.pillai
'Date     : 29/07/2007, 26/03/2024
'Rights(c): www.msaccesstips.com
'------------------------------------------------------
Dim varVal, ctl As Control, intFontWeight As Integer
Dim strFont As String, intFontSize As Integer
Dim boolFontstyle As Boolean
Dim lngfontColor As Long, boolFontUnderline As Boolean
Dim bkgColor As Long

On Error GoTo ZoomOpen_Err

Set ctl = Screen.ActiveControl
With ctl
   strFont = .FontName
   intFontSize = .FontSize
   intFontWeight = .FontWeight
   boolFontstyle = .FontItalic
   boolFontUnderline = .FontUnderline
   lngfontColor = .ForeColor
   'bkgColor = .BackColor
End With

   varVal = Screen.ActiveControl.Value
   DoCmd.OpenForm "Zoom", acNormal

With Screen.ActiveForm.Controls("TxtZoom")
   .Value = varVal
   .FontName = strFont
   .FontSize = intFontSize
   .FontWeight = intFontWeight
   .FontItalic = boolFontstyle
   .FontUnderline = boolFontUnderline
   .ForeColor = lngfontColor
   '.BackColor = bkgColor
End With

ZoomOpen_Exit:
Exit Function

ZoomOpen_Err:
Resume ZoomOpen_Exit
End Function
 

When you right-click a TextBox, the ZoomOpen() function is executed. It copies the TextBox contents into a Variant variable, opens the Zoom-in Form, and transfers the data into the larger TextBox. The original formatting attributes from the source text box are also applied to ensure the text appears the same in the Zoom-in TextBox.

After editing the text in the Zoom-in control, click the [Save] Command Button to save the changes back into the original text box of the Employees Form, and to close the Zoom-in Form.

The Zoom-in Form can be dragged and moved to a convenient position in the Application Window.  The Zoom Form will open in Popup and Modal Mode, and you must close it to access other controls or Forms. 

The SaveZoomData() Function.

The [Save] Command Button Click Runs the SaveZoomData() Function.  The VBA Code is given below.

Public Function SaveZoomData()
'------------------------------------------------------
'Function : Save Edited Data in the Control
'Author   : a.p.r.pillai
'Date     : 29/07/2007, 26/03/2024
'Rights(c): www.msaccesstips.com
'------------------------------------------------------
Dim vartxtZoom, strControl As String

On Error GoTo SaveZoomData_Err

 vartxtZoom = Forms("Zoom").Controls("txtZoom").Value

 DoCmd.Close acForm, "zoom"
 
 If Screen.ActiveControl.Locked = True Then
   strControl = Screen.ActiveControl.Name
   MsgBox strControl & " is Read-Only, Changes discarded!"
   Exit Function
 Else
    If IsNull(vartxtZoom) = False And Len(vartxtZoom) > 0 Then
        Screen.ActiveControl.Value = vartxtZoom
    End If
 End If
 
SaveZoomData_Exit:
Exit Function

SaveZoomData_Err:
Resume SaveZoomData_Exit
End Function

The SaveZoomData() Function saves the edited data into its Source TextBox. If the Textbox is locked, the edited data cannot be saved.

In both the above Functions, we used the Screen Object to address the active Form or active Text Box control without using their object names directly, like Screen.ActiveForm, Screen.ActiveControl that has the Focus.

As I stated earlier, all you need to do is right-click on the TextBox to open the Zoom-in control and present the TextBox contents in the Zoom Window for editing. 

For those who prefer using a shortcut menu to open the Zoom Control, I created a small macro-based menu that can be assigned to the Shortcut Menu Bar property of the Form or to the same property of any individual control on the Form. When applied at the Form level, the shortcut menu will appear whenever you right-click anywhere on the Form, not just on a specific control such as a TextBox. The Macro Shortcut Menu Options.

There are two Shortcut Menu options we planned to display in the Macro. 

1. Open Zoom
2. Cancel

The macro Commands for the Shortcut Menu Bar are listed in the McrZoom Macro Image shown below:

Right-click to open Large Image in New Tab/Window.

The macro shown above provides two options. The first option executes the ZoomOpen() function, which opens the Zoom Form and loads the text from the active TextBox into the Zoom Control for editing. The second option simply cancels the right-click event.

2. Create the Menu Macro.

We need to create a Menu Macro and insert the Menu Options Macro: McrZoom into the Menu Macro. The Menu Macro Image is given below:

Right-click to open Large Image in New Tab/Window.

The Menu Macro name is McrControlShortcut.

The 'Shortcut Menu Bar' Property of Form and Controls.

The Menu Macro can be inserted into the Shortcut Menu Bar Property of the Form or in the same Property of the Controls on the Form.

When added to the Form Property, the Menu appears wherever you right-click on the Form. When added to a specific Control's Shortcut Menu Bar Property, the Menu appears for that Control only.

Most controls on the form have the 'Shortcut Menu Bar' property, allowing you to insert a menu macro name to display the shortcut menu. When inserted into the TextBox's property, you can even right-click on the child label of the TextBox to bring up the shortcut menu.

Normally, on the OnClick Event Property of a Command Button or a TextBox, we can insert a Macro or a Public Function Name that executes it directly when it receives a Mouse Button Click.

Despite the 'Shortcut Menu Bar' property expecting a menu bar, it directly executes the macro or function name inserted into this property when the control receives a Right-click Event. Additionally, it briefly displays a small empty menu bar.

Examples:

1. Text0.Shortcut Menu Bar = "Macro2"
2. Text0.Shortcut Menu Bar = "=ZoomOpen()"

We will experiment with both methods. Created the Macro Menu as above for easy implementation as well.

In this round of streamlining VBA code, we do not employ any object-level wrapper classes. Instead, we use only the interface class Cls_ObjInit, where we can experiment with both approaches for utilizing the Zoom control.

The Zoom Form with txtZoom TextBox.

The Zoom Form Image is given below for Reference.

Right-click to open Large Image in New Tab/Window.

The Zoom Form has two Command Buttons. One to save the edited data into its Source Textbox, and the other to cancel the operation. Both Command Button Clicks Subroutines are written in the Form Module only.

Option Compare Database
Option Explicit

Private Sub cmdSave_Click()
  Call SaveZoomData
End Sub

Private Sub cmdCancel_Click()
  DoCmd.Close acForm, "Zoom"
End Sub

The Interface Class Module Cls_ObjInit

Option Compare Database
Option Explicit

Private frm As Access.Form

Public Property Get m_Frm() As Form
  Set m_Frm = frm
End Property

Public Property Set m_Frm(ByRef vForm As Form)
  Set frm = vForm
  
  Call Class_Init
End Property

Private Sub Class_Init()
Dim opt As String

opt = "McrControlShortcut"

'opt = "=ZoomOpen()" 'Call Function directly on Right-Click

frm.ShortcutMenu = True 'True by default
Call SetupControls(opt)
       
End Sub

Private Sub SetupControls(ByVal strOpt As String)
Dim ctl As Control

For Each ctl In frm.Controls
  Select Case ctl.ControlType
Case acTextBox
    'ctl.ShortcutMenuBar = StrOpt 'For all TextBoxes
            
      Select Case ctl.Name 'Only selected TextBoxes
          Case "Title", "Address", "Notes"
          
            ctl.ShortcutMenuBar = strOpt
            
      End Select

  End Select
Next
End Sub

Private Sub Class_Terminate()
Dim opt As String

opt = ""
Call SetupControls(opt)

End Sub

The Cls_ObjInit interface class declares a Form object in the global area, followed by the property procedures for handling the Form.

After receiving the active Form object through the frm property, the Class_Init() subroutine is executed. Both Class_Init() and Class_Terminate() call the common SetUpControls() subroutine. This routine is responsible for either assigning the macro-based shortcut menu to the Shortcut Menu Bar property of the Employees Form or directly invoking the ZoomOpen() function.

The Class_Terminate() subroutine resets the Shortcut Menu Bar property, ensuring that changes made by the standalone class module Cls_ObjInit are applied dynamically.

With a small adjustment in the Class_Init() subroutine, you can configure the Shortcut Menu Bar property either for all TextBoxes (or any other control) on the Form, or restrict it to specific TextBox controls only.

The Trial Runs.

In the Class_Init() subroutine, we plan to call the SetUpControls() subroutine to dynamically configure the Shortcut Menu Bar property of the TextBox controls. To better understand its behavior, we will experiment with two alternate settings, applying them one at a time. This trial approach allows us to observe how each configuration affects the functionality of the Zoom Control feature.

opt = "McrControlShortcut"

'opt = "=ZoomOpen()" 'Call Function directly on Right-Click

frm.ShortcutMenu = True 'True by default
 

By default, the second line of code is kept disabled. Remember that the setting frm.ShortcutMenu = True is the default for the form. If the shortcut menu does not appear as expected, check this property in the form and correct it.

Before invoking the SetUpControls() subroutine, the parameter variable Opt is initialized with the macro menu name "McrControlShortcut". The subroutine then assigns this macro name to the TextBox control’s Shortcut Menu Bar property. When this option is active, right-clicking the TextBox displays the custom shortcut menu. Choosing “Open Zoom” from the menu runs the public function: ZoomOpen(), while the “Cancel” option simply cancels the right-click action.

When the second option in the Class_Init() subroutine is enabled (instead of the first), the right-click event bypasses the macro and directly executes the ZoomOpen() function.

Similarly, the SetUpControl() subroutine has two options for implementation. The default method assigns the Shortcut Menu Bar property only to selected TextBox controls where data is likely to exceed the visible boundary. In this example, the fields include Title, Address, and Notes. Additional fields can be included as needed, depending on the requirements.

If we plan to implement it on all the TextBoxes on the Form, then the 'ctl.ShortcutMenuBar = StrOpt can be enabled, and the following lines of VBA Code can be removed.

      Select Case ctl.Name 'Only selected TextBoxes
          Case "Title", "Address", "Notes"
          
            ctl.ShortcutMenuBar = strOpt
            
      End Select

The Employees Form Module VBA Code.

Option Compare Database
Option Explicit

Dim Cl As New Cls_ObjInit

Private Sub Form_Load()
Set Cl.m_Frm = Me
End Sub

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

The Interface Class Cls_ObjInit is declared and instantiated in the global declaration area of the Employees Form. In the Form_Load() Event Procedure, the Current Form Object is passed to the Form Property Procedure, and from there the Class_Init() Subroutine is called to Set the Shortcut Menu Bar Property of the TextBoxes in the Employees Form.

This trick also works on the Tabular and Datasheet Forms. Two Demo Forms, Tabular and Datasheet Employee Forms, are also provided in the Demo Database.

Download the Demo Database.

DataEditing Zoom-in Control.zip

1. Reusing Form Module VBA Code for New Projects.
2. Streamlining Form Module Code - Part Two.
3. Streamlining Form Module Code - Part Three
4. Streamlining Form Module Code - Part Four
5. Streamlining Form Module Code - Part Five
6. Streamlining Form Module Code - Part Six
7. Streamlining Form Module Code - Part Seven
8. Streamlining Form Module Code - Part Eight
9. Streamlining Form Module Code - Part Nine
10. Streamlining Form Module Code - Part Ten
11. Streamlining Form Module Code - Part Eleven
12. Streamlining Report Module Code in Class Module
13. Streamlining Module Code Report Line Hiding-13.
14. Streamlining Form Module Code Part-14.
15. Streamlining Custom Made Form Wizard-15.
16. Streamlining VBA Custom Made Report Wizard-16.
17. Streamlining VBA External Files List in Hyperlinks-17
18. Streamlining Events VBA 3D Text Wizard-18
19. Streamlining Events VBA RGB Color Wizard-19
20. Streamlining Events Numbers to Words-20
21. Access Users Group(Europe) Presentation-21
22. The Event Firing Mechanism of MS Access-22
23. One TextBox and Three Wrapper Class Instances-23
24. Streamlining Code Synchronized Floating Popup Form-24
25. Streamlining Code Compacting/Repair Database-25
26. Streamlining Code Remainder Popup Form-26
27. Streamlining Code Editing Data in Zoom-in Control-27
28. Streamlining Code Filter By Character and Sort-28
29. Table Query Records in Collection Object-29
30. Class for All Data Entry Editing Forms-30
31. Wrapper Class Module Creation Wizard-31
32. wrapper-class-template-wizard-v2

Read More

Streamlining Code Reminder Popup Form

 Streamlining Form Module Code in Standalone Class Module.

Reminder Popup Form.

Introduction.

Understanding the significance of reminders is paramount. When it comes to important occasions such as a family member's or a friend's birthday, adequate preparation time is crucial. Being notified at least two days beforehand ensures that we don't overlook these events amidst our busy schedules and other pressing commitments.

When considering business-related activities, let's examine the Inventory System of a Pharmacy as an example. It's imperative to print out a list of medicines that fall below the minimum stock level or reach reorder levels on the 25th of each month to facilitate stock replenishment by placing orders promptly.

Seasonal demand requires higher stock levels of certain medicines during winter. By reviewing usage patterns from previous years, we can identify high-demand medicines and place advance orders with suppliers to ensure adequate availability before the season begins.

Tasks that require advanced alerts or scheduled notifications can be programmed to trigger a pop-up form or report with the relevant information, ensuring timely attention and prompt action. The Birthday Experiment.

Here, we will demonstrate this feature using the Employees table, which has been extended with two additional fields: BirthDate and BFlag. The BirthDate field contains the assumed date of birth for each employee. For calculation purposes, the day and month of birth are combined with the current year (e.g., 14-March-1961 becomes 14-March-2024) to determine this year’s birthday. The logical field BFlag is updated to True once the birthday greeting has been printed from the alert pop-up form.

The Alerts are programmed to run in three stages:

1. A pop-up form appears 48 hours before the individual's birthday and recurs again on the eve of the celebration.

2. The pop-up form will display on the birthday when the database is accessed. A straightforward birthday greeting card is generated directly from the pop-up form in PDF format. Upon completing the card printing process, the Employees Table field BFlag is marked as True, ensuring that the pop-up won't reappear during subsequent database openings.

3. If the Birthday Greetings is not printed and the BFlag Field is not set to True, then the alert pop-up will appear for the next two days after the due date, indicating overdue case(s). 

Initially, we establish an input Query named Birthday_RemData, which includes a new column dedicated to computing each employee's birthday for the Current Year, sourced from the BirthDate field within the Employees1 table. The Birthday_RemData Query is the foundational dataset for categorizing data into the above three categories for pop-up forms.

The Employees1 Table Image.

The Employees1 Table image is shown below, with the required Fields for ready reference.

Reminder: Data Filtering Queries.

The SQL of the Input Query, with the current year Date of Birth, is calculated for each Employee, from the actual Date of Birth in the Table given below:

Query Name: BirthDay_RemData (Birthday Reminder Input Data).

SELECT Employees1.EmployeeID, 
[FirstName] & " " & [LastName] AS Name, 
Employees1.BirthDate, 
Employees1.BFlag, 
DateDiff("yyyy",[birthdate],Date()) AS Age, 
DateValue(Format([BirthDate],"dd/mm") & "-" & CStr(Year(Date()))) AS DueDate
FROM Employees1;

1. Query: RemindQ1_OnDate - to filter data for the Popup on the actual BirthDay:

SELECT BirthDay_RemData.*
FROM BirthDay_RemData
WHERE (((BirthDay_RemData.DueDate)=Date()) AND ((BirthDay_RemData.BFlag)=False));

2. Query: RemindQ2_Advance - to filter data for the Popup that appears two days before the BirthDay:

SELECT BirthDay_RemData.*
FROM BirthDay_RemData
WHERE (((BirthDay_RemData.BFlag)=False) AND (([DueDate]-1)=Date()))
OR (((BirthDay_RemData.BFlag)=False) AND (([DueDate]-2)=Date()));

3. Query: RemindQ3_OverDue - to filter data for the Popup that appears two days after the BirthDay, if the birthday card is not printed on the Birthday.:

SELECT BirthDay_RemData.*
FROM BirthDay_RemData
WHERE (((BirthDay_RemData.BFlag)=False) AND (([DueDate]+1)=Date())) 
OR (((BirthDay_RemData.BFlag)=False) AND (([DueDate]+2)=Date()));

The RemindQ1_OnDate Query is the source Data of the Reminder1 Popup Form. The Reminder2 and Reminder3 Forms are linked to the RemindQ2_Advance and RemindQ3_Overdue Queries, respectively. All three are Tabular Forms.

Reminder: POPUP Forms.

1. The Remnder1 Popup Form Image is given below for reference.

The employee records with birthdays matching today’s date will be displayed on the Form, showing their actual date of birth and the birthday for the current year in separate columns. You can print the greeting card by clicking the “Print PDF Greeting” Command Button. The default path of the greeting’s target location can be temporarily altered directly in the text box. You can make the change permanent in the Default Value Property of the text box in the design view.

The Advance and Overdue Reminder Popups may show Day/Month in both columns differently. The Printing option is not available in the Form Footer Section on the Advanced and Overdue Popup Forms.

The Greetings PDF file will be saved in the path indicated in the TextBox located in the Footer Section of the Form. The path displayed in the TextBox is set in the Default Value Property of the TextBox, which you can modify to direct it to your preferred path.

The Sample Greetings Card.

The sample Greetings Card image is given below for reference.

The other two Popup Form Images are given below for information.

2. Alert about upcoming Birthdays:

3. Alerts about the missed Birthday Celebrations:

How do the Popup Form(s) open automatically if the Employee's Date of Birth meets any of the criteria specified above?

It is easy, immediately after the database is open, it checks the presence of records in the above three Queries (i.e., RemindQ1_OnDate, RemindQ2_Advance, and RemindQ3_OverDue) and opens the Alert Form linked to the Queries. For this purpose, a small Function is created in the Standard Module.

The VBA Code of Checkpopup() Function.

Public Function Checkpopup()
'========================================================
'Project: Reminder Popup Form
'Author : a.p.r. pillai
'Date   : March, 2024
'Rights : All Rights(c) Reserved by www.msaccesstips.com
'Remarks: Opens up preset Reminder Popup Form(s)
'       : on pre-scheuled Date/Month
'Example: Employees' Date-of-Birth Reminder.
'========================================================
Dim i, j, k As Integer
i = DCount("*", "RemindQ1_OnDate")
If i > 0 Then
    DoCmd.OpenForm "Reminder1", acNormal
End If

j = DCount("*", "RemindQ2_Advance")
If j > 0 Then
    DoCmd.OpenForm "Reminder2", acNormal
End If

k = DCount("*", "RemindQ3_OverDue")
If k > 0 Then
    DoCmd.OpenForm "Reminder3", acNormal
End If

End Function

Running the Popup Forms.

Now all we need to do is call this Function immediately after opening the Database. The first choice can be to create a Macro with the name AutoExec (the Auto Execute Macro) and call the Function using the RunCode Command. The name of the Macro must be AutoExec so that Access executes this macro automatically when the Database is open. Another option is to drag this Macro and drop it on the Desktop as a Shortcut. Double-click on it to open the Database and run the Macro.

Another option is to run the Function from the Form_Load() Event Subroutine of the first Form that opens when the database is open.

If there is no urgency, call the Function when the Employees Form is open, and the PopUp Form(s) will appear, if any of the three data filtering Queries, or all of them have data, after a brief delay of 3 Seconds.

This is good for experimenting with this trick and learning how it can be set up for Advanced Alert, or on-the-day or Overdue Alerts can be implemented in your other Projects.

The Employee Form-based Popup.

There are three employee forms for each Reminder category for setting up the date and experiment. All of them can be opened from a small Main Form. The Image of the Main Form is given below.

The Employees Form Image of the First Option:

When you open this Form, after a delay of about 3 seconds, the Remnder1_OnDate Popup Form will open with the Employees' Records that have their BirthDate today. If there are no records, the [Open Reminder] Command Button is disabled. If the pop-up doesn't appear, then change the Birthdate of one or two employees to match the Day and Month (don't change the year) to match the current Date.

If you close the Reminder Form by mistake, use this Command Button to open the Reminder Form again, without closing and opening the Employees Form. 

There are two other Forms for experimenting with Reminders of forthcoming or overdue Reminder setups.

Streamlining VBA Event Subroutine Codes.

Now coming to the Streamlined VBA Coding Part, there are three Employee Forms with two Command Buttons on each Form. The Employee information is for display purposes only. If you plan to edit the employee's Birthdate through this Form, you are welcome, but the validation check is not performed, and full responsibility is yours. All three Forms are linked to the same Employees1 Table. 

Since all three forms have two command buttons each, we need only one Command Button Wrapper Class to handle the Events of Command Button Clicks. But, we will use three different Class_Init() Interface Classes (Intermediary Class) to create separate Instances for all three Employee Forms, so that their Identity References will remain separately in memory. The 3 Seconds TimerInterval running Subroutine is also run from within this Class Module and then opens the Popup Form.

The Command Button Wrapper Class: Rm1_cmdButton - Employees Forms.

 
Option Compare Database
Option Explicit

Private WithEvents cmd As Access.CommandButton
Private mfrm As Form
Dim t As Integer
'========================================================
'Project: Reminder Popup Form
'Author : a.p.r. pillai
'Date   : March, 2024
'Rights : All Rights(c) Reserved by www.msaccesstips.com
'Remarks: Opens up preset Reminder Popup Form(s)
'       : on pre-scheuled Date/Month
'Example: Employees' Date-of-Birth Reminder.
'========================================================

Public Property Get m_Frm() As Access.Form
  Set m_Frm = mfrm
End Property

Public Property Set m_Frm(ByRef vfrm As Access.Form)
  Set mfrm = vfrm
End Property

Public Property Get m_cmd() As Access.CommandButton
  Set m_cmd = cmd
End Property

Public Property Set m_cmd(ByRef vcmd As Access.CommandButton)
  Set cmd = vcmd
End Property

Private Sub cmd_Click()
  Select Case cmd.Name
    Case "cmdReminder1"
        DoCmd.OpenForm "Reminder1", acNormal
    Case "cmdReminder2"
        DoCmd.OpenForm "Reminder2", acNormal
    Case "cmdReminder3"
        DoCmd.OpenForm "Reminder3", acNormal
        
    Case "cmdExit1"
        DoCmd.Close acForm, "Employees1"
    Case "cmdExit2"
        DoCmd.Close acForm, "Employees2"
    Case "cmdExit3"
        DoCmd.Close acForm, "Employees3"
  End Select
End Sub

We need only one Click-Event Subroutine in the Class Module to handle the Command Button Clicks from all three Forms. The Code is not messy and remains clean and directly accessible, rather than struggling with the Form Design View to reach them in different Form Modules. The Event Procedure Code is self-explanatory.

Three Different Interface Class Modules for Different Employee Forms. We don't create Instances of the Interface Class, hence we need to create three different Interface Classes. Moreover, it runs the TimerInterval Subroutine for three different Forms.

Interface Class of Employees1 Form: Rm1_Init Class VBA Code.

 Option Compare Database
Option Explicit

Private cmd As Rm1_CmdButton
Private WithEvents frm As Form

Private Coll As New Collection
Dim t As Integer

'========================================================
'Project: Reminder Popup Form
'Author : a.p.r. pillai
'Date   : March, 2024
'Rights : All Rights(c) Reserved by www.msaccesstips.com
'Remarks: Opens up preset Reminder Popup Form(s)
'       : on pre-scheuled Date/Month
'Example: Employees' Date-of-Birth Reminder.
'========================================================

Public Property Get m_Frm() As Form
  Set m_Frm = frm
  
End Property

Public Property Set m_Frm(ByRef vfrm As Form)
  Set frm = vfrm
  
  Call Class_Init
  
End Property

Private Sub Class_Init()
Dim ctl As Control
Const EP = "[Event Procedure]"

frm.OnTimer = EP

For Each ctl In frm.Controls
    Select Case TypeName(ctl)
        Case "CommandButton"
          Select Case ctl.Name
            Case "cmdReminder1", "cmdExit1"
            Set cmd = New Rm1_CmdButton
            Set cmd.m_Frm = frm
            Set cmd.m_cmd = ctl
                cmd.m_cmd.OnClick = EP
                Coll.Add cmd
            Set cmd = Nothing
          End Select
    End Select
Next

t = 0
frm.TimerInterval = 1000
End Sub

Private Sub frm_Timer()
Dim icount As Long
On Error GoTo frmTimer_Err

t = t + 1
If t = 3 Then
    frm.TimerInterval = 0
icount = DCount("*", "RemindQ1_OnDate")
    If icount > 0 Then
        t = 0
        frm.cmdReminder1.Enabled = True
        Call PopupOpen("Reminder1")
    Else
        frm.cmdReminder1.Enabled = False
        frm.Requery
    End If
End If

frmTimer_Exit:
Exit Sub

frmTimer_Err:
MsgBox Err.Description, , "frmTimer()"
Resume frmTimer_Exit
End Sub

Private Sub Class_Terminate()
Do While Coll.Count > 0
    Coll.Remove 1
Loop

End Sub
 

This class module is declared within the Employee1 Form Module, with the Employee1 Form Reference being passed to the frm object declaration. Subsequently, the Class_Init subroutine is invoked. Within this subroutine's outset, the Timer Event of the Employee1 Form is enabled, followed by the creation of Command Button Instances and the procedure for enabling events. The Timer Interval is set with a 1000 Milliseconds (1 second) delay, running for three seconds. Following this 3-second delay, the record count of the ReminderQ1_OnDate Query is obtained. If the record count exceeds zero, the Reminder1_OnDate Reminder Popup Form is opened, displaying the relevant records for viewing.

Interface Class of Employees2 Form: Rm2_Init Class.

Option Compare Database
Option Explicit

Private cmd As Rm1_CmdButton
Private WithEvents frm As Form

Private Coll As New Collection
Dim t As Integer
'========================================================
'Project: Reminder Popup Form
'Author : a.p.r. pillai
'Date   : March, 2024
'Rights : All Rights(c) Reserved by www.msaccesstips.com
'Remarks: Opens up preset Reminder Popup Form(s)
'       : on pre-scheuled Date/Month
'Example: Employees' Date-of-Birth Reminder.
'========================================================

Public Property Get m_Frm() As Form
  Set m_Frm = frm
  
End Property

Public Property Set m_Frm(ByRef vfrm As Form)
  Set frm = vfrm
  
  Call Class_Init
  
End Property

Private Sub Class_Init()
Dim ctl As Control
Const EP = "[Event Procedure]"

frm.OnTimer = EP

For Each ctl In frm.Controls
    Select Case TypeName(ctl)
        Case "CommandButton"
          Select Case ctl.Name
            Case "cmdReminder2", "cmdExit2"
            Set cmd = New Rm1_CmdButton
            Set cmd.m_Frm = frm
            Set cmd.m_cmd = ctl
                cmd.m_cmd.OnClick = EP
                Coll.Add cmd
            Set cmd = Nothing
          End Select
    End Select
Next

t = 0
frm.TimerInterval = 1000
End Sub

Private Sub frm_Timer()
Dim icount As Long
On Error GoTo frmTimer_Err

t = t + 1
If t = 3 Then
    frm.TimerInterval = 0
icount = DCount("*", "RemindQ2_Advance")
    If icount > 0 Then
        t = 0
        frm.cmdReminder2.Enabled = True
        frm.Requery
  
        Call PopupOpen("Reminder2")
    Else
        frm.cmdReminder2.Enabled = False
        frm.Requery
    End If
End If

frmTimer_Exit:
Exit Sub

frmTimer_Err:
MsgBox Err.Description, , "frmTimer()"
Resume frmTimer_Exit
End Sub

Private Sub Class_Terminate()
Do While Coll.Count > 0
    Coll.Remove 1
Loop

End Sub

The only difference in this Module is the name of the Query and Command Button Names. We use the same Rm1_CmdButton Wrapper Class here also.

Rm3_Init Interface Class also has the same VBA Code with different Query and Command Button Names.

Option Compare Database
Option Explicit

Private cmd As Rm1_CmdButton
Private WithEvents frm As Form

Private Coll As New Collection
Dim t As Integer
'========================================================
'Project: Reminder Popup Form
'Author : a.p.r. pillai
'Date   : March, 2024
'Rights : All Rights(c) Reserved by www.msaccesstips.com
'Remarks: Opens up preset Reminder Popup Form(s)
'       : on pre-scheuled Date/Month
'Example: Employees' Date-of-Birth Reminder.
'========================================================

Public Property Get m_Frm() As Form
  Set m_Frm = frm
  
End Property

Public Property Set m_Frm(ByRef vfrm As Form)
  Set frm = vfrm
  
  Call Class_Init
  
End Property

Private Sub Class_Init()
Dim ctl As Control
Const EP = "[Event Procedure]"

'frm.OnTimer = EP

For Each ctl In frm.Controls
    Select Case TypeName(ctl)
        Case "CommandButton"
          Select Case ctl.Name
            Case "cmdReminder3", "cmdExit3"
                Set cmd = New Rm1_CmdButton
                Set cmd.m_Frm = frm
                    cmd.m_Frm.OnTimer = EP
                Set cmd.m_cmd = ctl
                    cmd.m_cmd.OnClick = EP
                    Coll.Add cmd
            Set cmd = Nothing
        End Select
    End Select
Next

t = 0
frm.TimerInterval = 1000

End Sub

Private Sub frm_Timer()
Dim icount As Long
'On Error GoTo frmTimer_Err

t = t + 1
If t = 3 Then
    frm.TimerInterval = 0
  icount = DCount("*", "RemindQ3_OverDue")
    If icount > 0 Then
        t = 0
        frm.cmdReminder3.Enabled = True
        frm.Requery
  
        Call PopupOpen("Reminder3")
    Else
        frm.cmdReminder3.Enabled = False
        frm.Requery
    End If
End If

frmTimer_Exit:
Exit Sub

frmTimer_Err:
MsgBox Err.Description, , "frmTimer()"
Resume frmTimer_Exit
End Sub

Private Sub Class_Terminate()
Do While Coll.Count > 0
    Coll.Remove 1
Loop

End Sub

The Popup Forms' Wrapper Class and Interface Class Module VBA Code.

Only one Wrapper Class Module and one Interface Class Module are required for all three Popup Forms to handle the Event Procedures of the Command Buttons on them. All three Forms have only Command Buttons-click Events handled in the Wrapper Class Sub_CmdButton and Interface Class Module.

The Sub_CmdButton Wrapper Class Module VBA Code.

Option Compare Database
Option Explicit

Private WithEvents cmd As Access.CommandButton
Private frm As Form
'========================================================
'Project: Reminder Popup Form
'Author : a.p.r. pillai
'Date   : March, 2024
'Rights : All Rights(c) Reserved by www.msaccesstips.com
'Remarks: Opens up preset Reminder Popup Form(s)
'       : on pre-scheuled Date/Month
'Example: Employees' Date-of-Birth Reminder.
'========================================================

Public Property Get m_Frm() As Access.Form
  Set m_Frm = frm
End Property

Public Property Set m_Frm(ByRef vfrm As Access.Form)
  Set frm = vfrm
End Property

Public Property Get m_cmd() As Access.CommandButton
  Set m_cmd = cmd
End Property

Public Property Set m_cmd(ByRef vcmd As Access.CommandButton)
  Set cmd = vcmd
End Property

Private Sub cmd_Click()
  Select Case cmd.Name
   Case "cmdPrint1"
        Call GreetingsPrint
        
    Case "cmdCancel1"
        DoCmd.Close acForm, "Reminder1"
    Case "cmdCancel2"
        DoCmd.Close acForm, "Reminder2"
    Case "cmdCancel3"
        DoCmd.Close acForm, "Reminder3"
  End Select
End Sub

Private Sub GreetingsPrint()
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qryDef As DAO.QueryDef
Dim EID As Integer
Dim OutFile As String

'On Error GoTo GreetingsPrint_Err

Set db = CurrentDb
Set rst = db.OpenRecordset("RemindQ1_OnDate", dbOpenDynaset)

rst.MoveLast
rst.MoveFirst

Do While Not rst.EOF And Not rst.BOF
    EID = rst![EmployeeID]
    strSQL = "SELECT RemindQ1_OnDate.* FROM RemindQ1_OnDate "
    strSQL = strSQL & "WHERE (((RemindQ1_OnDate.EmployeeID)= "
    strSQL = strSQL & EID & "));"

Set qryDef = db.QueryDefs("BirthDayQ1OnDate_PDF")
    qryDef.SQL = strSQL
    db.QueryDefs.Refresh

DoCmd.OpenReport "Greetings1_PDF", acViewPreview
If MsgBox("Birthday Greetings Print Initiated, Proceed?", vbYesNo, "Greetings Print()") = vbNo Then
DoCmd.Close acReport, "Greetings1_PDF"
   Exit Sub
End If
DoCmd.Close acReport, "Greetings1_PDF"

OutFile = frm!Path & DLookup("Name", "BirthdayQ1OnDate_PDF", "EmployeeID = " & EID) & ".pdf"
DoCmd.OutputTo acOutputReport, "Greetings1_PDF", "PDFFormat(*.pdf)", OutFile, False, "", 0, acExportQualityPrint

rst.MoveNext
Loop

  rst.Close
  Set rst = Nothing
  Set db = Nothing


DoCmd.SetWarnings False
'Delete earlier saved records
DoCmd.OpenQuery "BirthDayReminder1_Del", acViewNormal

'Add latest records
DoCmd.OpenQuery "BDay_SavedQ1", acViewNormal

'Flag the Employee Record as Greetings Printed
'Reset the floags on January 1st, Next Year
DoCmd.OpenQuery "BirthDayQ_UpdateFlag1", acViewNormal
DoCmd.SetWarnings True

MsgBox "Greetings PDFs are saved in Path: " & frm!Path

GreetingsPrint_Exit:
Exit Sub

GreetingsPrint_Err:
MsgBox Err & ": " & Err.Description, , "GreetingsPrint_Click()"
Resume GreetingsPrint_Exit
End Sub

In the cmd_Click() Event Subroutine, the cmdPrint1 Command Button Click on the Reminder1 Form Calls the GreetingsPrint() Subroutine and prints the Birthday Greetings in PDF format and updates the Employees1 Table, marking the BFlag logical Field as True to prevent it from appearing in the Employee record again in the Popup Form. The Greetings will be printed for each Employee Record separately.

After printing the Popup Form, the records will be saved into a separate temporary Table: Birthday_Reminder1.

The Sub_Init Interface Class Module VBA Code.

Option Compare Database
Option Explicit

Private cmd As Sub_CmdButton
Private frm As Form
Private Coll As New Collection

Public Property Get m_Frm() As Form
  Set m_Frm = frm
  
End Property

Public Property Set m_Frm(ByRef vfrm As Form)
  Set frm = vfrm
  
  Call Class_Init
  
End Property

Private Sub Class_Init()
Dim ctl As Control
Const EP = "[Event Procedure]"

'Set frm2 = frm.BReminderSub1.Form
For Each ctl In frm.Controls
    Select Case TypeName(ctl)
        Case "CommandButton"
          Select Case ctl.Name
            Case "cmdPrint1", "cmdCancel1", _
            "cmdCancel2", "cmdCancel3"
            Set cmd = New Sub_CmdButton
            Set cmd.m_Frm = frm
            Set cmd.m_cmd = ctl
                cmd.m_cmd.OnClick = EP
                Coll.Add cmd
            Set cmd = Nothing
        End Select
    End Select
Next
End Sub

Private Sub Class_Terminate()
Do While Coll.Count > 0
    Coll.Remove 1
Loop

End Sub

All three Popup Forms: Reminder1, Reminder2, and Reminder3 have Command Buttons with unique Names to Close the Forms, and all of them are included in the Class_Init() Subroutine. Because of their unique names, we could handle their Event Procedures in a Single Wrapper Class Module.

Even though the Code requirement is simple, the Standalone Class Module VBA Coding gives you much flexibility for maintaining Code in a centralized location. It needs only one Click Event Subroutine for writing Code for several Command Buttons.

Demo Database Download

RemnderPopup2.zip

1. Reusing Form Module VBA Code for New Projects.
2. Streamlining Form Module Code - Part Two.
3. Streamlining Form Module Code - Part Three
4. Streamlining Form Module Code - Part Four
5. Streamlining Form Module Code - Part Five
6. Streamlining Form Module Code - Part Six
7. Streamlining Form Module Code - Part Seven
8. Streamlining Form Module Code - Part Eight
9. Streamlining Form Module Code - Part Nine
10. Streamlining Form Module Code - Part Ten
11. Streamlining Form Module Code - Part Eleven
12. Streamlining Report Module Code in Class Module
13. Streamlining Module Code Report Line Hiding-13.
14. Streamlining Form Module Code Part-14.
15. Streamlining Custom Made Form Wizard-15.
16. Streamlining VBA Custom Made Report Wizard-16.
17. Streamlining VBA External Files List in Hyperlinks-17
18. Streamlining Events VBA 3D Text Wizard-18
19. Streamlining Events VBA RGB Color Wizard-19
20. Streamlining Events Numbers to Words-20
21. Access Users Group(Europe) Presentation-21
22. The Event Firing Mechanism of MS Access-22
23. One TextBox and Three Wrapper Class Instances-23
24. Streamlining Code Synchronized Floating Popup Form-24
25. Streamlining Code Compacting/Repair Database-25
26. Streamlining Code Remainder Popup Form-26
27. Streamlining Code Editing Data in Zoom-in Control-27
28. Streamlining Code Filter By Character and Sort-28
29. Table Query Records in Collection Object-29
30. Class for All Data Entry Editing Forms-30
31. Wrapper Class Module Creation Wizard-31
32. wrapper-class-template-wizard-v2

Read More