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 initially Published 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.

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

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.

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.