Single Expanded Subdatasheet only (1 Viewer)

BiigJiim · Oct 17, 2019

Hi,

I have a datasheet form with a datasheet subform. I would like to make it that when a user expands a subdatasheet, any other existing expanded subdatasheets are collapsed, so that the user only sees one expanded subdatasheet at a time.

Can anyone tell me if this is possible? I am using Access 2019.

Many thanks,
Jim

theDBguy · Oct 17, 2019

Hi Jim. Interesting request. I don't use subdatasheets, so I am not even sure how to approach this question. I'll give it a try and let you know.

isladogs · Oct 17, 2019

I also don't use subdatasheets as they slow database performance and can be confusing to end users. A much better alternative is a form and related subform.

If you must use subdatasheets, then you would need to first close all subdatasheets using code or the ribbon then open the new subdatasheet

BiigJiim · Oct 18, 2019

Thanks guys. I don't normally use subdatasheets either, for the reasons mentioned. But in this instance, the user needs to be able to drill down 3 layers in the data. I thought this would be more useful than having 3 separate subforms.

If subdatasheets offered this functionality as an option, to allow the user to always focus on a single expanded subdatasheet at a time, I think this would make the UI a lot less cluttered for users. Oversight on the part of MS.

BiigJiim · Oct 18, 2019

theDBguy said:
Hi Jim. Interesting request. I don't use subdatasheets, so I am not even sure how to approach this question. I'll give it a try and let you know.

Thanks DBguy, I would be interested to see how you get on.

isladogs · Oct 18, 2019

You may find the following commands useful:
DoCmd.RunCommand acCmdSubdatasheetCollapseAll
DoCmd.RunCommand acCmdSubdatasheetExpandAll
DoCmd.RunCommand acCmdSubdatasheetRemove

The screenshot below is from the only app where I use subdatasheets (as an option).
I use a command button to toggle the subdatasheets all open or closed ...

I think the screenshot illustrates perfectly the issues with subdatasheets.
Spot the deliberate error that I left in on purpose

I would still suggest using 3 levels of subform rather than 3 levels of subdatasheet

theDBguy · Oct 18, 2019

BiigJiim said:
Thanks DBguy, I would be interested to see how you get on.

Hi Jim. I'm afraid nothing useful on my end. You might give Colin's suggestion a try. Good luck!

BiigJiim · Oct 18, 2019

For the benefit of anyone else with the same question, the only way I could get this to work was in the OnCurrent Event to collapse all the rows and then just expand the current row using a SendKeys statement. The record with the focus is always expanded. Not very elegant, but it works, kinda...

DoCmd.RunCommand acCmdSubdatasheetCollapseAll
SendKeys "+^({DOWN})"

Hope it helps someone!

theDBguy · Oct 18, 2019

BiigJiim said:
For the benefit of anyone else with the same question, the only way I could get this to work was in the OnCurrent Event to collapse all the rows and then just expand the current row using a SendKeys statement. The record with the focus is always expanded. Not very elegant, but it works, kinda...

DoCmd.RunCommand acCmdSubdatasheetCollapseAll
SendKeys "+^({DOWN})"

Hope it helps someone!

Hi. At least you got something working. Good luck with your project.

Single Expanded Subdatasheet only (1 Viewer)

BiigJiim

Registered User.

theDBguy

I’m here to help

isladogs

MVP / VIP

BiigJiim

Registered User.

BiigJiim

Registered User.

isladogs

MVP / VIP

Attachments

theDBguy

I’m here to help

BiigJiim

Registered User.

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread