You could enter code into the On Format event of the detail section that would take the value from the form and subtract 1. Then loop through every control in the detail section and multiply the result by the height of the “spacing” and add this to the Top property of the control.
If you do not want to normalize, then you can push this information into a temp table. If you then can get this into a continuous form you can use these properties to add blank lines. A combination of true and false in these properties can add lines.
Me.NextRecord
Me.PrintSection
Me.MoveLayout
I am pretty curious how they did it. Anyone have ideas? I tried a few things and can get it kind of to work, but getting the formatting correct would be hard
I put a tag on each row like G1, G2, G3
and actually moving the row.
Code:
Public Space As Integer
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Access.Control
For Each ctl In Me.Controls
If ctl.Tag <> "" Then ctl.Top = ctl.Top + Space * CInt(Right(ctl.Tag, 1) - 1) * ctl.Height
Next ctl
End Sub
Private Sub Report_Open(Cancel As Integer)
If (Me.OpenArgs & "") <> "" Then Space = CInt(Me.OpenArgs)
End Sub
This kind of worked but you would need case specific code for things like the lines and other rows that are formatted differently. If you go to a section page not sure what would happen.
Then loop through every control in the detail section and multiply the result by the height of the “spacing” and add this to the Top property of the control.
That is not quite right. You have to determine the row, if not you simply slide the whole thing down and equal amount.
If the number Spaces is 2, and Height is 10 (for simplicity).
if your ctl.Top = ctl.top + (Spaces * Height)
Then on the first "row" you add (20) second row you add(20) ... That simply moves all the rows down by 20.
I believe on first row you want to add 20, second row 40, third row 60.
There is no sure way to calculate the row. You can try to divide by the control height but a little formatting will throw everything off. I simply tagged each row.
The formula then becomes
ctl.Top = ctl.top + pseudorow * (Spaces * Height)
This gave me a pretty close solution for a single page report. I am not sure how you handle the second page. I get a error which I assume is moving a control below the length of a report.
Code:
Dim Space As Long
Dim NumberSpaces As Long
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Access.Control
Dim pseudoRow As Integer
If NumberSpaces > 0 Then
For Each ctl In Me.Detail.Controls
If ctl.Tag <> "" Then
pseudoRow = CInt(VBA.Mid(ctl.Tag, 2))
If pseudoRow > 1 Then
ctl.Top = ctl.Top + pseudoRow * (NumberSpaces * Space) - Space
End If
End If
Next ctl
End If
End Sub
Private Sub Report_Open(Cancel As Integer)
If (Me.OpenArgs & "") <> "" Then
NumberSpaces = CInt(Me.OpenArgs)
Space = 200
End If
End Sub
I still did not figure out the logic for the first row that moves. You can see that I tried to keep hemoglobin in place and move the next row. The first moved row, moves too far.
Examples for None, 1 space, 3 spaces.
Do I agree with everyone that this is putting lipstick on a pig? Yes.
I personally would go beyond normalization and instead do this in an Entity Attribute / Entity Value model. In fact this is a non-normalized solution but works when you have lots of properties / measures. This takes a little work to start, but would make life so much simpler.
With an Entity attribute model instead of 100s of fields in both main tables would have 5-10 fields. That is because you have so many measures.
That's exactly what I did with my tblParts, tblFinalProducts, and tblAssemblyComponents. Exactly that.....and it works great. I get that. Thank you for the effort of showing me a "map." Works well for me. If that is what we're all talking about then it may be conceivable to incorporate that...
www.access-programmers.co.uk
If interested in fixing this and making a useable application we can help.
The Temp Table idea is a better idea in general. That simplifies a lot of things. If the OP does not go with an EA/EV model this idea could be used extensively throughout the db not just in reports.
Yeah it is too far along to fix. I have worked on other versions previous, and yes so overly complicated. It could have been done so much simpler but much too late now. You will just have to suffer through maintaining this.
However, @arnelgp temp table idea can be used throughout this to make reporting and display much easier. You can use his idea to easily do the spacing.
When the analysis is only a blood picture, the distance between the lines can be widened or left as it is, but when there are other tests related to the same analysis, the distances can be narrowed to print them in one report. I saw it in one of the programs and I would like to do it
This really suggests to me using a temp table. Then you can group on different types of measures without creating hundreds of different reports.
You can simplify his approaches an allow this to work with a compilied (runtime or accde) application. Instead of programmatically changing the height of the detail section simply make a couple of different forms with different heights.
