Indenting a detail line

[DavidES]

Is there a way to indent a detail line in a report based upon the value of a
field in that detail? My goal is like a financial report:

1 Cost of Goods Sold
2 Hardware
3 Bolts $100.00
3 Nuts $150.00
2 Total Hardware $250.00
1 Total Cost of Goods $250.00

Thanks for any help.

 

[Marshall Barton]

Is there a way to indent a detail line in a report based upon the value of a
field in that detail? My goal is like a financial report:

1 Cost of Goods Sold
2 Hardware
3 Bolts $100.00
3 Nuts $150.00
2 Total Hardware $250.00
1 Total Cost of Goods $250.00

Well, yes, there is a way to "indent", but your example is
(or should be) using group header/footer sections. Each
section is positioned as needed with no need to adjust them.

Based on the example, I don't understand what you want to do
with the details or how a field in a record has anything to
do with it.

An example of a few records in the reports record source
might help explain how you want to transform the query's
records into the report's output.

 

[DavidES]

Thanks Marshall, I will try to explain my situation. My table lists the
following using my sample data:

ListNum Name Category Level Amount
10 Cost of Goods Sold Title 1
20 Hardware Title 2
30 Bolts 3 100.00
40 Nuts 3 150.00
50 Total Hardware Total 2 250.00
60 Total Cost of Goods Total 1 250.00

The ListNum is the order in which I wish the line to be listed.
The Level determines the indent I want for the line.

I've pretty much given up on the indent and I'm working with a series of
text controls positioned where I want them with conditional properties based
upon the level value... and it works great except that I can't find a way to
make the unused controls invisible which results in the control on top
whiting out the control under it where they overlap.

I'm looking into using the OnFormat event to create a case statement to make
the unused controls invisible. Maybe that is my solution.

 

[Marshall Barton]

Thanks Marshall, I will try to explain my situation. My table lists the
following using my sample data:

ListNum Name Category Level Amount
10 Cost of Goods Sold Title 1
20 Hardware Title 2
30 Bolts 3 100.00
40 Nuts 3 150.00
50 Total Hardware Total 2 250.00
60 Total Cost of Goods Total 1 250.00

The ListNum is the order in which I wish the line to be listed.
The Level determines the indent I want for the line.

I've pretty much given up on the indent and I'm working with a series of
text controls positioned where I want them with conditional properties based
upon the level value... and it works great except that I can't find a way to
make the unused controls invisible which results in the control on top
whiting out the control under it where they overlap.

I'm looking into using the OnFormat event to create a case statement to make
the unused controls invisible. Maybe that is my solution.

That is one wierd table. Looks more like a spreadsheet than
a database table.

You can do it by "indenting" or by making things visible.
personally, I would do the indenting:

Sub Detail_Format(
Dim indent As Long
indent = (.5 * 1440) * (Me.txtLevel -1) '1/2 inch/level
Me.txtListNum.Left = indent
Me.txtName.Left = indent
Me.txtListNum.Left = indent
Me.txtAmount.Left = indent
End Sub

 

[DavidES]

I'm willing to try your suggestion... where do I put it? and it seems to be
missing something in the first line...

Thanks for your help...

and yes, it is a weird table made from a download of a spreadsheet but the
best I could figure to do.

 

[Marshall Barton]

The first line is just a place holder that tells you where
to put the rest of the code ;-)

In other words, the code goes in the detail section's Format
event procedure. I assumed that the same place where you
were playing around with the visible stuff.

 

[DavidES]

Thanks for the help... I tested it but did not get the results I expected.
The left is blank and only the right part of the name appears and no amounts.

The first line is just a place holder that tells you where
to put the rest of the code ;-)

In other words, the code goes in the detail section's Format
event procedure. I assumed that the same place where you
were playing around with the visible stuff.
--
Marsh
MVP [MS Access]

I'm willing to try your suggestion... where do I put it? and it seems to be
missing something in the first line...


and yes, it is a weird table made from a download of a spreadsheet but the
best I could figure to do.

 

[Marshall Barton]

It sounds like some other controls are on top of the ones
we're indenting. To check fo this, you can set all of the
controls in the details section to have a different back
color.

If that doen't clarify what's happening, please post more
information including a Copy/Paste of the code in the event
procedure.

 

[DavidES]

Yes! that was the problem... we were writing on top of ourselves... The
txtAmount control was being written on top of the txtName control and in my
situation, the txtAmount was null so it looked like half of the name was cut
off.

Indent measures from the left margin for each use so I had to add inches to
the txtAmount indent to put it where I wanted it.

Here's the working code.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim indent As Long
indent = (0.25 * 1440) * (Me.txtLevel - 1) '1/4 inch/level
Me.txtName.Left = indent
Me.txtAmount.Left = indent + 4320 '4320 is 3 inch indent
End Sub

This is much easier than the code I was trying using the case method. Thanks
for showing it to me!

It sounds like some other controls are on top of the ones
we're indenting. To check fo this, you can set all of the
controls in the details section to have a different back
color.

If that doen't clarify what's happening, please post more
information including a Copy/Paste of the code in the event
procedure.
--
Marsh
MVP [MS Access]

Thanks for the help... I tested it but did not get the results I expected.
The left is blank and only the right part of the name appears and no amounts.

 

[Marshall Barton]

Good catch David.

My code made the (dumb?) assumption that the text boxes were
one above the other. Your adjustment is the kind of thing
that's needed when they are side by side.

You might find it a little more flexible if you used:

Me.txtAmount.Left = Me.txtName.Left + Me.txtName.Width

instead, just in case you should ever change the size of the
txtName text box.

 

[RobGMiller]

I have a similar issue but the text boxes that need to be indented are
located in a group header. The values in some of the text boxes are sums of a
number of records in the base query.

For some reason, the technique suggested by Marshal indents all the lines in
the resulting report regardless of the test value.

In this case, I am testing the value of a hidden field to direct weather to
indent the first text box on the left or not. A value of 7000 for instance
does not indent and the rest do.

The code I used:

Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)
Dim indent As Long

Stop

indent = (0.5 * 1440) '1/2 inch/level
If Not Me.MajorAccountNo = "7000" Then Me.Description.Left = indent

End Sub


In there a reason why a group header should not behave the same way as a
detail. If I stop at the group header On Format code, it runs 43 times,
detects the correct test value in each case and indents only when the
MajorAccountNo is not 7000. There are 25 records in the underlying query and
only 11 lines listed in the report.

The fact that it runs 43 times does not make sense to me. I would think that
the code should run only 11 times.

The grouping in based on the content of the description field which is a
concatenation of the MajorAcccountCode field and another field. This means
that each of the 11 resulting lines have a MajorAccountNo which is equal to
the MajorAccountNo in all the records that make up the group sum.


Your suggestions are appreciated

 

[John Spencer]

No where in your code to you undo the indent once you have set it.

I would expect to see something like the following

If Not Me.MajorAccountNo = "7000" Then
Me.Description.Left = indent
ELSE
Me.Description.Left = 0 'or some other value
END IF

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[RobGMiller]

Thanks for your advice John,

Removing the indent works.

I'd like to understand why but if you dont have time I am grateful for the
solution.

 

[John Spencer]

It works because the change you make is persistent for the duration of
the report. When the report is closed the "change" is lost and the next
time you open the report, the control starts over in the starting
position UNTIL it is moved.

That is why you need to move it "back" to the original position.

Think of it as
I moved this marble over an inch.
Until you move the marble back it will stay in the position you moved it to.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[RobGMiller]

Thanks for taking the time to explain that John.