Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Transparent Command Button


Command Buttons can be kept hidden on Forms by setting their Transparent or Visible Property to No and making them visible to click, when a certain condition is met or when a particular User opens the Form.

For example, when a Form with Data is routed through Networks to different Users (in a Secured Database) for verification, Approval of the contents, different Command Buttons can be created and programmed for each user and make them visible only when the respective User opens the Form or subject to the verified status of Data at lower Rank of Users and so on.

The most popular method in practice, in the above situations, is to disable the Command Button and keep it visible all the time and enable it when required. Making a Button visible or invisible is another way of doing the same thing. We cannot set the Command button's Enabled or Visible Property to No when the Button is active. We must shift the Focus from the Button to some other Control before setting the above properties to No through VBA Code.

The Transparent Property setting doesn't have this restriction. You can make the Command Button visible or invisible by setting the value to No (for Normal View) and Yes to make it disappear.

This method has some side effects. If the User knows exactly from where the Command Button disappeared then he can click on the same spot and the transparent Command Button will respond with its programmed action. But we can trick the User by changing the Button's invisible run time parking position to a different location by increasing or decreasing the Left Property value after saving the original value into a Module level Global Variable. When it is time to restore the Button to its normal shape on the original spot we can use the Left Property value saved in the Global Variable.

Designing a Table and Form.

Here, we are going to use the Transparent Command Button differently for designing and running a Main Switchboard Menu.

  1. Design a Table using the Structure shown below and enter a few sample records given in them.

  2. Design a Tabular Form using the above table using the Form Wizard and save it with the name DataFiles.

  3. Open the Form in Design view. Keep the ID and Desc fields and remove others. Keep Shift Key down and Click on both Fields to select them together. Or Click outside the Fields and drag the mouse over to select them both.

  4. Display the Property Sheet (View - - > Properties) and change the Following Property Values.

    • Enabled = No

    • Locked = Yes

    • Top = 0.0417"

    • Height = 0.1771"

    • Back Style = Normal

    • Back Color = -2147483633

    • Special Effect = Raised

    • Border Style = Solid

    • Border Color = 0

    • Border Width = Hairline

    • ForeColor = 0

  5. Select View Menu and remove the checkmark from the Form Header/Footer Option. Select Yes to the Warning message to delete the Header/Footer Sections from the Form.

  6. Click on the top left corner of the Form to de-select all controls and select the Form. Display the Form Property Sheet (View - -> Properties) and change the following Form Property Values.

    • Form Width = 2.5417"

    • Default view = Continuous Forms

    • Views Allowed = Form

    • Allow Edits = No

    • Allow Additions = No

    • Allow Deletions = No

    • Record Selectors = No

    • Navigation Buttons = No

    • Dividing Lines = No

    • Scrollbars = No

    • Border Style = None

    • Control Box = No

    • Min Max Buttons = None

    • Close Button = No

    • What this Button = No

    • Allow Design Changes = Design View Only

  7. Draw a Command Button about the size of both Field widths put together somewhere below the Fields so that we can modify the properties of the Command Button and place it over both the Fields in a transparent state.

  8. Select the Command Button and display the Property Sheet. Change the following Property Values:

    • Name = cmdMenu

    • Transparent = Yes

    • Height = 0.1771"

  9. Click on the On Click Property and select Event Procedure from the Dropdown List and click on the Build (. . .) button to open the Form's VBA Module.

    Here we can write code to test the ID Value of the record clicked by the User and open the Form corresponding to that Number.

    The VBA Code for a simple method to test and Open the Form corresponding to the ID number of the record clicked is given below.

  10. Copy and paste the following code into the VBA Module of the DataFiles Form and save the Form.

    Private Sub cmdMenu_Click()
    Dim IDNumber As Integer, strForm As String
    IDNumber = Me![ID]
    strForm = ""
    Select Case IDNumber
        Case 1
           strForm = "CRREQ_MASTER"
        Case 2
           strForm = "Dept_Codes"
        Case 3
           strForm = "Branch"
    End Select
    If Len(strForm) > 0 Then
       DoCmd.OpenForm strForm, acNormal
    End If
    End Sub

    You can use a better method, a more powerful and re-usable one that I have explained with VBA Code in an earlier Article with the Title: Opening Access Forms. I recommend that you read that article and the method explained there, which is easy to implement in any of your Projects. The sample table that we have seen at the beginning of this article is taken from that example with additional fields (Forms, Macro & Type). If the Code given there is used, you can add any number of records in the above table and don't need to test each ID number as we did in the above code. Besides that, you can open a Form directly or run a process, through macros, and at the end of the process open a Form to display the result by setting the value in the Type field to 0 or 1, when a new record is added to the table.

  11. Drag the Transparent Command Button (or use Ctrl Key with Arrow Key combination in Office 2000, Arrow Key alone in Office 2003, after selecting it) and place it over ID and Desc Fields correctly as shown below.

  12. Click on the Detail Section of the Form and Display the Property Sheet. Change the Details Section height.

    • Detail Section Height = .25"

    The finished design of the Form will look like the image given below in Normal view. This Form will be used as a Sub-Form on the Control Screen (Main Switchboard) for our Data Files Menu. If you don't want to show the Serial Numbers appearing on the left, you can set the Visible Property of that field to No.

    Trial Run

    Open the Main Switchboard Form of your Project (or any Form to try it out) drag and place the above Form on it. I placed the Menu on the sample Form that we have used for the Colorful Command Buttons Article.

  13. Click on the Sub-Form, Display the Property Sheet, and change the following Property Values:

  • Special Effect = Flat

  • Border Style = Transparent

The instances of the same Transparent Command Button now appear on top of each record in the Form and will respond to the Clicks on them. We can check the Value of the record on which the click was received and open the Form corresponding to that record ID number.

When you add more records to the menu table they will automatically appear on the menu without any modifications to the design.

Try the Menu by adding Form Names already available in your Project replacing the ones that appear in the Code.

  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


1 comment:

  1. Nice dispatch and this enter helped me alot in my college assignement. Thank you for your information.


Comments subject to moderation before publishing.




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


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