Remove report elements when no value is present (1 Viewer)

swingline

Member
Local time
Today, 19:47
Joined
Feb 18, 2020
Messages
51
I am using Access 2010.

I have one table question bank and a few queries that pull random question by category for the bank. I have created reports based on these queries. The output is simply something like


1. Question contents
a. Answer one
b. Answer two
c. Answer three
d. Answer four

It just repeats the required number of questions per the query. What I would like to do is see if there is a way to hide when a text box has no value? For example

1. Question contents
A. True
B. False
C.
D.

I would want to remove text box c and d and allow the report to shrink down and save a little space. Is this possible to achieve or a bridge too far?
 

Ranman256

Well-known member
Local time
Today, 12:47
Joined
Apr 9, 2015
Messages
4,339
In the ON PRINT event:

Label.visible = not isnull(txtBox)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:47
Joined
May 7, 2009
Messages
19,169
you can use 1 Unbound Textbox.
call a function to fill this textbox.
the control source of the textbox:

=fillTextbox([ans1], [ans2], [ans3], [ans4])

public function fillTextbox(paramArray p() As Variant)
dim v As Variant
dim r As String
for each v in p
If Len(Trim(v & "")) > 0 then
r = r & v & vbnewline
end if
next
if len(r)>0 then r=left(r,len(r)-1)
filltextbox=r
end function
 

Isaac

Lifelong Learner
Local time
Today, 09:47
Joined
Mar 14, 2017
Messages
8,738
If your textboxes are standard white backcolor, on a standard report white backcolor, and don't have borders, you could use conditional formatting with no code.
Edit: I guess this wouldn't help with your shrinking.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:47
Joined
Oct 29, 2018
Messages
21,358
Hi. You can also try making your Textboxes really short and simply rely on the Can Grow property.
 

swingline

Member
Local time
Today, 19:47
Joined
Feb 18, 2020
Messages
51
In the ON PRINT event:

Label.visible = not isnull(txtBox)
This has the intended result for me. Any Ideas on how I could apply something similar to a image that is empty?

1. Question one



figure1.jpg



A. Answer 1
B. Answer 2
C. Answer 3
D. Answer 4

Anyway to hide a image box when printing a report? I used a image control box with no image and changed the control source to FigureLocation in the question table. The FigureLocation is just the file location of the image on our shared drive.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:47
Joined
May 7, 2009
Messages
19,169
you can use the Format event of the Report's Detail Section (this is applicable on Print Preview Only).

Code:
Dim mlng_image_height As Long

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Me.FigureLocation) Then
    Me.YourImage.Height = 0
    Me.Detail.Height = Me.Detail.Height - mlng_image_height
Else
    Me.YourImage.Height = mlng_image_height
End If
End Sub

Private Sub Report_Load()
mlng_image_height = Me.YourImage.Height
End Sub

the idea is to "shrink" and "expand" the image control.
 

swingline

Member
Local time
Today, 19:47
Joined
Feb 18, 2020
Messages
51
you can use the Format event of the Report's Detail Section (this is applicable on Print Preview Only).

Code:
Dim mlng_image_height As Long

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Me.FigureLocation) Then
    Me.YourImage.Height = 0
    Me.Detail.Height = Me.Detail.Height - mlng_image_height
Else
    Me.YourImage.Height = mlng_image_height
End If
End Sub

Private Sub Report_Load()
mlng_image_height = Me.YourImage.Height
End Sub

the idea is to "shrink" and "expand" the image control.

Thanks, I will give this a go when I'm back in the office.
 

June7

AWF VIP
Local time
Today, 08:47
Joined
Mar 9, 2014
Messages
5,423
I encountered issue of Detail height being less than mlng_image_height. I simply set Detail height to 0 and let CanGrow deal with making section large enough:
Me.YourImage.Height = 0
Me.Detail.Height = 0
 

swingline

Member
Local time
Today, 19:47
Joined
Feb 18, 2020
Messages
51
Code:
Private Sub Report_Load()
mlng_image_height = Me.YourImage.Height
End Sub
This causes an error 438 when opening the report.


