Bug in Access 2002 Reports

[notbitmonk]

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 Databas
Option Explici
Dim intDepth As Intege

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

On Error GoTo ErrorHandle

If IsNull(intDepth) The
intDepth =
End I
'Position controls at the top to prevent misplacemen
DepthTop.Top =
DepthBottom.Top =
USCS.Top =
Description.Top =
SampleSelected.Top =
GWObserved.Top =
timeHour.Top =
sngResults.Top =
sngRecovery.Top =
Detail.Height = 30
Line41.Height = 30
Line42.Height = 30

If (Nz(DepthBottom) - intDepth) > 1 The
Detail.Height = (DepthBottom - intDepth) * 30
intDepth = DepthBotto
DepthTop.Top =
DepthBottom.Top = Detail.Height - 30
USCS.Top = Detail.Height - 30
Description.Top = Detail.Height - 30
SampleSelected.Top = Detail.Height - 30
GWObserved.Top = Detail.Height - 30
timeHour.Top = Detail.Height - 30
sngResults.Top = Detail.Height - 30
sngRecovery.Top = Detail.Height - 30
Line41.Height = Detail.Heigh
Line42.Height = Detail.Heigh
End I

ExitHandler
Exit Su

ErrorHandler
MsgBox "Error: " & Err.Number & vbNewLine
& "Description: " & Err.Description, vbMsgBoxHelpButton, "Error durin
format", Err.HelpFile, Err.HelpContex

GoTo ExitHandle

End Su

 

[Stephen Lebans]

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.

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:

 

[Jesse Aviles]

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 Differenc
0 2
2 6
6 8
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 directl
in the section's Format event
Me.Section(acDetail).Height = whateve

-

HT
Stephen Leban
http://www.lebans.co
Access Code, Tips and Trick
Please respond only to the newsgroups so everyone can benefit

I have a procedure attached to the format event of the detail sectio

of a report. The code works great. I can see the report with the layou
that I want (code in the format events takes care of positioning fiel
boxes and creating a "scaled" report"). Problem is that when I send th
report to print, the printout does not has any of the format that I se
on the screen (I print it to a Lexmark X1550 and to PDF). Th
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:

 

[Stephen Lebans]

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.

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--------------------------------------------------------------|
|
|
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.

 

[Jesse Aviles]

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 locatio
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 Software: http://www.gcagint.com/images/forms/log_a_ewnn06.pdf and hopefully it will make my diatribe clearer

Thanks for any help.

 

[Stephen Lebans]

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.

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 Software: http://www.gcagint.com/images/forms/log_a_ewnn06.pdf
and hopefully it will make my diatribe clearer.

 

[Jesse Aviles]

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

 

[Stephen Lebans]

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.

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

your address at (e-mail address removed).

 

[Jesse Aviles]

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é
(e-mail address removed)

 

[Jesse Aviles]

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 Databas
Option Explici
Dim intDepth As Intege
Dim intCounter As Intege
Dim intRecordCount As Intege

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

On Error GoTo ErrorHandle

If IsNull(intDepth) The
intDepth =
End I

'Position at top to prevent misplacemen

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

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

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

intCounter = intCounter +

If intCounter = intRecordCount The
intDepth =
intCounter =
End I

Debug.Print "DepthBottom after calculation " & DepthBotto
Debug.Print "intDepth after calculation " & intDept
Debug.Print "Detail height after calculation " & Detail.Heigh
Debug.Print "intCounter = " & intCounte
Debug.Print "intDepth after intCounter if statement " & intDept
Debug.Print "--------------------------------------------------
ExitHandler
Exit Su

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

GoTo ExitHandle

End Su

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

The code of the command button on the calling form is

Private Sub cmdPreviewrptBoringLog_Click(
On Error GoTo ErrorHandle

Dim stDocName As Strin
Dim strLinkCriteri

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

ExitHandler
Exit Su

ErrorHandler
MsgBox "Error Number: " & Err.Number & vbNewLine & "Description: "
& vbCrLf & Err.Description, vbMsgBoxHelpButton, "Error in Execution", Err.HelpFile, Err.HelpContex
Resume ExitHandle

End Su

Once again thank for your time, patience and help

Jesse Avilé
(e-mail address removed)

 

[Stephen Lebans]

Glad you got it working.


--

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

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