Streamline Zoom-in Control Data Editing ~ LEARN MS-ACCESS TIPS AND TRICKS

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.

Open Zoom
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:

Text0.Shortcut Menu Bar = "Macro2"
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.