Trying to control when a line is displayed in a report.

[Nigel]

I have a field in a report called FlightNo. Hide Duplicates=Yes. Thus if I
have four records with the same FlightNo the FlightNo field is only displayed
beside the first.

I want a horizontal line to appear above each occurance of FlightNo. I've
drawn it in the report. Name = FlightSeparatorLine. Visible = Yes. I've
added the following code to the Detail section but it doesn't seem to work.
The line is still appearing between every record. What am I doing wrong?

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

Start:

On Error GoTo FlightLineError:
If Me![FlightNo].IsVisible = True Then
Me![FlightSeparatorLine].Visible = True
Else
Me![FlightSeparatorLine].Visible = False
End If
GoTo Finishup:

FlightLineError:
Me![FlightSeparatorLine.Visible] = False
MsgBox ("There was an error")
Finishup:

End Sub

 

[Marshall Barton]

I have a field in a report called FlightNo. Hide Duplicates=Yes. Thus if I
have four records with the same FlightNo the FlightNo field is only displayed
beside the first.

I want a horizontal line to appear above each occurance of FlightNo. I've
drawn it in the report. Name = FlightSeparatorLine. Visible = Yes. I've
added the following code to the Detail section but it doesn't seem to work.
The line is still appearing between every record. What am I doing wrong?

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

Start:

On Error GoTo FlightLineError:
If Me![FlightNo].IsVisible = True Then
Me![FlightSeparatorLine].Visible = True
Else
Me![FlightSeparatorLine].Visible = False
End If
GoTo Finishup:

FlightLineError:
Me![FlightSeparatorLine.Visible] = False
MsgBox ("There was an error")
Finishup:

End Sub

As long as the text box control that is bound to the
FlightNo field is also named FlightNo, I would expect your
code to work. Double check the text box name and make sure
that is what you are using in your code (the name of the
field in the record source table/query is not relevant
here).

Can't see how it would not be the case, but the line control
must also be in the detail section.

 

[Nigel]

Hi Marshall,

Agreed. To avoid confusion with the control source I renamed the text box
to rptFlightNo. But I've discovered my code is not running at all. I don't
know why. I replaced all the code with MsgBox ("Hello") ... and even that
wouldn't run.

Any ideas?

Nige'

I have a field in a report called FlightNo. Hide Duplicates=Yes. Thus if I
have four records with the same FlightNo the FlightNo field is only displayed
beside the first.

I want a horizontal line to appear above each occurance of FlightNo. I've
drawn it in the report. Name = FlightSeparatorLine. Visible = Yes. I've
added the following code to the Detail section but it doesn't seem to work.
The line is still appearing between every record. What am I doing wrong?

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

Start:

On Error GoTo FlightLineError:
If Me![FlightNo].IsVisible = True Then
Me![FlightSeparatorLine].Visible = True
Else
Me![FlightSeparatorLine].Visible = False
End If
GoTo Finishup:

FlightLineError:
Me![FlightSeparatorLine.Visible] = False
MsgBox ("There was an error")
Finishup:

End Sub

As long as the text box control that is bound to the
FlightNo field is also named FlightNo, I would expect your
code to work. Double check the text box name and make sure
that is what you are using in your code (the name of the
field in the record source table/query is not relevant
here).

Can't see how it would not be the case, but the line control
must also be in the detail section.

 

[Nigel]

Oops! ... forget my last message ... I was opening the report instead of
previewing it !!! Doh!!!

Thanks for your help.

Nige'

Hi Marshall,

Agreed. To avoid confusion with the control source I renamed the text box
to rptFlightNo. But I've discovered my code is not running at all. I don't
know why. I replaced all the code with MsgBox ("Hello") ... and even that
wouldn't run.

Any ideas?

Nige'

I have a field in a report called FlightNo. Hide Duplicates=Yes. Thus if I
have four records with the same FlightNo the FlightNo field is only displayed
beside the first.

I want a horizontal line to appear above each occurance of FlightNo. I've
drawn it in the report. Name = FlightSeparatorLine. Visible = Yes. I've
added the following code to the Detail section but it doesn't seem to work.
The line is still appearing between every record. What am I doing wrong?

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

Start:

On Error GoTo FlightLineError:
If Me![FlightNo].IsVisible = True Then
Me![FlightSeparatorLine].Visible = True
Else
Me![FlightSeparatorLine].Visible = False
End If
GoTo Finishup:

