Consecutive record fields in a report

[FSHOTT]

I am still trying to figure out how to compare two consecutive record fields
in a report so if they are not equal I can make a section footer for the
field visible or not visible. To hopefully better explain my problem I will
try to better explain it. I have a report of wires in a wiring harness. In
this report I have a few section footers based on whether some of the
parameter fields change (e.g. Wire Origin Location, Wire Destination
Location, Wire Type, etc.). Currently the report will put in multiple footers
(they are color boxes based on the specific field) for some of the
consecutive report field changes. What I would like to do is make some the
report footers not visible if they are not the record field changes that are
of interest. That is say the Wire Type group changes so I want a Wire Type
footer in the report but I do not want an Origin Location or Destination
Location footer so I want to make them not visible. To do this I believe I
need to know how to save and compare two consecutive record fields of say the
Origin Location when the Wire Type changes. I hope this is clear enough.
Thanks.....

 

[Clifford Bass]

Hi Frank,

It is not clear to my why, in your example, you would get an Origin
Location or Destination Location footer at all when the Wire Type changes?
Is your sorting and grouping order not the order you stated (Wire Origin
Location, Wire Destination
Location, Wire Type)? If so, what is your sorting and grouping order? Are
there times when you would want an Origin Location and/or Destination
Location footer when the Wire Type changes? If not, how about just
eliminating those group footers? If that does not help, post an example of
what your desired report look is, includinge the differing possibilities.

Clifford Bass

 

[FSHOTT]

Clifford Thank you for taking the time to respond to my question. My sorting
and grouping order is Wire Type, Harness Number (this is a wiring harness
made up of several wires of the same and differing wire types), Wire Origin
Location, Wire Destination Location, etc. Each of the sorting and groupings
has a distinctly identifiable footer in the report(color, cross hatch).
Currently when a wire Destination Location and Origin Location both change I
get both footers. For these occasions I would like to make the Destination
Footer go away (make non visible). I hope this helps clarify. To send an
example I believe I need to send an example database. I will work on creating
one.

 

[Clifford Bass]

Hi Frank,

No need for an example database. Your explanation is sufficient. I
will post back in a bit.

Clifford Bass

 

[Clifford Bass]

Hi Frank,

Based on you wording, try the following:

If you do not already have them, create headers for the Harness Number,
Wire Destination Location and Wire Origin Location. Note that I renamed the
group header and footer names from the generic "GroupHeader1", etc. to
meaningful names such as "GroupHeaderHarnessNumber". Also, I usually rename
the report's controls. So the control that holds the Harness Number might be
named "txtHarnessNumber". Add the following code, adjusting as necessary for
you actual report's fields.

==================================================

Private m_strPreviousWireDestinationLocation As String
Private m_strPreviousWireOriginLocation As String

Private Sub GroupHeaderHarnessNumber_Format(Cancel As Integer, FormatCount
As Integer)

m_strPreviousWireOriginLocation = [txtWireOriginLocation].Value
m_strPreviousWireDestinationLocation = [txtWireDestinationLocation].Value

End Sub

Private Sub GroupHeaderWireDestinationLocation_Format(Cancel As Integer,
FormatCount As Integer)

m_strPreviousWireDestinationLocation = [txtWireDestinationLocation].Value

End Sub

Private Sub GroupHeaderWireOriginLocation_Format(Cancel As Integer,
FormatCount As Integer)

GroupFooterWireDestinationLocation.Visible =
([txtWireDestinationLocation].Value = _
m_strPreviousWireDestinationLocation Or
[txtWireOriginLocation].Value = _
m_strPreviousWireOriginLocation)
m_strPreviousWireOriginLocation = [txtWireOriginLocation].Value

End Sub

==================================================

If it works right this will hide the Wire Destination Location footer
only when the Wire Type and Harness Number are the same and only when both
the Wire Destination Location and Wire Origin Location change. If that is
not quite what you need you can tweak the code as appropriate.

Clifford Bass

 

[FSHOTT]

When I put the below code in my report and try to get print preview I get a
pop up which says Microsoft Access has encountered a problem and needs to
close. It trys to repair the file and creats a backup. The subsequent reports
cannot be opened in Design View.

I am obviously doing something terribly wrong.

To execute the code below I put in an event procedure in each of the
associated GroupHeaders. Is this the correct procedure?

I also have an additional question. In the below code I am using the string
OZone & Dzone data but also have a related table which convers these to
Single numbers which I can better sort from smallest to largest. I use the
OZoneNo (Origin Zone Number) and DZoneNo in my report to sort the data. How
would I use these fields in the below code instead of the OZone / DZone
strings?



I used the Ozone and Dzone fields in the equations instead of the txtOZone
and txtDZone labels for the controls you recommended. Where should I put
these controls? Currently I use the Ozone & Dzone fields in expressions in
the detail section of the report but do not specifically have the OZone or
DZone as a control to attach the respective labels to. Should I be putting
these sprcific labeled controls in the detail section and making them not
visible?


Here are the actual File Field names in my database.
WireOriginLocation ---> Ozone (String)
WireDestinationLocation ---> Dzone (String)
WireType ---> WireSizeID (String)
HarnessNumber ---> HarnessNo (String)

