First Published 25 Feb 2023 Last Updated 28 Feb 2023
When Harold Macmillan (UK Prime Minister 1957-1963) was asked what was the most difficult thing about being Prime Minister, he replied: "Events, my dear boy, events."
See Quote Investigator
And so it is with Access datasheets which, in many ways, behave differently to single and continuous forms
a) Even if a datasheet field is hidden in the property sheet, it will still be visible.
To hide the field, you must use the Hide Field command in the datasheet column context menu or reduce the field width to zero
This is by design and allows users to choose to show / hide individual datasheet columns at runtime. For example, as shown in the split form below:
b) When a field in a single or continuous forms is disabled or locked, it does not respond to any mouse events (as you would expect).
NOTE: Locked controls in these forms do still respond to key events e.g. key press
However, if a datasheet field is disabled, it DOES respond to the following events (whether or not the control is also locked):
- Click / double-click (textbox control only)
- Mouse Down / Mouse Up / Mouse Move (textbox / checkbox / combo box)
- Enter / Got Focus / Key Press (textbox / checkbox / combo box - all work when locked / do not work when disabled)
This is the same behaviour as for single and continuous forms
To be absolutely clear, you would expect some differences in behaviour for disabled and locked controls.
A control with Locked = Yes does respond to the mouse. You can click in it to give it the focus, and you can select text in it to copy.
All the discrepancies are for disabled controls in datasheets
Controls where Enabled = No should not be interactive so should not respond to any events. In my opinion, that should also apply to datasheets.
All the above information is also true for the datasheet section of a split form. For example:
This lack of consistency between the different types of forms and controls is far from ideal.
Another effect of the behaviour described above is that datasheet fields can be modified when they are disabled or locked using one of the above events.
Private Sub Field1_Click()
'all code still works when field locked or disabled in datasheet
MsgBox "Textbox Field1 clicked"
If MsgBox("Add a * before & after each line in this field?", vbQuestion + vbYesNo, "Alter records?") = vbYes Then
CurrentDb.Execute "UPDATE Table1 SET Field1 = '*' & [Field1] & '*';", dbFailOnError
The next two screenshots show the effect of this code
Clicking Yes updates the field records even though they are disabled
The issue with lack of consistency has recently been reported to Microsoft and I have had valuable feedback from a member of the Access team.
I have corrected some earlier errors in the article based on this feedback
I hope the issue will get fixed in the not too distant future . . .
Example database: Datasheet Event Bug ACCDB file Approx 0.45 MB (zipped)
Thanks to Bruce (AKA DeluxeInformation) for alerting me to this issue in his thread at Utter Access forum: Another annoying inconsistency/bug
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 28 Feb 2023
Return to Access Blog Page
Return to Top