Single Expanded Subdatasheet only

[BiigJiim]

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]

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]

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]

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]

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]

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]

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]

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]

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.