Ms-Access Advanced VBA Programming Techniques, Tips and Tricks.

Ads

Double-Action Command Button

Double-Action doesn't mean that with one Click you can execute two different procedures. For that matter you can do several things at one click. Here, the emphasis is on programing 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 safety of the data and to prevent users changing the field values accidently.

But, when it is time to make some changes in one or more fields on 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 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 from 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 goes for adding 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 for safety of data.

The only question at this point is that how do we determine which click is for what, 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 applying 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 sate 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 set the Form Locked or Unlocked state. At every click on the Button we will change the Caption Value to Edit or Lock interchangably.

    • 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 Value 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.
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 comments.

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

Trending

Infolinks Text Ads

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

Activity Dates and Quarterly Reports

There are four Quarters in a Year: Jan - Mar = 1st Quarter Apr - Jun = 2nd Jul - Sep = 3rd Oct - Dec = 4th First three months of the yea...

Labels

Blog Archive

Recent Posts