Edit Data in Zoom-In Control

Introduction.

While designing a Form, Report, or Query, certain property fields—such as Record Source, Filter, or Order By—can be more comfortably edited using the Zoom feature. To use it:

• Right-click on the property box (e.g., Record Source),

• A shortcut menu will appear with a Zoom… option,

• Click Zoom… to open a larger, resizable window where you can edit the property value with ease.

✦ This feature is especially useful for writing long SQL statements, expressions, or formulas that don’t fit well in the default single-line input box.

However, the Zoom option is not available while entering or editing data on Forms, especially for large Text or Memo fields (now called Long Text in newer versions of Access). These fields often hold more data than what is visible in the limited display area of the control.

In such cases, users must either:

• Rely on navigating and scrolling within the control itself, or

• Use alternative solutions, like a pop-up form or a double-click event to open a larger edit window.

✦ Adding a double-click event on such controls to launch a Zoom-like custom form can greatly enhance user experience.

Click to Enlarge

The Zoom Tool Button is available under the Properties category of the built-in Shortcut Menus for Forms, Reports, Queries, and Indexes. You can copy this Zoom button and paste it onto your custom Shortcut Menu—or even onto the built-in Form View control shortcut menu, just like we did with the Animated Floating Calendar feature. This allows users to access the Zoom window conveniently while working on Forms, especially for fields with large amounts of text.

Designing the Zoom Control

But we are going to take a different approach—we’ll design our own Zoom-In control. It will be both interesting and insightful to explore how this custom feature works behind the scenes.

We can create this quite easily—all we need is a small form, two simple VBA routines, and a shortcut menu button, similar to the one we added earlier to the Form View Control sub-menu for our Animated Floating Calendar.

Open a new Form and design a Text Box and two Command Buttons as shown below:

Click to Enlarge

Set the properties of the Form, Text Box, and Command Buttons as given below:

1. Display the Form's Property Sheet (Press Alt+ENTER) and change the following Form Properties:
   • Caption = Zoom
   • Default View = Single Form
   • Allow Edits = Yes
   • Allow Deletions = No
   • Allow Additions = No
   • Data Entry = No
   • Scroll Bar = Neither
   • Record Selectors = No
   • Navigation Buttons = No
   • Dividing Lines = No
   • Auto Resize = Yes
   • Auto Center = Yes
   • Popup = Yes
   • Modal = Yes
   • Border Style = Dialog
   • Control Box = Yes
   • Min Max Buttons = None
   • Close Button = Yes
   • What's this Button = No
   • Width = 5.4583"
   • Allow Design Changes = Design View Only
   • Movable = Yes
2. Click on the Detail Section of the Form and change the Height and other properties as shown below:
   • Height = 2.4167"
   
   

   Text Box and Command Buttons Properties

   Text Box Properties:

   • Name = txtZoom
   • Left = 0.2083"
   • Top = 0.1667"
   • Width = 4.1875"
   • Height = 2.0417"

   Command Button1 :

   • Name = cmdOK
   • Caption = OK

   Command Button2 :

   • Name = cmdCancel
   • Caption = Cancel
3. Display the Form's VB Module (Click on the Code Toolbar Button above (when the Form is still in Design View) or select Code from View Menu), copy and paste the code given below into the Form Module, and save the Form with the name Zoom.

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

   Create a Custom Toolbar Button

4. Create a custom toolbar button using the method described earlier in the post Custom Menu Bars and Toolbars. You can place this button on:

   • Your main toolbar above,

   • your custom shortcut menu (if you’ve already created one), or

   • The Form View Control submenu under the Forms section of the built-in shortcut menu, just like we did when adding the button for the Animated Floating Calendar.