FlightLineError:
Me![FlightSeparatorLine.Visible] = False
MsgBox ("There was an error")
Finishup:

End Sub

As long as the text box control that is bound to the
FlightNo field is also named FlightNo, I would expect your
code to work. Double check the text box name and make sure
that is what you are using in your code (the name of the
field in the record source table/query is not relevant
here).

Can't see how it would not be the case, but the line control
must also be in the detail section.

 

[Nigel]

Hi Marshall,

It's working ... but not properly. The lines are appearing in the wrong
place. Here's what I want it to do (flight numbers in brackets are actually
hidden):

Flight No From To
--------------------------------------
1 Here There
(1) There Nowhere
-------------------------------------------
2 Nowhere There
(2) There Here
---------------------------------------
3 Here There
----------------------------------------
4 There Here
---------------------------------------

And here is what it is doing!!!

Flight No From To
--------------------------------------
1 Here There
--------------------------------------
(1) There Nowhere
2 Nowhere There
-------------------------------------------
(2) There Here
---------------------------------------
3 Here There
----------------------------------------
4 There Here
---------------------------------------

So it's not quite syncronising with the Hide Duplicates setting. What am I
doing wrong?

Cheers

Nige'

I have a field in a report called FlightNo. Hide Duplicates=Yes. Thus if I
have four records with the same FlightNo the FlightNo field is only displayed
beside the first.

I want a horizontal line to appear above each occurance of FlightNo. I've
drawn it in the report. Name = FlightSeparatorLine. Visible = Yes. I've
added the following code to the Detail section but it doesn't seem to work.
The line is still appearing between every record. What am I doing wrong?

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

Start:

On Error GoTo FlightLineError:
If Me![FlightNo].IsVisible = True Then
Me![FlightSeparatorLine].Visible = True
Else
Me![FlightSeparatorLine].Visible = False
End If
GoTo Finishup:

FlightLineError:
Me![FlightSeparatorLine.Visible] = False
MsgBox ("There was an error")
Finishup:

End Sub

As long as the text box control that is bound to the
FlightNo field is also named FlightNo, I would expect your
code to work. Double check the text box name and make sure
that is what you are using in your code (the name of the
field in the record source table/query is not relevant
here).

Can't see how it would not be the case, but the line control
must also be in the detail section.

 

[Marshall Barton]

It's working ... but not properly. The lines are appearing in the wrong
place. Here's what I want it to do (flight numbers in brackets are actually
hidden):

Flight No From To
--------------------------------------
1 Here There
(1) There Nowhere
-------------------------------------------
2 Nowhere There
(2) There Here
---------------------------------------
3 Here There
----------------------------------------
4 There Here
---------------------------------------

And here is what it is doing!!!

Flight No From To

I can't explain what you said it is doing. Flights 1 and 2
look like the line is at the bottom of the detail section.
But 3 and 4 contradict that.

All I can guess at is maybe there is a group header amd/or
footer sections that have other lines.

 

[Nigel]

Hi Marshall,

That's what I thought too ... so I deleted the one line from the Detail
section and then there were NO lines in the report. I created a new line in
the Detail section ... right through the middle of the text boxes ... and it
still made the same error as before.

I'm out of ideas on this one.

Cheers

Nige'

 

[Marshall Barton]

An entirely different way to go at putting lines above the
first record for each flight number is to scrap the
HideDuplicates approach and all its code.

Instead, use Sorting and Grouping (View menu) to add a group
level with header on the flight number field. Move the
flight number text box and the line from the detail section
to this new group header section. Then add a line of code
to the group header's Format event:
Me.MoveLayout = False

 

[Nigel]

Ahh ... this I shall try!

Thanks

Nige'

An entirely different way to go at putting lines above the
first record for each flight number is to scrap the
HideDuplicates approach and all its code.

Instead, use Sorting and Grouping (View menu) to add a group
level with header on the flight number field. Move the
flight number text box and the line from the detail section
to this new group header section. Then add a line of code
to the group header's Format event:
Me.MoveLayout = False
--
Marsh
MVP [MS Access]

That's what I thought too ... so I deleted the one line from the Detail
section and then there were NO lines in the report. I created a new line in
the Detail section ... right through the middle of the text boxes ... and it
still made the same error as before.