Shrink Fields Based on Record Source

[jollofrice]

Hello,

I've got a concatenated field (we'll call it [Report]) in a report that I want to shrink, allowing lower fields to move up, if a given field in my report record source (we'll call this [Source]) is null. How can I do this?

Thanks

 

[RuralGuy]

If you set the CanShrink property of the section (Detail??) and the control to YES then if there is nothing else on the line it will shrink when the ControlSource is null.

 

[jollofrice]

I don't have just one ControlSource, though. The text box is a concatenation of two query fields with some text. Is there a way to make the whole thing shrink if a third query field is null (VBA?)?

Thanks

 

[RuralGuy]

If you will be controlling the visibility of a control with the content of a separate control then you can set the visibility of the control in the Print event to force it to shrink. It the control source contains the controlling field then using '+' to concantenate rather than '&' will propagate any null field.

 

[jollofrice]

Thanks! The '+' concatenation took care of one of the controls (I've got two I was working on). Now I'm trying to use VBA to set the Visible property in the Print event of the Detail section:

If [PendingRequestsHostQuery.Name] = [PendingRequestsBenQuery.Name] Then
Text21.Visible = No
Else
Text21.Visible = yes
End If

But this sets every instance of Text21.Visible to no, instead of just the ones where that meet my If criteria. Any ideas?

 

[RuralGuy]

Try:

If IsNull[Name] Then
   Text21.Visible = False
Else
   Text21.Visible = True
End If

BTW, Name is a Reserved word in Access and should not be used as a field name.
Problem names and reserved words in Access

 

[jollofrice]

Oh. Sorry. The '+' concatenation got the null field control to shrink. This is a concatenated field I want to shrink if two source fields are equal (might be a new string).

 

[RuralGuy]

I apologize but I did not look at your posted code carefully enough. You can only have one RecordSource to the report. What two "[Name]" fields are you comparing?

 

[jollofrice]

The [name] fields are the names of a sponsoring organization and a benefiting organization (for charitable events). I have an organizations table and an events table. I have two queries, one for sponsoring org's infor related to an event, and one for the benefiting org. A final query collects all the information about the event and both organizations and forms the RecordSource for my report.

I want the report to only show the sponsoring org if it's the same as the beneficiary org.

 

[RuralGuy]

So there is a [PendingRequestsHostQuery.Name] and [PendingRequestsBenQuery.Name] field for each record. Use True and False and put a breakpoint on the If statement and hover over the fields and see what you have. Then F8 and see which branch the code takes.

If [PendingRequestsHostQuery.Name] = [PendingRequestsBenQuery.Name] Then
   Text21.Visible = False
Else
   Text21.Visible = True
End If

...you may continue to have problems until you rename those fields. Access can be funny that way.

 

[jollofrice]

That did the trick! Thanks for pointing out that I was using a reserved word too. I appreciate the help!

 

[RuralGuy]

Glad I could help.