5. Right-click on the button and display properties. Type =ZoomOpen() in the Action control and close the Toolbar Customize Dialogue control.
6. Copy and paste the following VB Code in a Global Module and save it.

   Public Function ZoomOpen()
   '------------------------------------------------------
   '  Author  : a.p.r. pillai
   '  Date    : 29/07/2007
   '  Remarks: Open Zoom Control with Active Field's Data
   '------------------------------------------------------ 
   Dim varVal, ctl As Control, intFontWeight As Integer
   Dim strFont As String, intFontSize As Integer
   Dim BoolFontstyle As Boolean, boolFontUnderline As Boolean
   
   On Error GoTo ZoomOpen_Err
   
   Set ctl = Screen.ActiveControl   
   strFont = ctl.FontName   
   intFontSize = ctl.FontSize   
   intFontWeight = ctl.FontWeight   
   BoolFontstyle = ctl.FontItalic   
   boolFontUnderline = ctl.FontUnderline
   
   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
   End With
   
   ZoomOpen_Exit:
   Exit Function
   
   ZoomOpen_Err:
   Resume ZoomOpen_Exit
   End Function
   
   Public Function CloseZoom()
   '------------------------------------------------------
   '  Author : a.p.r. pillai
   '  Date   : 29/07/2007
   '  Save Edited Data back into the Source Field
   '------------------------------------------------------ 
   Dim vartxtZoom, strControl As String
   
   On Error GoTo CloseZoom _Err
   'copy the Edited Data into the Variable vartxtZoom 
   vartxtZoom = Forms("Zoom").Controls("txtZoom").Value
   'close the Zoom Form 
   DoCmd.Close acForm, Screen.ActiveForm.NAME
   
   'The Source Data field become active again 
   If Screen.ActiveControl.Locked = True Then
      strControl = Screen.ActiveControl.NAME
      MsgBox "Read-Only Field. Changes will not be Saved.", "Control : " & strControl
      GoTo CloseZoom _Exit 
   Else
       If IsNull(vartxtZoom) = False And Len(vartxtZoom) > 0 Then
           Screen.ActiveControl.Text = vartxtZoom
       End If 
   End If
   
   CloseZoom _Exit:
   Exit Function
   
   CloseZoom _Err:
   Resume CloseZoom _Exit
   End Function
   

7. Open one of your data entry forms, preferably one that includes a memo field with 2 or 3 lines of text. Right-click the memo field (or any other field you prefer) to display the shortcut menu, and click on the custom Zoom button that you added earlier.

   If you placed the button on a toolbar above, make sure to first select the target field on the form to activate it, then click the toolbar button to trigger the zoom action.

   In my case, I added the Zoom button to my custom shortcut menu and designed a custom button image for it. The icon depicts a small area being enlarged into a big window, but once finished, the image ended up resembling a CRT monitor.

   

   Click to Enlarge

   When the Zoom button is clicked, it calls the first function: =ZoomOpen(). This opens the Zoom In window, displaying the contents of the active field in a text box named txtZoom for easy editing. The font properties (such as font name, size, and style) of the original control are automatically applied to the Zoom In text box, ensuring a consistent look and feel during editing.

8. After adding or editing the text, click the OK button on the Zoom Control to save the changes back to the source field and close the window. If the Cancel button is clicked instead, the Zoom Control closes without saving any changes, leaving the original field data unchanged.

Click to Enlarge

If the active field is locked for editing, the Zoom In control will still open and display the field's data. However, when attempting to save changes, a message will appear indicating that the field is read-only, and the control will close without saving any edits.

If the source field is locked for editing, it's better not to open the Zoom In window at all with read-only data. Allowing the user to make changes only to reject them at the point of saving—with a warning—can feel frustrating and diminish the reliability of our Zoom In control.

Before opening the Zoom window, check whether the active field is locked for editing. If it is, display an appropriate message and skip opening the Zoom In control altogether. I’ll leave this enhancement as an exercise for you. Try modifying the code yourself—and if you run into any difficulty, feel free to ask me for help.

Download

Download Demo Database

• Start-up Screen Design
• Control Screen Design