Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Double-Action Command Button

Introduction.

Double-Action doesn't mean that with one click you can execute two different procedures. For that matter, you can do several things with one click. Here, the emphasis is on programming the same Command Button for doing two different Actions i.e., when the Button is clicked a second time it will execute a different action than it did for the first one.

To make the point more clear, let us look into a real-world example. When the user opens a Data Editing Screen we want the screen to be kept locked for the safety of the data and to prevent users from changing the field values accidentally.

But, when it is time to make some changes in one or more fields in the current record the user will click the Command Button to unlock the Form for editing. After editing, the User will click the same button again to lock the screen.

Let us try an example. But, before starting with the design task, let us note down the above points with more specifics so that you will have a general idea of what we are trying to do when we proceed with the implementation steps.

Consider the Requirements

(a) When the Form is open for the first time, or when moved from one record to the other, the Form must be locked for editing. Why, when moving from one record to the other? Well, the User clicked the Button to unlock the Form for editing, but he forgot or didn't bother to click a second time to lock the Form. So we will apply an automatic Lock when she moves into another record.

(b) When the user adds a New Record the Form is fully open for keying in values and our Double-Action Command Button will be in a disabled state.

(c) When the user Clicks the Double-Action Command Button (as we call it now) for the first time, unlock the Form for editing.

(d) When she Clicks the same button a second time, lock the form again, to prevent the change of data by mistake.

The only question at this point is that how do we determine which click what action and if the user repeats the clicks several times, then what happens? It is very simple, read on.

Design Task

  1. Open one of your existing Projects with a Data Editing Screen already available in it.

  2. Create a Command Button at the Footer of the Form.

  3. Click on the Command Button to select it, if it is not already in a selected state, display the Property Sheet (View - - > Properties).

  4. Change the Name Property to cmdEdit.

  5. Change the Caption Property (very important) Value to Edit.

  6. To take care of the points that we have noted at (a), and (b) above, display the VBA Module of the Form (View - - > Code) copy and paste the following Code into the Module.

    Private Sub Form_Current()
    If Me.NewRecord Then
        With Me
            .cmdEdit.Caption = "Edit"
            .cmdEdit.ForeColor = 0
            .cmdEdit.FontBold = False
            .AllowEdits = True
            .cmdEdit.Enabled = False
        End With
    Else
        With Me
            .AllowEdits = False
            .cmdEdit.Caption = "Edit"
            .cmdEdit.ForeColor = 0
            .cmdEdit.FontBold = False
            .cmdEdit.Enabled = True
        End With
    End If
    End Sub 

    The above code will take care of the New Record entry event as well as apply the automatic Lock when the User didn't Click the Command Button to Lock the Form directly.

    Now we can concentrate on the Button Click part i.e. (c), and (d) points given above. Here, we have to check for the state of the Button when Clicked by the User. The trick is checking the Caption Value of the Button, and decide what is the user's intentions, and setting the Form Locked or Unlocked state. At every click on the Button, we will change the Caption Value to Edit, or Lock interchangeably.

    • If the Caption Value was Edit when the user clicked the button, then he/she wants to edit values on the Form.

    • If the Caption was Lock, then the user wants to lock the Form after editing the data.

  7. The following code will do the trick. Copy and paste the VBA Code into the Form's Module and save the Form.

The VBA Code

Private Sub cmdEdit_Click()
Dim cap As String
cap = Me.cmdEdit.Caption
Select Case cap
     Case "Edit"
         With Me
          .AllowEdits = True
          .cmdEdit.Caption = "Lock"
          .cmdEdit.ForeColor = 128
          .cmdEdit.FontBold = True
          .Refresh
         End With
    Case "Lock"
        With Me
          .AllowEdits = False
          .cmdEdit.Caption = "Edit"
          .cmdEdit.ForeColor = 0
          .cmdEdit.FontBold = False
          .Refresh
        End With
End Select
End Sub 

Experiment with your design and check whether it works every time correctly as expected.

If any suggestions for improvement or found that it didn't work the way you expected it, then please post them in the comments.

Earlier Post Link References:

  1. Command Button Animation
  2. Double Action Command Button
  3. Colorful Command Buttons
  4. Transparent Command Button
  5. Command Button Animation-2
  6. Creating Animated Command Button with VBA
  7. Command Button Color Change on Mouse Move

Share:

8 comments:

  1. Mr. Pillai,

    A double action command button is very helpful. Thank you for this post.

    I was able to get your code to run on MS Access 2003 but can't seem to adapt it to my specific need. Can you help me?
    I am generating a document number using a command button and visual basic. The code is working.
    The document number starts with three letters, which are always the same (ABC). The letters are followed by a six digit number (must be 6 digits), then the initials of the person adding the document (varies from 2 to 4 letters), and if the document is a revision of an existing document, then ".Rev" followed by the revision number.
    The first action command of the "Generate Document Number" button is twofold:
    If the document is a revision, the user provides the original document number, the initials of the person adding the document (from a drop down box), and the revision number. The document number is generated by concatenating the provided document number & person's initials & ".Rev" & the revision number. The document number may look like ABC000044JB.Rev1
    If the document is not a revision (Me.RevisionNumber = "None"), the user supplies the initials of the person adding the document, then I loop through each MID(Me.DocumentNumber 4, 6) (While Not EOF) and find the largest of the six digit numbers, maxnum, (using EVAL). That number is incremented by one. The original document number is then [Document Number:] = "ABC" & maxnum + 1 & the initials of the person adding the document. It may look like ABC000045JB
    Trouble:
    The problem with this is when the user generates a document number then needs to change either the initials or revision number. When they make their change and use the "Generate Document Number" command button again, it increments the current document number and replaces an earlier document number with the one just changed. So the new number would be ABC000046KD or ABC000046JB.Rev1 but a document number previously entered has been replaced by ABC000045JB. That messes up our library.
    I am trying to use your code to resolve this problem.
    Once the document number is generated, I need to make Left(DocumentNumber, 9) so that it cannot be changed (in any record) and the second action command of the "Generate Document Number" button (caption now should be "Update Document Number") so that if a user changes either the initials or revision number, it will keep Left(DocumentNumber, 9) and concatenate the initials & ".Rev" & revision number. (If things get bad for the user, I have a "delete the current record" button.)
    I would like to make it so that Left(DocumentNumber, 9) of the Document Number field is .AllowEdits = False but the rest of the document number and the rest of the form can be edited.

    Can you help me with this? Or perhaps point me in the right direction?

    Thank you, I very much appreciate your time and expertise.

    /R,
    Jan

    ReplyDelete
  2. Sorry for the delay.

    Since, part of the same Field cannot be locked from editing you can make the Document Number into two segments, first 9 characters in one field and the rest in second. On the table you can define the Document Number Fields as Primary Key using both segments together. On the Form lock the first segment field (Me.FirstSegment.Locked = True) so that it remains safe. After editing the second segment, on the Lost Focus Event of the field, save the current record’s Form Bookmark (strBkmk = Me.Bookmark) in a variable and scan through the recordset (.recordsetclone of the Form) with the modified Document Number. If a match is found within the Recordsetclone and the Bookmark doesn’t match with the current Form Bookmark then you found another record with the same number and warn the User about the wrong change. You can put back the old value of the second segment by referring the .OldValue property in Code, Me.SecondSegment.Value = Me.SecondSegment.OldValue and put back the Focus on the second Segment again.

    For resetting the focus back to the same field, you have to define a variable at the Form’s Global level, say Err_Flag as Boolean. Check the status of this variable on the Got_Focus event of the next field in the Tab Index order, if it found set with the value True then put the focus back to the second segment field (me.secondsegment.setfocus = true) so that the user is forced to have a re-look on the change of number.

    This may not be the 100% correct solution to your problem but I hope it will help you to think differently.

    Regards,

    ReplyDelete
  3. I had a re-look at the problem that you have presented and created a small sample database with test routines. I hope this will help you to find answers to most of your Queries. You may download this database in Zipped form by following this Link: http://www.msaccesstips.com/downloads/2008/04/DocNo.zip.
    You may modify the code further to refine them against User made errors like very large version number or irrelevant Initials and so on.

    1. On the current event of the Form the Document Number can be divided into 3 segments (first 9 characters, Initials & Revision Number, while maintaining the original Document Number in one field) and display them in 3 different unbound Text Boxes. The Original Document in full can be displayed in the Field in a locked
    condition. The first segment Text Box also must be kept locked.

    2. When the User makes changes to the Initial and Revision Text Boxes, log that event and enable the 'Revision Update' Command Button so that the user can click to generate a new Version of the existing Document. Keep the Command Button disabled till the user makes some changes to these two text boxes.

    3. a) Assemble Document Number using all the three segments edited by the User and check in the recordsetclone whether a record already exists with the same number. If so display a warning message and quit the procedure. If the Document Number is defined as primary Key Field then MS-Access will automatically take care of this step.

    b) Add a new Record in the Recordsetclone using the Document with New Version Number and copy other fields into it from the Form Fields.

    c) Refresh the recordset to register the Bookmark of the new record added. Search for the same record in the recordsetclone with the Document Number and use the Bookmark of the recordsetclone to set the Bookmark of the Form resulting the new Record become Current on the Form.

    4. When the User attempts to add a New Document we can generate the new Document Number by using the maximum Number available in the table + 1 in the Before_Insert Event Procedure. Maximum value available in the table can be obtained using a Total Query (SELECT Max(Val(Mld([DQCNQ],4,6))) AS [MAX] FROM Tablel;).

    5. Read the value into the program using Dlookup() Function. The New Number will be created and recorded only when the user makes an entry of a character in any of the Fields on the Form.

    The Command Button's usage comes only once when attempting to create a new Version of the Existing Document.

    ReplyDelete
  4. I had previously used two command buttons to reach the same goal, one on top of the other - one hidden, one not. I was glad to see your trick - which more than anything, helped me think differently.

    However, I put it to the test, and while it works, it seems to take longer to process, where the two button version happens instantly. I'll include my code, so you can tell me if something I've done is causing this.

    Thanks for your help!

    Private Sub Edit_Click()
    Dim cap As String
    cap = Me.Edit.Caption
    Select Case cap
    Case "Edit List"
    Forms!ProspectForm!ContactList2b.Form!ContactSelect.Visible = True
    With Me
    .DeleteContacts.Visible = True
    .Edit.Caption = "Edit Off"
    .Refresh
    End With
    Case "Edit Off"
    Forms!ProspectForm!ContactList2b.Form!ContactSelect.Visible = False
    With Me
    .DeleteContacts.Visible = False
    .Edit.Caption = "Edit List"
    .Refresh
    End With
    End Select
    End Sub

    ReplyDelete
  5. Probably the Font Color and Style change causes the delay.

    Regards

    ReplyDelete
  6. I have been searching all over the web for a solution like this, and your double click command button has made life so much easier. I want to be able to lock all my fields, except one though, as I still want to include a surname search option. Is there something I can add in to your code to allow this. Many thanks

    ReplyDelete
  7. [...] LEARN MS-ACCESS TIPS AND TRICKS » Blog Archive » Double-Action … [...]

    ReplyDelete
  8. [...] The Sub-Form is enabled with Allow Additions, so that you can add data into it. The blank row appearing below the current record is never saved as a record before you enter any data into it manually or through the control of a program. If you need only Editing the existing records then you may set the Sub-Form's Allow Additions property value to No. But, this can be controlled through VBA programs for enabling/disabling at will. If you want to explore this aspect find the details from this Link: Double-Action Command Button. [...]

    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