Code Samples for Businesses, Schools & Developers

Version 1.2             First Published 17 Jan 2024            Last Updated 29 Jan 2024


This is the fifth article in my series showing how functionality can be added to continuous forms.

It was prompted by a comment by Saphirah in response to a question at Access World Forums: Button visible/invisible in a Continuous Form

The original question was:
I have a continuous form, and I have a command Button by the side of each record, also I have a yes/no box in each record
Can I make the button visible/invisible depend of the box if its true or false?

The initial response from several experienced Access developers was that it couldn't be done.

This is because only limited formatting of individual records is possible in continuous forms as these actually consist of one set of controls repeated for each record.

Form Design
The standard solution is to use conditional formatting. However whilst this is available for textboxes, it cannot be used for command buttons (or labels).
The conditional formatting button is disabled for both of these control types.

Luckily there is another approach, as pointed out by Saphirah:
You can set your buttons "Transparent" Option in the OnPaint Event of your Form Detail Section.

This idea was completely new to everyone else in the thread. So simple . . . but it works!

Main Form
In this example, the command button is transparent (so not visible) when the InStock checkbox is ticked.
When a button is clicked, the checkbox is set false and that button is immediately 'hidden'

This just requires the following code in the Detail_Paint and button click events:

CODE:

Private Sub cmdUpdate_Click()
      If Me.chkInStock Then Me.chkInStock = False
End Sub

Private Sub Detail_Paint()
      Me.cmdUpdate.Transparent = Not Me.chkInStock
End Sub


Optionally, as in my example, you can also lock the checkbox so it cannot be clicked and the button click event cannot be reversed.

Checkbox Locked
NOTE:
1.   You cannot set the Visible property false using the Paint event at runtime.
      Attempting to do that results in run-time error 32521: You can't change the value of this property in the OnPaint event

2.   Making it transparent has much the same effect except that transparent buttons can still be clicked!
      That is easily handled using code such as that shown above in the button click event

3.   Only command buttons have a transparent property. This method cannot be used for other control types such as textboxes etc.

I hope this article will help inspire others to find ways of using this very simple but effective approach.



Download

Click to download the example app:     Paint Me Transparent v1.2      ACCDB - approx 0.5 MB (zipped)

UPDATE 18 Jan 2024
I have just used a similar idea to provide a solution to a question from an Italian user at the MS Answers forum: nascondere casella con flag (Hide Checkbox with Flags) In this case, I placed a button over the checkbox and made it transparent when another field had a specified value.

Using my own example database, I had first tested this idea by hiding the CAT control with a button cmdCATCover when the CAT field value = "B".
When the field had any other value the button is made transparent.

Hide CAT = B
Doing this required just one extra line of code in the Detail_Paint event

CODE:

Private Sub Detail_Paint()
      Me.cmdUpdate.Transparent = Not Me.chkInStock
      Me.cmdCATCover.Transparent = Nz(Me.CAT, "") <> "B"
End Sub


NOTE:
Placing buttons over continuous form controls such as textboxes, checkboxes and combo boxes makes those controls uneditable even when the button is transparent.



More Continuous Form Examples

The following articles provide further examples of functionality added to continuous forms:

•   Highlight Current Record
•   Highlight Selected Control or Column
•   Highlight Filtered Columns
•   Add Multiselect Filter   (2 pages)
•   Sort columns
•   Hide duplicate values in a column   (as for reports)
•   Hide & Restore Selected Columns   (2 pages)
•   Freeze columns   (2 pages)
•   Move columns

Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 29 Jan 2024



Return to Code Samples Page




Return to Top