CODE
Option Compare Database
Private m_strPreviousDZone As String
Private m_strPreviousOZone As String

Private Sub GroupHeaderDZone_Format(Cancel As Integer, FormatCount As Integer)
m_strPreviousDZone = [DZone].Value
End Sub

Private Sub GroupHeaderHarnessNo_Format(Cancel As Integer, FormatCount As
Integer)
m_strPreviousOZone = [OZone].Value
m_strPreviousDZone = [DZone].Value
End Sub

Private Sub GroupHeaderOZone_Format(Cancel As Integer, FormatCount As Integer)
GroupFooterDZone.Visible = ([DZone].Value = m_strPreviousDZone Or
[OZone].Value = m_strPreviousOZone)
m_strPreviousOZone = [OZone].Value
End Sub

Private Sub GroupFooterWireID_Format(Cancel As Integer, FormatCount As
Integer)
Me.GroupFooterWireID.Visible = Len(Nz(Me![Wire ID], vbNullString)) > 0

End Sub

 

[Clifford Bass]

Hi Frank,

It looks pretty good. But the crashing may be coming when you try to
hide the WireID group footer within the formatting of it. But I am not sure
on that. Try shifting the code to the WireID group header's On Format event
and get rid of the footer on format subroutine.

To use the numbers it sounds like you already have them joined into the
report's data source query. So all you really should need is to shift to
using them as the grouping items instead of the actual string values. Then
in the the code shift the stuff to use single type values instead of strings.
To help make sure you make all of the changes correctly add this line just
after Option Compare Database:

Option Explicit

This forces the declaration of all variables. So if you mistype one
somewhere, when you compile the code (Debug menu, Compile Database) it will
complain and point out the problem.

Then you can change the two module-level variables at the top:

Private m_sngPreviousDZone As Single
Private m_sngPreviousOZone As Single

Create the new numeric (i.e. OZoneNo, etc.) group header On Format
events and move the code from the string versions to numeric versions,
changing the variables and text box references as needed. Then delete the
old group header format subroutines.

Explanation on the things in my code like "txtWireOriginLocation":
They are actually the names of the text boxes into which Access puts the
values. Usually I rename the text boxes from either the generically
generated name such as Text123 or from their naming by the wizard, that uses
the field name to "txt" followed by the field name. So in code when I see
txtWireOriginLocation I know that it is a text box and that it holds the
contents of the WireOriginLocation field. As an aside check boxes I start
with "chk", buttons with "btn", labels with "lbl" and similar for other
controls. In code you can only, to my knowledge, refer to the text box that
holds a field value, not the actual field.

So, in your case, I am presuming that you used the wizard and that it
named the text boxes with the field names. In which case the code you have
is what you want (not counting the changes to using the numeric values
instead).

If you still run into Access crashing after the change or maybe even
before, try a compact and repair. Make a backup copy first--good to have
regardless. If compacting and repairing does not work, you could try the
decompile/recompile process as documented at
<http://www.granite.ab.ca/access/decompile.htm>.

Does all that make sense? Let me know if there are questions.

Clifford Bass

When I put the below code in my report and try to get print preview I get a
pop up which says Microsoft Access has encountered a problem and needs to
close. It trys to repair the file and creats a backup. The subsequent reports
cannot be opened in Design View.

I am obviously doing something terribly wrong.

To execute the code below I put in an event procedure in each of the
associated GroupHeaders. Is this the correct procedure?

I also have an additional question. In the below code I am using the string
OZone & Dzone data but also have a related table which convers these to
Single numbers which I can better sort from smallest to largest. I use the
OZoneNo (Origin Zone Number) and DZoneNo in my report to sort the data. How
would I use these fields in the below code instead of the OZone / DZone
strings?



I used the Ozone and Dzone fields in the equations instead of the txtOZone
and txtDZone labels for the controls you recommended. Where should I put
these controls? Currently I use the Ozone & Dzone fields in expressions in
the detail section of the report but do not specifically have the OZone or
DZone as a control to attach the respective labels to. Should I be putting
these sprcific labeled controls in the detail section and making them not
visible?


Here are the actual File Field names in my database.
WireOriginLocation ---> Ozone (String)
WireDestinationLocation ---> Dzone (String)
WireType ---> WireSizeID (String)
HarnessNumber ---> HarnessNo (String)

CODE
Option Compare Database
Private m_strPreviousDZone As String
Private m_strPreviousOZone As String

Private Sub GroupHeaderDZone_Format(Cancel As Integer, FormatCount As Integer)
m_strPreviousDZone = [DZone].Value
End Sub

Private Sub GroupHeaderHarnessNo_Format(Cancel As Integer, FormatCount As
Integer)
m_strPreviousOZone = [OZone].Value
m_strPreviousDZone = [DZone].Value
End Sub

Private Sub GroupHeaderOZone_Format(Cancel As Integer, FormatCount As Integer)
GroupFooterDZone.Visible = ([DZone].Value = m_strPreviousDZone Or
[OZone].Value = m_strPreviousOZone)
m_strPreviousOZone = [OZone].Value
End Sub

Private Sub GroupFooterWireID_Format(Cancel As Integer, FormatCount As
Integer)
Me.GroupFooterWireID.Visible = Len(Nz(Me![Wire ID], vbNullString)) > 0

End Sub