Report Formating

[jacque427]

I have the following code in a report to "hide" blank fields.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim Ctrl As Control
For Each Ctrl In Me.Controls
If Ctrl.ControlType = acTextBox Then
If IsNull(Ctrl.Value) Then Ctrl.Visible = False Else Ctrl.Visible = True
End If
Next Ctrl

End Sub


It works wonderfully, what I am wondering is can the rest of the report move up to fill in the area left blank?

 

[dhookom]

About the only way that this can be accomplished is by setting the Can Shrink properties to Yes. Moving controls around in code is also possible but tedious.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jacque427]

Thing is, I do have can shrink to yes, but it still leaves a large blank space where there is no data. That's what has me kind of confused.

 

[ChrisCalvert]

All of this assumes that there is one control per line, but it seems like you are maybe indicating that.

What if you did something like:
Me.Height = Me.Height - Ctrl-Height
in the loop, for each thing that you are hiding. Probably this would just shorten the end and cut off things instead of moving stuff up, but you might try it.

Otherwise, you could just do this.
--------------------------------------
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim Ctrl As Control
Dim Shrink as Float
For Each Ctrl In Me.Controls
If Ctrl.ControlType = acTextBox Then
If IsNull(Ctrl.Value) Then Ctrl.Visible = False Else Ctrl.Visible = True
Shrink = Shrink + Ctrl.Height
End If
Ctrl.Top = Ctrl.Top - Shrink

Next Ctrl

End Sub
-----------------------------------------
This way, every time a text box is hidden it adds the height of that text box to the offset (called 'Shrink') and moves every subsequent box up by that much. Didn't have time to test this but the idea seems solid enough (to me, at least).

Good Luck,
Chris

 

[jacque427]

That code didn't work. And unfortunatly my ftp site is down so I can't post an example of the mdb.

 

[jacque427]

It doesn't appear to like the Dim Shrink as Float statement

 

[ChrisCalvert]

Right, sorry about that. I think I meant double.
Was working on some C++ just before answering.

This code works for one record, but I didn't think about the fact that the .tops will stay where you put them. After each record you need to find a way to put all the controls back where they were. You can store the original .tops, or just reverse the logic of the loop.

Sorry I can't complete it right now, but if you are still having trouble with it by late this evening, just post and I will try to complete it.

----------------------------------------
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim Ctrl As Control
Dim Shrink As Double
Dim NewTop As Double


For Each Ctrl In Me.Controls


If Ctrl.ControlType = acTextBox Then
If IsNull(Ctrl.Value) Then
Ctrl.Visible = False
Shrink = Shrink + Ctrl.Height
Else
Ctrl.Visible = True
End If

End If

NewTop = Ctrl.Top - Shrink
If NewTop > 0 Then Ctrl.Top = NewTop Else Ctrl.Top = 0

Next Ctrl

End Sub
---------------------------------------------

 

[jacque427]

I copied and pasted the code as written, the area does not get any smaller.

Now, I'm not *even* a programmer, but the code looks alright to me.


jacque


Just knowlegeable enough to cause problems.

 

[RandymanH]

Not sure if this will help...

I have a 5 column list style report with one record on a single detail line and to hide a record I just hide the detail section and advance to the next record with a static counter...

' DONT PRINT
If Me.Detail.Controls.Item(2).Value = -1 Then
Me.Detail.Visible = False
i = i + 1
Else
Me.Detail.Visible = True
End If

sounds like you may have a more complicated situation though.

 

[jacque427]

I'd have to agree, it is more complicated then that. It doesn't look like it'll work.

So I am going to abandon that issue and move on.

Thanks for the options though.


jacque

Just knowlegeable enough to cause problems.

 

[ghalewood]

I have a report that shows detail lines from a table where each record could be up to 3 lines long on the report. However, in normal practise, only the fields on the first line are populated. I have the Can Shrink property set for each textbox and for the Detail of the report and when printed, I don't get blank spaces on the report. As long as the field the textbox is reporting on is empty nothing is shown.
Report detail layout is like this
[tt]
TIME ORDER PARM1 PARM2 PARM3 PARM4 PARM5
PARM6 PARM7 PARM8 PARM9 PARM10
one long text box for discription

and it comes out like this:

08:00 Move North East West South
08:01 Take One Two Three Four Five Six
Seven
Take a load of stuff

[/tt]

I may be missing the point of the post, but I can't see why you would need to code to hide the blank fields, aslong as you set the can shrink property on the textbox and the report detail, everything should shuffle up automatically.

Graham

 

[dhookom]

I assume that the situation would be similar to yours but assume the "one long text..." was as wide as only PARM6 - PARM8. If either PARM1-3 were all empty or PARM6-8 were all empty then you might want the controls directly below it to move up to fill the empty space.

I generally question the table structure when this happens. For instance PARM1-PARM10 raise the red flag of normalization. I have no idea if this was the case with the original post since there was no description of the details of the problem ever revealed.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ghalewood]

Ouch, I can't even think why you would want to move textboxes around like that, controlling the positions must me a nightmare.

The orders table actually has 12 parameters in it. It is used to hold orders input for a game, the longest being along the lines of

Joe Transfer (to) Fred 100 Food 120 Elves 55 Dwarves 5 Wagons 2 Siege Engines 4 Cloud Giants

I only allow 50 orders per player and with the data being transient and deleted after processing all the orders, it is easier to hold each order as 1 record on the orders table, plus as illustrated, makes it easy to show what orders have been processed for each character.

Now if I were to tell you that I had to split a table into 2 because I had more that 255 flags/attributes for each character.... Well thats another nightmare.

 

[dhookom]

Your table is not normalized by my standards. Each order should be a record unless I am mis-understanding your situation.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ghalewood]

Aye, Each order is 1 record on the table with up to 12 parameters (or instructions if you like) for each order and 1 string field for the 'one big long message'

What I am trying to get across, is that I will always have a time and order in the example above, but depending on the order, 0 to all of the 12 parameters could have information in them, and when I print the order and associated information, I don't get spaces on the report if parm6/7/8/9/10 don't have any info to show. The original poster was setting the visible propery to false and wanted to know how to 'remove' the resulting space on the report. I believe that setting the detail can shrink property would achieve what he wanted. Not sure how we got to the position of using code to move the textboxes up on the detail piece unless it was to achieve results in the way you explained.

Perhaps there isn't enough information in the original post to provide an accurate response.

 

[jacque427]

Hmm, I have posted the db at

http://www.seesterstamps.com/db1.mdb

for ya'll to look at if you wish.

It's got me stumped.

 

[dhookom]

jacque427,
I would think this would require a lot of code or setup. If you couldn't do without this, I would name all the text boxes and labels in the subreport like txt1 - txtWhatever and lbl1 - lblWhatever. Then set the tag properties to identify a "row" in the display. You could then loop through all the controls to see if a row has any values and if not, move all the remaining rows up on the page.

Not a fun task and not generally worth the effort.

ghalewood,
12 parameters in a single record still doesn't meet my standards of normalization. If it works for you then have a go with it.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jacque427]

You know Duane, after speaking with ya'll I'd have to agree.

Thanks for the input, it has been very helpful. I'll just have to deal with a bit of white space.



Just knowlegeable enough to cause problems.

 

[ghalewood]

Hi jacque427,

You could change all your labels to textboxes and put the following code in the control source and set the 'can shrink' to true on each of them.

=IIf([text16]>"","Relationship","")

That will allow report detail to shrink and remove a lot of the white space on your report.

Ensuring that logically grouped information is on the same row should also minimize incomplete rows.

I have found though that the spaces between the rows is still 'shown' so it doesn't get rid of the white space entirely, but it is much reduced.

Alternatively, you could keep the lables and put in the data fields control source

=IIf([Zip]>"",[zip],"Data not available")

or some message other than "Data not available".

Just some thoughts.

If you want the amended database send me your mail address to GRAHAM_HALEWOOD@HOTMAIL.COM

Graham

 

[dhookom]

Graham,
Clarification about your expression:
=IIf([text16]>"","Relationship","")
This will probably not work since text16 may be Null and Null is not the same as "".

One method that I have successfully used is to (as you suggested) change the labels to text boxes and set them to can shrink. Set their control sources to something like:
="Relationship " + [RelationshipField]
Make sure the box is set to not grow and it only displays the word "Relationship". If the field value is null, then the text box will shrink to nothing.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.