Numbering in Report

[iggydarsa]

Hi All,

I am running a report based on a query and this is what it looks like:

No....Name....Points
1......Alice.....100
2......Bob.......100
3......Charles...90
4......David......75
5......Eric.........75

Name and Points fields are fetched from a query and I added a textbox. In textbox property for Control Source I entered =1 and for Running Sum I selected OverAll, so that way I got the No column.

What I am trying to accomplish is not showing the number in No column after the first record if the Points are the same. So the table should look like this:
No....Name....Points
1......Alice.....100
........Bob.......100
3......Charles...90
4......David......75
........Eric.........75

How can I do that?

Thanks

 

[revans]

In your detail format event try this

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Static iMyPoints As Long
If iMyPoints = Me.Points Then
  Me.No.Visible = False
Else
  Me.No.Visible = True
  iMyPoints = Me.Points
End If
End Sub

"No" is the name of the number textbox.

hth,

Rich

 

[iggydarsa]

Thanks for the response revans,

However, I still see all the numbers.

 

[revans]

I'm using 2003, if you have a newer version perhaps things have changed, though I find it hard to believe Access has changed that much

Is iMyPoints declared using Static or Dim? It should be Static.

What is the name of the textbox you have the numbers in (the one I named "No")? Did you change the code or your textbox to reflect that name?

Otherwise I'm not sure what could be going on.

Rich

 

[iggydarsa]

I am using 2007, and yes I agree I dont think things changed that much

I copy pasted your code so it remains Static.

in the properties of the textbox in details section i went to properties and changed the Name to No to reflect the change. I also change Points to Total since it is the name of the other textbox

so my code looks like this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Static iMyPoints As Long
If iMyPoints = Me.Total Then
  Me.No.Visible = False
Else
  Me.No.Visible = True
  iMyPoints = Me.Total
End If
End Sub

Thanks

 

[revans]

Hmm, I don't know what to say. Let me start from scratch.

I create a table named tblTotal

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>tblTotal</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>Name</TH><TH bgColor=#c0c0c0 borderColor=#000000>Total</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#c0c0c0>Alice</TD><TD borderColor=#c0c0c0 align=right>100</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>Bob</TD><TD borderColor=#c0c0c0 align=right>100</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>Charles</TD><TD borderColor=#c0c0c0 align=right>90</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>David</TD><TD borderColor=#c0c0c0 align=right>75</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>Eric</TD><TD borderColor=#c0c0c0 align=right>75</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

I create a report named Report2. I set the RecordSource property to tblTotal. I drag to two fields into the Detail section. I create a new textbox in the Detail section. I set the Name property No, the Control Source property to =1, and the Running Sum property to Over All. I also put the labels for the two bound fields in the page header.

In the detail section's On Format event I put the following code (I also tried the On Print event for the deail section and it works just fine but I prefer the On Format event.)

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Static iMyPoints As Long
If iMyPoints = Me.Total Then
  Me.No.Visible = False
Else
  Me.No.Visible = True
  iMyPoints = Me.Total
End If
End Sub

When I run the report it looks very similar to this (without the dots)

.......Name....Total
1......Alice.....100
........Bob.......100
3......Charles...90
4......David......75
........Eric.........75

It sounds like we're doing the same things. I cannot explain why you are not getting the same results. Could you try creating a brand new report. Maybe Access is having some kind of issue with the old report?

Rich

 

[iggydarsa]

It is interesting

I used the same code for both Format and Print events.

Format event seems like doing nothing but when I do print preview I see the report the way I want to see...

I guess that will do it for now.

Thanks for your efforts and time.