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

For example:
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.
      For example:

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
            Me.Requery
      End If

End Sub

      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 . . .



Download

Example database: Datasheet Event Bug     ACCDB file     Approx 0.45 MB (zipped)



Acknowledgements

Thanks to Bruce (AKA DeluxeInformation) for alerting me to this issue in his thread at Utter Access forum: Another annoying inconsistency/bug



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 28 Feb 2023

Return to Access Blog Page

Return to Top