First Published 2 Mar 2024

This topic came up as part of a lengthy thread by user Marshall Brooks at Access World Forums: Pop-Up Datasheet Form Positioning and Scrollbars Questions

Amongst other things, Marshall wanted to set alternate row colors for the datasheet and couldn't find the ribbon button

Initially not realising he was using a popup form, I instructed him to open the datasheet normally (not in design view).
Next click the Form Datasheet ribbon then the (wrongly named) Alternative Row Colour button.


NOTE: This button caption seems to vary depending on language & locale. In some locales, it is correctly named as Alternate Row Color.
In the UK (using en-gb), it is Alternative Row Colour.

This works perfectly for most datasheet forms, including those set as modal. However, it fails for popup datasheet forms where the ribbon does not appear.


Nor does the ribbon appear when using a datasheet subform or a datasheet in a split form.

I asked the Access team whether this was for technical reasons or a bug.

The response, which makes total sense to me, was that the ribbon state is based on the currently active window that is a part of the Access application window.
Popup forms are outside the Access application window and not seen as the currently active window

Similar issues prevent the form datasheet ribbon appearing with datasheets in subforms or split forms.



So what can you do if you have a popup datasheet and you want to set alternate row colors or apply conditional formatting?

You CANNOT do so with the datasheet in design view as its properties are designed to be modified at runtime
Although these ribbon items appear in the Format ribbon seen in design view, the buttons are disabled for datasheets.


If you go to Layout View, you can set conditional formatting but not alternate row colors




The workaround is simple. Open the datsheet in design view and temporarily change the Popup property to No


Change back to datasheet view, make the desired changes to alternate row colors (etc) and save the form.
Then change the popup property back to Yes. The changes made will 'stick'.

NOTE:
1.   For a datasheet subform, you need to open it as a standalone form.
2.   This solution won't work for datasheets in a split form.
      However, it is not difficult to create your own simulated split form to replicate the functionality and overcome some of the limitations of the built-in split form
      As you can see in the example shown below, the form datasheet ribbon is visible and the buttons enabled.


      For more details, see my article: Build Your Own SIMULATED Split form



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 2 Mar 2024

Return to Access Blog Page

Return to Top