Version 2.3 First Published 17 Jan 2024 Last Updated 10 Mar 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.
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!
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.
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:
Paint Me Transparent v1.2 ACCDB - approx 0.5 MB (zipped)
UPDATE 1: 18 Jan 2024
I 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.
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
I then took the idea a stage further to selectively hide additional controls.
In this case, selected values were hidden for comboboxes and images as well as checkboxes, textboxes and the buttons themselves.
NOTE:
Placing buttons over continuous form controls such as textboxes, checkboxes and combo boxes makes those controls uneditable even when the button is transparent.
Download
Click to download:
Paint Me Transparent v1.3 ACCDB - approx 0.5 MB (zipped)
UPDATE 2: 5 Mar 2024
Although the above example works well, it was clearly not at all realistic.
I decided to create another example based on a modified (and simplified) version of the Orders form from the new Northwind (Developers Edition) sample database.
I removed all the original form code and replaced the form record source (qryOrderList) with a single table, OrderSummary to make the example more straightforward.
I then locked the form so it was read only as in the original database.
Two command buttons cmdPaid (Paid Date) and cmdShipped (Shipping Date) are used to enter dates and update the order status columns.
Both buttons are normally set as transparent except in specific cases:
a) If stock has been allocated to an order, the Paid Date button becomes visible.
Click when payment is made and enter a date using the popup date picker form.
The PaidDate is populated, the Status changes to Paid and the button is hidden (made transparent).
b) The Shipped Date button now becomes visible.
Click when the order is shipped and enter a date.
The Shipped Date is completed, Detail Status changes to Shipped.
The order Status changes to Closed and the button is hidden again.
As transparent buttons can still be clicked, code is used to make sure this has no effect on the data.
CODE:
Private Sub cmdPaid_Click()
If Me.OrderDetailStatusName = "Allocated" And IsNull(Me.PaidDate) Then
'enter date selected from date picker
InputDateField PaidDate, "Select the Payment Date"
'if user didn't cancel then update record
If Not IsNull(Me.PaidDate) Then Me.OrderStatusName = IIf(Me.OrderDetailStatusName = "Shipped", "Closed", "Paid")
End If
End Sub
'=====================================================
Private Sub cmdShipped_Click()
If Me.OrderDetailStatusName = "Allocated" And IsNull(Me.ShippedDate) And Not IsNull(Me.PaidDate) Then
'enter date selected from date picker
InputDateField ShippedDate, "Select the Shipped Date"
'if user didn't cancel then update record
If Not IsNull(Me.ShippedDate) Then
Me.OrderDetailStatusName = "Shipped"
Me.StatusName = "Closed"
End If
End If
End Sub
'=====================================================
Private Sub Detail_Paint()
'This paints the buttons to be visible or transparent depending on record values
'bypass err 2424 if form filtered so neither button is visible
On Error Resume Next
'cmdPaid (Paid Date) button is visible only for new orders where stock has been allocated and payment has not yet been made
'i.e. if PaidDate is null, Status is 'New' and Detail Status is 'Allocated'
Me.cmdPaid.Transparent = Not (IsNull(Me.PaidDate) And Me.OrderStatusName = "New" And Me.OrderDetailStatusName = "Allocated")
'cmdShipped (Shipped Date) button is visible only where the order has been paid but not yet shipped
Me.cmdShipped.Transparent = Not (IsNull(Me.ShippedDate) And Me.OrderDetailStatusName = "Allocated" And Me.cmdPaid.Transparent)
End Sub
NOTE:
The same approach works with editable forms but is particularly useful for read only forms
Download
Click to download:
Paint Me Transparent v2.3 ACCDB - approx 1.6 MB (zipped)
UPDATE 3: 10 Mar 2024 - Video
I have now created a video (13:11) for YouTube to demonstrate how the transparent property of command buttons is used in both versions 1.3 and 2.3 above.
You can watch the Paint Me Transparent video on my Isladogs YouTube channel or you can click below:
If you liked the video, please subscribe to my Isladogs on Access channel on YouTube. Thanks.
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 (3 pages)
• Move and Resize Columns (2 pages)
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 10 Mar 2024
|
Return to Code Samples Page
|
Return to Top
|