Command Buttons can be kept hidden on Forms by setting their Transparent or Visible Property to No and make them visible to click, when 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 more 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.

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 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
   • Fore Color = 0

 

5. Select View Menu and remove check mark from 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
   • Whats 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 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 Drop down 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, 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 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 at the left, you can set the Visible Property of that field to No.

     

    
    

     

    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

Click to Enlarge

The instances of the same Transparent Command Button now appears 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 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 appears in the Code.