Transparent Command Button ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

Command buttons on forms can be hidden by setting their Transparent or Visible property value = 'No', and then made visible again when a specific condition is met or when a particular user opens the form.

Example:
In a secured database where a form is routed through a network for data verification and approval by multiple users, you could:

Create separate command buttons for each user role.
Show each button only when the relevant user opens the form.
Base visibility on the verification status set by users of a lower rank.

The most common approach in such scenarios is not to hide the button, but to disable it while keeping it visible, and then enable it when required. Setting an active button’s Enabled or Visible property cannot be set to 'No'. You must first move the focus to another control before making the change in VBA.

The Transparent property does not have this limitation. You can make a command button invisible by setting the Transparent Property value to'Yes'.

However, there is a side effect:
If a user knows the exact location of the invisible button, clicking that spot will still trigger its action. To prevent this, you can “park” the invisible button in a different location at runtime by modifying its Left property.

Before moving the button, save its current Left position to a module-level global variable.
When it’s time to restore the button, use the saved value to reposition it in its original location.

Designing a Table and Form.

Here, we will use the Transparent Command Button for a Main Switchboard Menu.

Design a Table using the Structure shown below and enter a few sample records as given below.
Use the Form Wizard to design a Tabular Form for the above table and save it with the name DataFiles.
Open the Form in Design view. Keep the ID, Desc fields, and remove other columns. Keep the 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.
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
Select View Menu and remove the check mark from the Form Header/Footer Option. Select Yes to the Warning message to delete the Header/Footer Sections from the Form.
Click on the top left corner of the Form to deselect 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
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.
Select the Command Button and display the Property Sheet. Change the following Property Values:
- Name = cmdMenu
- Transparent = Yes
- Height = 0.1771"
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.
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
```
Note: A more powerful and reusable method for handling this functionality is explained in my earlier article, Opening Access Forms. I recommend going through that article, as the method described there is very simple to implement in any project.
In this example, the sample table shown earlier has been adapted from that article’s example, with additional fields (Forms, Macro, and Type). If you use the code provided in that article, you can add any number of records to this table without needing to test each ID value individually, as was required in the earlier approach.
This method also allows you to:
- Open a form directly, or
- Run a process via a macro and, at the end of the process, open a form to display results.
You can control this behavior by setting the Type field value to 0 or 1 when inserting a new record.

To implement this with your command button, drag the transparent button (or, for precise positioning, use Ctrl + Arrow Keys in Office 2000 or the Arrow Keys alone in Office 2003 after selecting it) and place it directly over the ID and Desc fields, as shown below.
Click on the Detail Section of the Form and Display the Property Sheet. Change the Details Section height.
- Detail Section Height = .25"
The completed form design will appear as shown in the image below when viewed in Normal View. This form will function as a subform on the Control Screen (Main Switchboard) for our Data Files menu.

If you prefer not to display the serial numbers on the left side, simply set the Visible property of that field to No in the Property Sheet.

Trial Run
Open the Main Switchboard form of your project (or any other form you’d like to test with) and drag the form you created in the previous step onto it.

In my example, I placed the menu form on the sample Switchboard form used in the Colorful Command Buttons article.
Click on the Sub-Form, Display the Property Sheet, and change the following Property Values:

Special Effect = Flat
Border Style = Transparent

The transparent command button instances now appear above each record in the form and will respond to user clicks. Using VBA, you can detect which record was clicked and open the form associated with that record’s ID.

When new records are added to the menu table, they will automatically appear in the menu—no design changes required.

Test the menu by replacing the sample form names in the code with actual form names from your project.