Duplicate Counts

Hi,
In searching for answers to duplicates, I found a post from 2008 (please see
below). It was exactly what I was looking for. I followed the instructions
but I get a yellow highlight on Me.txtOrderCount = intIDCount when I try
running the code. I have very little knowledge of code and not sure what I
am doing wrong. Can someone please guide me so I can get this working? I
truly appreciate any help given.


Duane's solution should work with your daily report, but if you are first
grouping the weekly report by days then the same OrderID could appear in more
than one group header if picked and loaded on separate days as this would be
the second group level, so it would again be counted twice.

A solution would be to count the distinct OrderID values in code in the
report's module. First you'd declare two module level variables, one to hold
a value list of the distinct OrderIDs, one to hold the count. In the detail
section's Print event procedure the OrderID would be added to the value list
and the count incremented each time a new OrderID is encountered. Finally
the value of the count would be assigned to an unbound text box in the report
footer. So the report's module would look something like this:

Option Compare Database
Option Explicit

Dim strIDList As String
Dim intIDCount As Integer

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

If PrintCount = 1 Then
If InStr(strIDList, "~" & Me.OrderID) = 0 Then
strIDList = strIDList & "~" & Me.OrderID
intIDCount = intIDCount + 1
End If
End If

End Sub

Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)

Me.txtOrderCount = intIDCount

End Sub

Note that I've used the tilde character as the delimiter for the value list
rather than the more usual comma, colon or semi-colon. This is just in case
you are using a structured OrderID (or equivalent) which might contain one of
those characters. I'm assuming it won't contain a tilde! If it’s a simple
number such as an autonumber it will still work of course.

Ken Sheridan
Stafford, England
--
Sue

Sue -

Are you getting an error message? If there is no error message, and you are
just getting the yellow highlight, then it probably just a ghost breakpoint.
You should be able to get rid of it by re-compiling the code (and maybe close
and re-open Access).

If there is an error message, then please let us know what it says so we can
help.
--
Daryl S


"Susan123" wrote:

Hi,
In searching for answers to duplicates, I found a post from 2008 (please see
below). It was exactly what I was looking for. I followed the instructions
but I get a yellow highlight on Me.txtOrderCount = intIDCount when I try
running the code. I have very little knowledge of code and not sure what I
am doing wrong. Can someone please guide me so I can get this working? I
truly appreciate any help given.


Duane's solution should work with your daily report, but if you are first
grouping the weekly report by days then the same OrderID could appear in more
than one group header if picked and loaded on separate days as this would be
the second group level, so it would again be counted twice.

A solution would be to count the distinct OrderID values in code in the
report's module. First you'd declare two module level variables, one to hold
a value list of the distinct OrderIDs, one to hold the count. In the detail
section's Print event procedure the OrderID would be added to the value list
and the count incremented each time a new OrderID is encountered. Finally
the value of the count would be assigned to an unbound text box in the report
footer. So the report's module would look something like this:

Option Compare Database
Option Explicit

Dim strIDList As String
Dim intIDCount As Integer

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

If PrintCount = 1 Then
If InStr(strIDList, "~" & Me.OrderID) = 0 Then
strIDList = strIDList & "~" & Me.OrderID
intIDCount = intIDCount + 1
End If
End If

End Sub

Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)

Me.txtOrderCount = intIDCount

End Sub

Note that I've used the tilde character as the delimiter for the value list
rather than the more usual comma, colon or semi-colon. This is just in case
you are using a structured OrderID (or equivalent) which might contain one of
those characters. I'm assuming it won't contain a tilde! If it’s a simple
number such as an autonumber it will still work of course.

Ken Sheridan
Stafford, England
--
Sue

Sue:

One of my old posts coming back to haunt me!

Just to be absolutely clear, the lines:

Dim strIDList As String
Dim intIDCount As Integer

go in the 'declarations' area of the report's module. This makes the
variable available throughout the module.

The lines:

If PrintCount = 1 Then
If InStr(strIDList, "~" & Me.OrderID) = 0 Then
strIDList = strIDList & "~" & Me.OrderID
intIDCount = intIDCount + 1
End If
End If

go in the Print event procedure of the detail section.

And the line:

Me.txtOrderCount = intIDCount

goes in the Print event procedure of the report footer.

Is it possible that you either haven't added the txtOrderCount text box to
the footer, or have added a text box to the footer with a different name to
that used in the code?

Ken Sheridan
Stafford, England

Susan123 wrote:
Hi,
In searching for answers to duplicates, I found a post from 2008 (please see
below). It was exactly what I was looking for. I followed the instructions
but I get a yellow highlight on Me.txtOrderCount = intIDCount when I try
running the code. I have very little knowledge of code and not sure what I
am doing wrong. Can someone please guide me so I can get this working? I
truly appreciate any help given.

Duane's solution should work with your daily report, but if you are first
grouping the weekly report by days then the same OrderID could appear in more
than one group header if picked and loaded on separate days as this would be
[quoted text clipped - 39 lines]
Ken Sheridan
Stafford, England

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201001/1