Bug in Access 2002 Reports

I have a procedure attached to the format event of the detail section of a report. The code works great. I can see the report with the layout that I want (code in the format events takes care of positioning field boxes and creating a "scaled" report"). Problem is that when I send the report to print, the printout does not has any of the format that I see on the screen (I print it to a Lexmark X1550 and to PDF). The formatting is lost even if I export to snapshot. If you look at the code you will see that is divided in two sections. The first section positions the fields at the top of the detail section. This is done because Access wil "remember" the last height of the section and continue using it until a larger height is provided, it doesnt matter that the code is giving instructions to make the section smaller. If the first part of the code is not included, the report will come out "half-scaled", meaning that it will make the section bigger and not smaller. Also, when the report is printed, I start getting error the the control is too big for the section but I dont see any extra space and the report will print. If I try attaching the code to print event, I just get a bunch of errors because properties are not available at Print time. Thank for any help. Here is the code:

Option Compare Database
Option Explicit
Dim intDepth As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Scales detail section to fit boring log

On Error GoTo ErrorHandler

If IsNull(intDepth) Then
intDepth = 0
End If
'Position controls at the top to prevent misplacement
DepthTop.Top = 0
DepthBottom.Top = 0
USCS.Top = 0
Description.Top = 0
SampleSelected.Top = 0
GWObserved.Top = 0
timeHour.Top = 0
sngResults.Top = 0
sngRecovery.Top = 0
Detail.Height = 300
Line41.Height = 300
Line42.Height = 300

If (Nz(DepthBottom) - intDepth) 1 Then
Detail.Height = (DepthBottom - intDepth) * 300
intDepth = DepthBottom
DepthTop.Top = 0
DepthBottom.Top = Detail.Height - 300
USCS.Top = Detail.Height - 300
Description.Top = Detail.Height - 300
SampleSelected.Top = Detail.Height - 300
GWObserved.Top = Detail.Height - 300
timeHour.Top = Detail.Height - 300
sngResults.Top = Detail.Height - 300
sngRecovery.Top = Detail.Height - 300
Line41.Height = Detail.Height
Line42.Height = Detail.Height
End If

ExitHandler:
Exit Sub

ErrorHandler:
MsgBox "Error: " & Err.Number & vbNewLine _
& "Description: " & Err.Description, vbMsgBoxHelpButton, "Error during
format", Err.HelpFile, Err.HelpContext

GoTo ExitHandler

End Sub

--
Jesse Avilés

Without even trying to figure out what you are trying to accomplish,
with A2K or higher you can set the Height of the Detail section directly
in the section's Format event.
Me.Section(acDetail).Height = whatever

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


"notbitmonk" wrote in message
...
I have a procedure attached to the format event of the detail section
of a report. The code works great. I can see the report with the layout
that I want (code in the format events takes care of positioning field
boxes and creating a "scaled" report"). Problem is that when I send the
report to print, the printout does not has any of the format that I see
on the screen (I print it to a Lexmark X1550 and to PDF). The
formatting is lost even if I export to snapshot. If you look at the
code you will see that is divided in two sections. The first section
positions the fields at the top of the detail section. This is done
because Access wil "remember" the last height of the section and
continue using it until a larger height is provided, it doesnt matter
that the code is giving instructions to make the section smaller. If
the first part of the code is not included, the report will come out
"half-scaled", meaning that it will make the section bigger and not
smaller. Also, when the report is printed, I start getting error the
the control is too big for the section but I dont see any extra space
and the report will print. If I try attaching the code to print event,
I just get a bunch of errors because properties are not available at
Print time. Thank for any help. Here is the code:

Option Compare Database
Option Explicit
Dim intDepth As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Scales detail section to fit boring log

On Error GoTo ErrorHandler

If IsNull(intDepth) Then
intDepth = 0
End If
'Position controls at the top to prevent misplacement
DepthTop.Top = 0
DepthBottom.Top = 0
USCS.Top = 0
Description.Top = 0
SampleSelected.Top = 0
GWObserved.Top = 0
timeHour.Top = 0
sngResults.Top = 0
sngRecovery.Top = 0
Detail.Height = 300
Line41.Height = 300
Line42.Height = 300

If (Nz(DepthBottom) - intDepth) 1 Then
Detail.Height = (DepthBottom - intDepth) * 300
intDepth = DepthBottom
DepthTop.Top = 0
DepthBottom.Top = Detail.Height - 300
USCS.Top = Detail.Height - 300
Description.Top = Detail.Height - 300
SampleSelected.Top = Detail.Height - 300
GWObserved.Top = Detail.Height - 300
timeHour.Top = Detail.Height - 300
sngResults.Top = Detail.Height - 300
sngRecovery.Top = Detail.Height - 300
Line41.Height = Detail.Height
Line42.Height = Detail.Height
End If

ExitHandler:
Exit Sub

ErrorHandler:
MsgBox "Error: " & Err.Number & vbNewLine _
& "Description: " & Err.Description, vbMsgBoxHelpButton, "Error
during
format", Err.HelpFile, Err.HelpContext

GoTo ExitHandler

End Sub

--
Jesse Avilés

Mr. Lebans:

I have two fields that contain a number that specifies a depth. I want two have a report that makes the detail section taller or smaller depending on the difference between these two fields. This will give the report a feeling of being "to scale", when you look at it you can see there are smaller, larger or equal intervals. For example:
Top Bottom Difference
0 2 2
2 6 4
6 8 2
Looking at the above, you can see that two interval are equal and one interval is larger. I want this difference to be observed visually ally in the report, as follows:
Top Bottom ------------------------------------------------------|
0 |
|
2 ------------------------------------------------------------|
2 |
|
|
|
|
6--------------------------------------------------------------|
6 |
|
8--------------------------------------------------------------|

When you look at the code, you will see an equation that will take care of performing the substraction to determine the difference between the two fields. The result of the substraction is the number that is then provided to the Detail.Height property to set the Height of the detail section as every record is formatted. First problem comes when only the lower part of the code is used (enclosed in an If...Then...Else statement). Access will format the section but it will remember the last placement of the controls. This behavior makes the section taller and not smaller (it doesnt matter that the code is instructing Access to make the section smaller). When Access prints the report, the print out comes the same way as on screen but the behaviour (placement of controls) is faulty. That is why the first half of the code is important. It places the controls at the top of the section every time the section is formatted forcing Access to use the behavior intended in the code. Access complies and formats the section as I intend making the display look "to scale". Problem is the print out will not show the controls placed as in the screen. I hope this makes the problem clearer. Thank you.


----- Stephen Lebans wrote: -----

Without even trying to figure out what you are trying to accomplish,
with A2K or higher you can set the Height of the Detail section directly
in the section's Format event.
Me.Section(acDetail).Height = whatever

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


"notbitmonk" wrote in message
...
I have a procedure attached to the format event of the detail section
of a report. The code works great. I can see the report with the layout
that I want (code in the format events takes care of positioning field
boxes and creating a "scaled" report"). Problem is that when I send the
report to print, the printout does not has any of the format that I see
on the screen (I print it to a Lexmark X1550 and to PDF). The
formatting is lost even if I export to snapshot. If you look at the
code you will see that is divided in two sections. The first section
positions the fields at the top of the detail section. This is done
because Access wil "remember" the last height of the section and
continue using it until a larger height is provided, it doesnt matter
that the code is giving instructions to make the section smaller. If
the first part of the code is not included, the report will come out
"half-scaled", meaning that it will make the section bigger and not
smaller. Also, when the report is printed, I start getting error the
the control is too big for the section but I dont see any extra space
and the report will print. If I try attaching the code to print event,
I just get a bunch of errors because properties are not available at
Print time. Thank for any help. Here is the code:
Option Compare Database
Option Explicit
Dim intDepth As Integer
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Scales detail section to fit boring log
On Error GoTo ErrorHandler
If IsNull(intDepth) Then
intDepth = 0
End If
'Position controls at the top to prevent misplacement
DepthTop.Top = 0
DepthBottom.Top = 0
USCS.Top = 0
Description.Top = 0
SampleSelected.Top = 0
GWObserved.Top = 0
timeHour.Top = 0
sngResults.Top = 0
sngRecovery.Top = 0
Detail.Height = 300
Line41.Height = 300
Line42.Height = 300
If (Nz(DepthBottom) - intDepth) 1 Then
Detail.Height = (DepthBottom - intDepth) * 300
intDepth = DepthBottom
DepthTop.Top = 0
DepthBottom.Top = Detail.Height - 300
USCS.Top = Detail.Height - 300
Description.Top = Detail.Height - 300
SampleSelected.Top = Detail.Height - 300
GWObserved.Top = Detail.Height - 300
timeHour.Top = Detail.Height - 300
sngResults.Top = Detail.Height - 300
sngRecovery.Top = Detail.Height - 300
Line41.Height = Detail.Height
Line42.Height = Detail.Height
End If
ExitHandler:
Exit Sub
ErrorHandler:
MsgBox "Error: " & Err.Number & vbNewLine _
& "Description: " & Err.Description, vbMsgBoxHelpButton, "Error
during
format", Err.HelpFile, Err.HelpContext
GoTo ExitHandler
End Sub
--
Jesse Avilés

Sorry Jesse, your explanation is not sinking in!
:-(
You must change your code so that you modify each section's height, and
its controls contained within, for every single possibility...period.

Also I have had problems, from within the Detail Section format event
doing:
Me.Height

I always use:
Me.Section(acDetail).Height



--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


"Jesse Aviles" wrote in message
...
Mr. Lebans:

I have two fields that contain a number that specifies a depth. I
want two have a report that makes the detail section taller or smaller
depending on the difference between these two fields. This will give
the report a feeling of being "to scale", when you look at it you can
see there are smaller, larger or equal intervals. For example:
Top Bottom Difference
0 2 2
2 6 4
6 8 2
Looking at the above, you can see that two interval are equal and one
interval is larger. I want this difference to be observed visually ally
in the report, as follows:
Top
Bottom ------------------------------------------------------|
0
|

|








2 ------------------------------------------------------------|
2
|

|

|

|

|

6--------------------------------------------------------------|
6
|

|

8--------------------------------------------------------------|

When you look at the code, you will see an equation that will take
care of performing the substraction to determine the difference between
the two fields. The result of the substraction is the number that is
then provided to the Detail.Height property to set the Height of the
detail section as every record is formatted. First problem comes when
only the lower part of the code is used (enclosed in an If...Then...Else
statement). Access will format the section but it will remember the
last placement of the controls. This behavior makes the section taller
and not smaller (it doesnt matter that the code is instructing Access to
make the section smaller). When Access prints the report, the print out
comes the same way as on screen but the behaviour (placement of
controls) is faulty. That is why the first half of the code is
important. It places the controls at the top of the section every time
the section is formatted forcing Access to use the behavior intended in
the code. Access complies and formats the section as I intend making
the display look "to scale". Problem is the print out will not show the
controls placed as in the screen. I hope this makes the problem
clearer. Thank you.


----- Stephen Lebans wrote: -----

Without even trying to figure out what you are trying to
accomplish,
with A2K or higher you can set the Height of the Detail section
directly
in the section's Format event.
Me.Section(acDetail).Height = whatever

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


"notbitmonk" wrote in
message
...
I have a procedure attached to the format event of the detail
section
of a report. The code works great. I can see the report with the
layout
that I want (code in the format events takes care of positioning
field
boxes and creating a "scaled" report"). Problem is that when I
send the
report to print, the printout does not has any of the format that
I see
on the screen (I print it to a Lexmark X1550 and to PDF). The
formatting is lost even if I export to snapshot. If you look at
the
code you will see that is divided in two sections. The first
section
positions the fields at the top of the detail section. This is
done
because Access wil "remember" the last height of the section and
continue using it until a larger height is provided, it doesnt
matter
that the code is giving instructions to make the section smaller.
If
the first part of the code is not included, the report will come
out
"half-scaled", meaning that it will make the section bigger and
not
smaller. Also, when the report is printed, I start getting error
the
the control is too big for the section but I dont see any extra
space
and the report will print. If I try attaching the code to print
event,
I just get a bunch of errors because properties are not available
at
Print time. Thank for any help. Here is the code:
Option Compare Database
Option Explicit
Dim intDepth As Integer
Private Sub Detail_Format(Cancel As Integer, FormatCount As
Integer)
'Scales detail section to fit boring log
On Error GoTo ErrorHandler
If IsNull(intDepth) Then
intDepth = 0
End If
'Position controls at the top to prevent misplacement
DepthTop.Top = 0
DepthBottom.Top = 0
USCS.Top = 0
Description.Top = 0
SampleSelected.Top = 0
GWObserved.Top = 0
timeHour.Top = 0
sngResults.Top = 0
sngRecovery.Top = 0
Detail.Height = 300
Line41.Height = 300
Line42.Height = 300
If (Nz(DepthBottom) - intDepth) 1 Then
Detail.Height = (DepthBottom - intDepth) * 300
intDepth = DepthBottom
DepthTop.Top = 0
DepthBottom.Top = Detail.Height - 300
USCS.Top = Detail.Height - 300
Description.Top = Detail.Height - 300
SampleSelected.Top = Detail.Height - 300
GWObserved.Top = Detail.Height - 300
timeHour.Top = Detail.Height - 300
sngResults.Top = Detail.Height - 300
sngRecovery.Top = Detail.Height - 300
Line41.Height = Detail.Height
Line42.Height = Detail.Height
End If
ExitHandler:
Exit Sub
ErrorHandler:
MsgBox "Error: " & Err.Number & vbNewLine _
& "Description: " & Err.Description, vbMsgBoxHelpButton, "Error
during
format", Err.HelpFile, Err.HelpContext
GoTo ExitHandler
End Sub
--
Jesse AvilÃf©s

Thank you Mr. Lebans. I have no trouble with the code. The code works as I intend it to. Its function is to create an illusion, using white space and control positioning, of depths sampled during subsoil drilling operations. On screen I see the result intended. Where does my problem lies? In printing the report. When I print the report all formatting is lost. It prints like an spreadsheet and not in the "to scale" format observed in the screen. I haven't been able to print it to pdf or snapshot in the "scale" version either. I don't know what is causing the problem. The OnFormat event lays out the controls in their place before printing takes place but why is the formatting is lost when the report is actually printed? I have also observed that when the report is printed Error 2100 is raised during the print event. The description provided says the the control or subform control is too large for this location. There is no subform and the screen version of the report does not shows any control out of place.

The following is for your understanding of what I'm trying to do.

Controls are located based on the difference of sampling depths. I can see the report on the screen and, without reading it, observe that depths sampled were constant or not. What happens in the field is as follows:
The driller places the rig at the sample location
A sampling rod is placed in the ground and the driller drives the rod two feet in the soil. The sampled interval is 0-2.
The driller retrieves the sample and drives the sampling rod four feet deeper than the previous depth. The sampling rod is now at 6 feet below surface. The sampled interval was from 2 feet below surface to 6 feet below surface (2-6).

You can see that the sampled intervals are different (2 and 4). If I were to look at the boring log (this is the name of what I'm trying to do) and it was prepared as a spreadsheet (every row the same height as the previous), I will have to read the boring log to see differences or similarities. However, if I could make it so that each row will adjust its height proportionately to the depth driven, by looking at the boring log, I could make some assumptions. Take a look at the following pdf from gINT Softwa http://www.gcagint.com/images/forms/log_a_ewnn06.pdf and hopefully it will make my diatribe clearer.

Thanks for any help.

Can you send me the MDB containing the sample Report?

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


"Jesse Aviles" wrote in message
...
Thank you Mr. Lebans. I have no trouble with the code. The code
works as I intend it to. Its function is to create an illusion, using
white space and control positioning, of depths sampled during subsoil
drilling operations. On screen I see the result intended. Where does
my problem lies? In printing the report. When I print the report all
formatting is lost. It prints like an spreadsheet and not in the "to
scale" format observed in the screen. I haven't been able to print it
to pdf or snapshot in the "scale" version either. I don't know what is
causing the problem. The OnFormat event lays out the controls in their
place before printing takes place but why is the formatting is lost when
the report is actually printed? I have also observed that when the
report is printed Error 2100 is raised during the print event. The
description provided says the the control or subform control is too
large for this location. There is no subform and the screen version of
the report does not shows any control out of place.

The following is for your understanding of what I'm trying to do.

Controls are located based on the difference of sampling depths. I
can see the report on the screen and, without reading it, observe that
depths sampled were constant or not. What happens in the field is as
follows:
The driller places the rig at the sample location
A sampling rod is placed in the ground and the driller drives the
rod two feet in the soil. The sampled interval is 0-2.
The driller retrieves the sample and drives the sampling rod four
feet deeper than the previous depth. The sampling rod is now at 6 feet
below surface. The sampled interval was from 2 feet below surface to 6
feet below surface (2-6).

You can see that the sampled intervals are different (2 and 4). If I
were to look at the boring log (this is the name of what I'm trying to
do) and it was prepared as a spreadsheet (every row the same height as
the previous), I will have to read the boring log to see differences or
similarities. However, if I could make it so that each row will adjust
its height proportionately to the depth driven, by looking at the boring
log, I could make some assumptions. Take a look at the following pdf
from gINT Softwa http://www.gcagint.com/images/forms/log_a_ewnn06.pdf
and hopefully it will make my diatribe clearer.

Thanks for any help.

Gladly! But I cant find an e-mail in your web page. You can mail me your address at .

Did you even try to step through your code to see where the problem is?

When DepthBottom is less than intDepth the result of the subtraction
will give you a negative value that you multiply by 300 to generate a
height for the Detail section. You simply cannot set the Height of the
Detail section to a negative value!

When I offer to help I assume that you have already tried to debug your
issue. I don't believe that was the case here.
--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


"Jesse Aviles" wrote in message
...
Gladly! But I cant find an e-mail in your web page. You can mail me
your address at .

Mr. Lebans:
I had done so. The DepthBottom value was always higher than intDepth. The code that you beheld was stripped of the different debug.print statements placed to observe the value of the differents variables involved. The value was never negative. However, since you mentioned the debugging, I have to concede that I never checked the debugging results during printing operations. I just did so and found that what you say is correct. During printing and after previewing the report, the OnFormat event is fired again (some new information for me) and since intDepth is a global variable, its value is never reset causing the Detail.Height property to contain a negative value which just as you said can not be. I changed the command button in the form to print the report instead of previewing it and had no trouble whatsoever. However this is an impractical solution since the user needs to change the report from within Access and not change the default printer from within the Control Panel. I will work with a For...To statement (a new one to me) to count the number of records in the detail section and reset the global variable intDepth once the count equals the number of records. Thank you for your time and patience and the tip.

Jesse Avilés

Mr. Lebans:
The report is printing correctly from the preview. I did not use the For..Next statement relying instead of counters and resetting the global variables depending on the counters values. The code on the calling form includes an Open Argument when it calls forth the report since I wasn't able to use the filter property of the report. Following is the final code I used.

Option Compare Database
Option Explicit
Dim intDepth As Integer
Dim intCounter As Integer
Dim intRecordCount As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Scales detail section to fit boring log

On Error GoTo ErrorHandler

If IsNull(intDepth) Then
intDepth = 0
End If

'Position at top to prevent misplacement

DepthTop.Top = 0
DepthBottom.Top = 0
USCS.Top = 0
Description.Top = 0
SampleSelected.Top = 0
GWObserved.Top = 0
timeHour.Top = 0
sngResults.Top = 0
sngRecovery.Top = 0
Detail.Height = 300
Line41.Height = 300
Line42.Height = 300

'Calculate space and set controls in their position
Debug.Print "DepthBottom before calculation " & DepthBottom
Debug.Print "intDepth before calculation " & intDepth

Detail.Height = (DepthBottom - intDepth) * 300
Debug.Print "Calculation Value " & (DepthBottom - intDepth)
intDepth = DepthBottom
DepthBottom.Top = Detail.Height - 300
USCS.Top = Detail.Height - 300
Description.Top = Detail.Height - 300
SampleSelected.Top = Detail.Height - 300
GWObserved.Top = Detail.Height - 300
timeHour.Top = Detail.Height - 300
sngResults.Top = Detail.Height - 300
sngRecovery.Top = Detail.Height - 300
Line41.Height = Detail.Height
Line42.Height = Detail.Height

intCounter = intCounter + 1

If intCounter = intRecordCount Then
intDepth = 0
intCounter = 0
End If

Debug.Print "DepthBottom after calculation " & DepthBottom
Debug.Print "intDepth after calculation " & intDepth
Debug.Print "Detail height after calculation " & Detail.Height
Debug.Print "intCounter = " & intCounter
Debug.Print "intDepth after intCounter if statement " & intDepth
Debug.Print "--------------------------------------------------"
ExitHandler:
Exit Sub

ErrorHandler:
MsgBox "Error: " & Err.Number & vbNewLine _
& "Description: " & Err.Description, vbMsgBoxHelpButton, "Error during format", Err.HelpFile, Err.HelpContext

GoTo ExitHandler

End Sub

Private Sub Report_Open(Cancel As Integer)
intRecordCount = DCount("DepthTop", "qryFullBoringLog", Me.OpenArgs)
Debug.Print "intRecordCount = " & intRecordCount
End Sub


The code of the command button on the calling form is:

Private Sub cmdPreviewrptBoringLog_Click()
On Error GoTo ErrorHandler

Dim stDocName As String
Dim strLinkCriteria

stDocName = "rptBoringLog"
strLinkCriteria = "[BoringInfoID] = " & Me!BoringInfoID
DoCmd.OpenReport stDocName, acPreview, , strLinkCriteria, , strLinkCriteria

ExitHandler:
Exit Sub

ErrorHandler:
MsgBox "Error Number: " & Err.Number & vbNewLine & "Description: " _
& vbCrLf & Err.Description, vbMsgBoxHelpButton, "Error in Execution", Err.HelpFile, Err.HelpContext
Resume ExitHandler

End Sub

Once again thank for your time, patience and help.

Jesse Avilés