Code:
If IsNull(Me.FigureLocation) Then

Should this be the table location? [Questions]![FigureLocation]
 

June7

AWF VIP
Local time
Today, 08:47
Joined
Mar 9, 2014
Messages
5,423
FigureLocation needs to be field that has image path.

YourImage needs to be name of Image control.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:47
Joined
May 7, 2009
Messages
19,169
you replace "YourImage" with the real name of your Image Control.
 

swingline

Member
Local time
Today, 19:47
Joined
Feb 18, 2020
Messages
51
FigureLocation needs to be field that has image path.

Code:
Dim mlng_image_height As Long

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Me.FigureLocation) Then
    Me.Image24.Height = 0
    Me.Detail.Height = Me.Detail.Height - mlng_image_height
Else
    Me.Image24.Height = mlng_image_height
End If
End Sub

I have FigureLocation as the control source for Image24. This is in the query that the report uses, but on on the actual report.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:47
Joined
May 7, 2009
Messages
19,169
you add FigureLocation field (of query) to your report, then on it's Property, Visible=No (not visible).
you can then use the code:

Code:
Dim mlng_image_height As Long

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Me.FigureLocation) Then
    Me.Image24.Height = 0
    Me.Detail.Height = Me.Detail.Height - mlng_image_height
Else
    Me.Image24.Height = mlng_image_height
End If
End Sub

Private Sub Report_Load()
mlng_image_height = Me.Image24.Height
End Sub
 

swingline

Member
Local time
Today, 19:47
Joined
Feb 18, 2020
Messages
51
So I added FigureLocation to the report and turned off visibility on the field no errors reported now. When I go to print preview it removes all images instead of the the ones with null in FigureLocation field. Also its not removing the white space.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:47
Joined
May 7, 2009
Messages
19,169
here is a sample, extract an run the db.
run the report in print preview.
 

Attachments

  • noPics.zip
    58.5 KB · Views: 106

swingline

Member
Local time
Today, 19:47
Joined
Feb 18, 2020
Messages
51
Ok so my problem is that I am using Columnar style vs Tabular for the report. I will just add "TOP" adjustments to both sides of the IF for Null and not null.
 
Last edited:

swingline

Member
Local time
Today, 19:47
Joined
Feb 18, 2020
Messages
51
Hmm Little bit of an issues I'm not sure how to address. Everything works until I add the Top adjustments for Me.Answer.4.Top then I get error 2100. Remove it and it moves everything as needed with the exception of answer4

Code:
Dim mlng_image_height As Long

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Me.FigureLocation) Then
    Me.Image24.Height = 0
    Me.Detail.Height = Me.Detail.Height - mlng_image_height
    Me.Answer1.Top = 1080
    Me.Answer1_Label.Top = 1080
    Me.Answer2.Top = 1620
    Me.Answer2_Label.Top = 1620
    Me.Answer3.Top = 2160
    Me.Answer3_Label.Top = 2160
    Me.Answer4.Top = 2639.952
    Me.Answer4_Label.Top = 2639.952
Else
    Me.Image24.Height = mlng_image_height
    Me.Answer1.Top = 5280.048
    Me.Answer1_Label.Top = 5280.048
    Me.Answer2.Top = 5820.048
    Me.Answer2_Label.Top = 5820.048
    Me.Answer3.Top = 6360.048
    Me.Answer3_Label.Top = 6360.048
    Me.Answer4.Top = 6840
    Me.Answer4_Label.Top = 6840
End If
End Sub

Private Sub Report_Load()
mlng_image_height = Me.Image24.Height
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:47
Joined
May 7, 2009
Messages
19,169
i don't think you need to do that.
the controls will adjust by itself.
 

swingline

Member
Local time
Today, 19:47
Joined
Feb 18, 2020
Messages
51
i don't think you need to do that.
the controls will adjust by itself.
The controls only seem to adjust when on a tabular report. When I run this on a Columnar style report the image box is resized to 0 but the rest of the controls dont shift up on the report.
 

Users who are viewing this thread

Top Bottom