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 initially released in August 2007 and titled "Edit Data in Zoom-in Control." In the preceding example, a custom shortcut menu was devised and linked to the form to activate the zoom-in control, enabling the editing of textbox contents with multiple lines of data, akin to the notes field in the Employees Table.

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

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.

 Besides that, 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 TextBox Contents. 

But, now we have a better method, only right-click on a TextBox to open the Zoom-in Form with the Data from that TextBox. Edit the TextBox contents then click the [Save] Command Button on the Zoom Form to replace the data back into the TextBox.

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:

Besides copying the Data from the Employees Notes TextBox into the large TextBox in Zoom Form it is formatted with the same TextBox formatting attributes on the Employees Form. 

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

Right-clicking on a TextBox runs this ZoomOpen() Function, copies the TextBox contents into a Variant type Variable, opens the Zoom-in Form, and transfers the copied data into the Large TextBox on the Zoom Form. The original text formatting attribute values are applied in the Zoom-in TextBox Text.

After editing the Text click on the [Save] Command Button to save the changes back into the original Textbox of the Employees Form, and 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 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 TextBox 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. 

If you insist on a Shortcut Menu to click and open the Zoom Control, I designed a small Macro-based Shortcut Menu. It can be inserted into the 'Shortcut Menu Bar'  Property of the Form or the same Property of any other Control on the Form. If inserted into the Form's Property the Shortcut Menu will appear when you Right-click anywhere on the Form, not necessarily on a Control like TextBox.

1. 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:

There are two Options in the Macro Image given above. The first option Runs the ZoomOpen() Function that opens the Zoom Form with the active TextBox control's Text Data in 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:

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.

This time in the Streamlining of VBA Code we don't have any Object-level Wrapper Classes except the Interface Class Cls_Objinit, where we can experiment with both methods of running the Zoom Control usage. 

The Zoom Form with txtZoom TextBox.

The Zoom Form Image is given below for Reference.

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 has the declaration of a Form object in the Global Area followed by the Property Procedures for the Form.

After receiving the active Form Object in the frm Property call the Class_Init() Subroutine. The Class_Init() and Class_Terminate() Subroutines use a common Subroutine SetUpControls() only to set the 'Shortcut Menu Bar' Property in the Employees Form with the macro-based Shortcut Menu or to run the ZoomOpen() Function directly. The Class_Terminate() Subroutine resets the Shortcut Menu Bar Property. So the change happens dynamically with the use of the Standalone Class Module Cls_ObjInit. 

With a small change in the Class_Init() Subroutine of the Cls_ObjInit Class, we can either set the Shortcut Menu Bar Property of all the TextBoxes (or any other Control) or only selected TextBox controls on the Form. 

The Trial Runs.

In the Class_Init() Subroutine we plan to Call the SetUpControl() Subroutine to set the 'Shortcut Menu Bar' Property of the TextBox Control with two different settings alternately to try it out and learn how both works. Check the following lines of Code:

opt = "McrControlShortcut"

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

frm.ShortcutMenu = True 'True by default

The second line of code is kept disabled.  The line frm.ShortcutMenu = True is the default setting of the Form. If the Shortcut Menu doesn't appear as expected check this Property of the Form and correct the setting.

Before invoking the SetupControl() subroutine, the parameter variable "Opt" is initialized with the macro menu name "McrControlShortcut". The subroutine will then assign the menu macro name to the Textbox Control's 'Shortcut Menu Bar' property. Upon executing the code with this option, the shortcut menu will appear when the TextBox receives the right-click event. Selecting the first option 'Open Zoom' from the shortcut menu executes the Public function "ZoomOpen()". The "Cancel" option in the menu cancels the right-click event.

When the second option in the Class_Init() Subroutine, is now kept disabled, when used the Right-Click Event directly executes the Function ZoomOpen().

Similarly, the SetupControl() Subroutine has two options for your preferred use. The default method is to assign the 'Shortcut Menu Bar' Property to only a few selected TextBoxes, where we expect an overflow of Data beyond the boundary of the TextBox(s). The selected fields are Title, Address, and Notes Fields. Can be added with more Fields based on 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 works on the Tabular and Datasheet Forms too. Two Demo Forms, Tabular and Datasheet Employees 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 Elevan
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

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 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 demands necessitate an increased stock of certain medicines during winter. By analyzing the winter season requirements of the past few years, we can identify medicines with high demand and proactively place orders for enhanced stock from suppliers well in advance before the onset of winter.

Any tasks requiring advanced alerts or scheduled notifications can be programmed to trigger a popup Form or Report displaying pertinent information. This ensures prompt action and timely attention to important matters.

The Birthday Experiment.

Here, we will experiment with this feature with the Employees Table, added with two new Fields: BirthDate and BFlag. The birthDate field is filled with some assumed date of birth dates. The Date of Birth Day and Month are translated to the current year Date (the birth date 14-March-1961 calculated to 14-March-2024) to find the birthday this year. The BFlag Logical Field is updated to True when the Birthday Greetings is printed from the alert Popup 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 the pop-up won't reappear when the database is open again.

3. If the Birthday Greetings is not printed and the BFlag Field is not set to True, the alert pop-up will appear for the next two days after the due date, indicating as 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 given 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 Popup on the actual BirthD\ay:

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

2. Query: RemindQ2_Advance - to filter data for 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 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 in the text box directly. 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 Advance 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 does 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 to 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 Advance 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 have their BirthDate today. If there are no records the [Open Reminder] Command Button is disabled. If the popup 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 Remainder Form again, without closing and opening the Employees Form. 

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

Streamlining VBA Event Subroutine Codes.

Now coming to the Streamlined VBA Coding Part, There are three Employees Forms with two Command Buttons on each Form. The Employee information is for display purposes only. If you plan to Edit the employee 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 the 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 Employees 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 to handle 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 the Employee record again in the Popup Form. The Greetings will be Printed for each Employee Record separately.

After Printing the Popup Form 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 Elevan
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

Read More