Hide subdatasheet headers if no data (1 Viewer)

[alikona]

Hi all,

I'm working on a form that will allow the users to look up orders. I have a subform that is in datasheet format. This datasheet also has a subdatasheet.

Since this form is only to be used to look up records, I've suppressed the 'add new' blank rows by using Recordset Type: Snapshot and Allow Additions: No.

This is working properly but now I'm running into the issue where if the data has not been populated in the subdatasheet record, the headers for the subdatasheet are still there, with nothing beneath them. Visually, this is not very appealing.

Is it possible to suppress the headers/labels for a subdatasheet if no data is present?

 

[theDBguy]

Hi. Welcome to AWF!


I don't normally use subdatasheets, and I am not aware of any settings to turn off the headers. Sorry...

 

[Micron]

I guess it's a matter of personal preference, but showing them proves that the connection/structure is still working; it's just that there are no records for a particular view. I wish I knew a name for that type of scenario because I have seen lots of examples. One that I first encountered was when participating in an audit. The lack of entries in a form was a problem, i.e. how do you know that nothing applies for that form field versus someone forgot to fill it in? The answer was, use N/A (not applicable). The subdatasheet headers are your "N/A".

 

[MajP]

I find sub data sheets not to be very aesthetic and confusing, but that is just my opinion. I would normally do linked subforms. The first subform shows the records in continous view and the second shows the related child records of the record selected in the first subform. I usually add some code to hilite the selected record in the first subform.

 

[Micron]

I find sub data sheets not to be very aesthetic and confusing, but that is just my opinion

As do I, which reminded me that I was going to point out that they can be turned off in case alikona isn't aware of that, but I forgot.

 

[isladogs]

I also avoid subdatasheets partly because I think they tend to confuse end users but also because they affect performance. That's because the Subdatasheet(s) have to be loaded as well as the main data sheet.

There is definitely no vba command to hide a Subdatasheet which has no records. As already stated, they can be removed completely.

 

[theDBguy]

I also avoid subdatasheets partly because I think they tend to confuse end users but also because they affect performance. That's because the Subdatasheet(s) have to be loaded as well as the main data sheet.

There is definitely no vba command to hide a Subdatasheet which has no records. As already stated, they can be removed completely.

Ah, maybe there's a potential solution there. Check if the subdatasheet is empty and use VBA to remove the subdatasheet. Just thinking out loud...

 

[isladogs]

If you remove the Subdatasheet from the ribbon or using vba, it is removed for all records. Switching it on again if there is a Subdatasheet record would re-enable it for all main datasheet records. Its all or nothing.

Alikona
As already suggested , I'd use the main form and subform approach instead

 

[MajP]

Here is the concept of a linked subform. This may not be the most aesthetic because it was a code demo not a form demo. But the concept is Employee records on main form. The employees related orders in subform 1. The order details for the selected record in subform 2.

 

[alikona]

Thanks for all the input guys. I may end up just removing the subdatasheets depending on how the users would like to see the data. On my entry form, I do have it set up as multiple linked subforms instead of the datasheet/subdatasheet view.

I guess it's a matter of personal preference, but showing them proves that the connection/structure is still working; it's just that there are no records for a particular view. I wish I knew a name for that type of scenario because I have seen lots of examples. One that I first encountered was when participating in an audit. The lack of entries in a form was a problem, i.e. how do you know that nothing applies for that form field versus someone forgot to fill it in? The answer was, use N/A (not applicable). The subdatasheet headers are your "N/A".

FDA calls this CGMP (Current Good Manufacturing Practice) and that is true, especially in the medical device world, nothing should be left blank. For reports, I convert non required null values to "N/A" because of this.

 

[isladogs]

Why not make all fields required in that case and set N/A as the default values?

 

[Micron]

From a quality systems standpoint, that's just as bad as leaving them blank. I'd ask "how do I know someone meant for that field to be N/A versus just forgetting to put something of value that should have been entered?" Form validation would be the way to go if this thread has anything to do with QS.

 

[isladogs]

Good point!