Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Edit Data in Zoom-In Control

Introduction

While designing a Form, Report, or Query certain properties of these objects can Zoom in and edit the property values in a big window, like Record Source Property, Filter or Order By property, etc. When you right-click on these properties a Shortcut Menu will open up with the Zoom option on it, (see the image below) and when clicked we can edit the property values comfortably in a big window.

But this option is not available when editing data on Forms, for large Field Type like Memo or text fields that hold more data than it displays.

The Zoom Tool Button is available under the Properties Category of Forms, Reports, Query, and Index groups in the Built-in Shortcut Menus. We can make a copy of this button and paste it on our Custom Shortcut Menu or on the built-in Form View Control Shortcut Menu, as we did for Animated Floating Calendar, and use it on our Forms if needed.

Designing the Zoom Control

But we will do it differently. We are going to design our own Zoom In Control and it will be interesting to explore and find out how this thing works?

We can create this very easily. We need a small Form, two small VB Routines, and a Shortcut Menu Button, similar to the one that we have created in the Form View Control sub-menu of our Animated Floating Calendar.

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

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
    • Whats 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 (see earlier Post Custom Menu Bars and Toolbars) either on the Toolbar above or on your Custom Shortcut Menu, if you have created one earlier, or create a button on the Form View Control submenu under the Forms heading in the Built-in Shortcut Menu, where we have placed a new button for our 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 Editing Forms, preferably with a memo field having 2 or 3 lines of text. Right-Click the memo field (or any field that you prefer) to display the Shortcut Menu with the button that we have created. If you have created a Toolbar button above, then select a field on the form to make that field active, and then click on the Toolbar Button above. I have added a new button on my Custom Shortcut Menu and designed a Button Image (see Shortcut Menu image below), showing a small area zooming in into a big window, but the finished Button Image looks like a CRT Monitor.

    When the Zoom button is clicked it Calls the first Function =ZoomOpen() and the Zoom In window will open up with the active field's data in the txtZoom Text Box for editing. The font properties of the parent control will be copied into Zoom In Control.

  8. After adding/editing the text, click the OK button on the Control to save the edited data into the source field and to close the Zoom Control. If the Cancel Button is clicked, then the Zoom Control is closed without making any changes to the source field data.

If the active field is locked from editing, the Zoom In control will still open with the field data. But, while attempting to save changes, it will display a message indicating the read-only status of the field and close the control without saving the contents.

If the Source Field is locked from editing, then it is a better idea not to open the Zoom In Window at all with the read-only field data. Otherwise, after the user makes so many changes, and then tries to save them, issuing a warning message at that point is meaningless and will adversely affect the reputation of our Zoom In Control.

Before opening the Zoom window, check the Active Field's Editing Status, whether locked or not, if it is then displayed an appropriate message do not open the Zoom In Control at all. This change, I leave it to you as an Exercise. Modify the code yourself and try it. If you could not make it then let me know.

Download


Download Demo Database


Share:

8 comments:

  1. Although I like the idea, the Zoom.mdb (http://www.msaccesstips.com/2007/08/edit-data-in-zoom-in-control/) seems to have severe limitations. After entering a certain amount of data in the zoombox, closing it causes all the changes to be lost if the active control is a memo field. If I enter just a little new data, it keeps it. Why is this? I thought a string could hold up to 65k characters.

    ReplyDelete
  2. Hi Steven,

    Thank you for pointing out the Error. Please make a small change in the following line in

    Function CloseZoom()

    Screen.ActiveControl.Text = vartxtZoom

    change to

    Screen.ActiveControl.Value = vartxtZoom

    Please try it out after the change and give me a feed back, if you don't mind.

    Regards,
    a.p.r. pillai

    ReplyDelete
  3. Reg: Edit Data in Zoom Control.

    Using Shift+F2 will anyhow zoom any control. Would appreciate your comment in what way your procedure is better than Shift+F2.

    RL Narayan

    ReplyDelete
  4. The article reveals the Programming aspect of this control and the same goes for Repairing Compacting Database topic and others given below.  repairing compacting database with vba  working with chart object in vba  custom made form wizard  custom-report-wizard   The contents of this site are intended to be more than a basic tutorial guide.  

    Thanks,  

    ReplyDelete
  5. If you are using the runtime of access the zoom function is not included. So you have to do it with code.
    i was searching because i have an example for the zoom function, but i am searching for code which does sub and subsub forms also ...

    regards

    ReplyDelete
  6. Can I edit the shortcut menu to add the copy and past buttons in the shortcut menu of your database file? please help me. thank you.

    ReplyDelete
  7. Go through the following Posts:
    Calender and Toolbars.
    Custom Menus and Toolbars.
    Custom Menus and Toolbars2.

    ReplyDelete
  8. Thanks for a very interesting blog. What else may I get that kind of info written in such a perfect approach? I’ve a undertaking that I am simply now operating on, and I have been at the look out for such info.
    upload zoom recordings to google drive,

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code