Hiding fields in Details section (1 Viewer)

jharding08

Member
Local time
Today, 02:43
Joined
Feb 16, 2021
Messages
55
I have many fields as well as a subreport in the detail section of my report, each one is for a person and their info

If any of these fields dont have data, I am hiding the header and have the Can Shrink of both to Yes.

The Headers are textboxes and only show text if the textbox field has data, if not, both should Shrink.

I also have the detail set to Can Shrink, but also to Can Grow since the subreport for Children is a continous form that has three lines per child

Here is the design view:
1615228236644.png


The report does shrink the fields that don't have data ( I tested with solid border lines), but the gap between fields doesnt shrink that much. Is it because of the subreport? why is Email2 spaced out from Email1? Is there a way to shrink the fields in the left column and let the subreport continuous form grow on the right? I did test by removing the subreport and the gaps on the left were still between the lines that have data

1615228202345.png
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:43
Joined
May 21, 2018
Messages
8,463
I think you are limited because of the subreport. If I am correct about that, and If I had to do this I would consider concatenating all of that information into one big textbox using rich text and put bold tags around headers (address, home phone, email 1 ,etc.). The concatenation should be simple, the bolding would be more a pain. Not trivial, but should be doable. The other option would be a temp table. You show 8 lines of data. Assume your temp table has fields
txt1, txt2, ...txt8.
Then populate the data that exists into these fields. For me this would be the easier solution than figuring outh the rich text tags.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:43
Joined
May 21, 2018
Messages
8,463
One thing I tried that does not seem doable is to change the control source of the controls in the onpaint event. I tried to see if a piece of data was missing and reset the control source of the control to the next included piece of data. Move them up your "ladder" of controls. This caused the the report to crash with no error. Then I tried in the onprint event and that is too late, you cannot change at that time.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:43
Joined
Feb 19, 2002
Messages
42,970
It's hard to see what's going on but the space doesn't shrink if there are overlapping controls. So, make sure that there are no overlaps.

Maj was posting at the same time. I agree that concatenating might solve the problem. Use & vbCrLf after each item to get a new line.
 

isladogs

MVP / VIP
Local time
Today, 09:43
Joined
Jan 14, 2017
Messages
18,186
The subreport height in design view is the minimum space it will occupy. If needed it can be taller.
So try shrinking the subreport height so it is only just visible e.g. 0.1 cm. Does that help?
 
Last edited:

jharding08

Member
Local time
Today, 02:43
Joined
Feb 16, 2021
Messages
55
I am kind of doing the concatenating, but adjusting the top property for the header and the textbox where the value isnt null and making it visible. I'll add the height of the textbox to get to the new top height for the next non-null textbox vertically

It seems to work and line them up, but for sure the subreport is putting the gap in the Left column of data. Even if I make it .1", it still grows and creates the gap. I'd have to put it below the last textbox so it can grow and not create spaces

Will any textbox have a gap from the subreport like that, even if its concatenated into one?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:43
Joined
Feb 19, 2002
Messages
42,970
You need to post a picture. Circle the problem. Make both the subreport control and the control for the concatenated text one line high and align the top of the text with the top of the subreport control.
 

jharding08

Member
Local time
Today, 02:43
Joined
Feb 16, 2021
Messages
55
The concatenation to one text box works. I used <b>text</b> for bold in the rich text and lined up the headers as best I could using Calibri(Detail). I might switch to a monospace font but the rest is calibri and looks fine. Thanks for the help!
 

jharding08

Member
Local time
Today, 02:43
Joined
Feb 16, 2021
Messages
55
One last question on this - I am concatenating the fields in the control source of the textbox. Before the Phone number was a concat of Spouse1 and Spouse 2, but now I need to be able to hide details on deceased people, so I broke them out to Phone1 and Phone2.

IIf(Nz([Phone1],"")<>"", "<b> Home: </b>" & [Phone1]) & IIf(Nz([Phone2],"")<>"",", " & [Phone2])
 

jharding08

Member
Local time
Today, 02:43
Joined
Feb 16, 2021
Messages
55
So now I need to first check if the Flag to show Deceased is True or False (chkShowDec), if Show Deceased is false, I need to check each PersonID for deceased=true, then I need to show the header "Home:" in front of the phone numbers that aren't null.

Is there an efficient way to do this in the control source (with IIFs?)
 

jharding08

Member
Local time
Today, 02:43
Joined
Feb 16, 2021
Messages
55
I created a function where I pass in all the values I need to construct the proper string and it returns the string for the control source, but can I call this function from the control source? It returns a string, but then the textbox just prints out the string.
 

jharding08

Member
Local time
Today, 02:43
Joined
Feb 16, 2021
Messages
55
Example:
  • Textbox Control Source: =fnSetFBInfo([chkShowDec]," Home: ","[Phone]",[Phone],[PersonDec1],"[PhoneSP]",[PhoneSP],[PersonDec2])
  • fnSetFBInfo returns string: ="<b> Home: </b> "& [Phone] & " , " & [PhoneSP], which shows in the display of the textbox on report view
  • If I put textbox.controlsource : ="<b> Home: </b> "& [Phone] & " , " & [PhoneSP], It shows the values for [Phone] and [PhoneSP], which I need
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:43
Joined
May 21, 2018
Messages
8,463
Not sure, but maybe the following.

Works with any amount of fields.
Pass in the caption as "Caption1;Caption2;....."
Pass in the fields

Code:
Public Function ConcatFields(Captions As String, ParamArray Fields() As Variant)
  Const DivOpen = "<div>"
  Const DivClose = "</div>"
  Const BoldOpen = "<Strong>"
  Const BoldClose = "</Strong>"
  Dim I As Integer
  Dim arrCaptions() As String
  arrCaptions = Split(Captions, ";")
  For I = 0 To UBound(arrCaptions)
    If Not IsNull(Fields(I)) Then
        If ConcatFields = "" Then
          ConcatFields = DivOpen & BoldOpen & arrCaptions(I) & ": " & BoldClose & Fields(I) & DivClose
        Else
          ConcatFields = ConcatFields & vbCrLf & DivOpen & BoldOpen & arrCaptions(I) & ": " & BoldClose & Fields(I) & DivClose
        End If
    End If
  Next I
End Function

Concat.jpg
 

Attachments

  • ConcatRT.accdb
    796 KB · Views: 491

Users who are viewing this thread

Top Bottom