Duplicate Counts
in [Getting Started]
|
From: Susan123 on 29 Jan 2010 13:15 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
From: Daryl S on 29 Jan 2010 13:33 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
From: KenSheridan via AccessMonster.com on 29 Jan 2010 18:29 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/Forums.aspx/access-gettingstarted/201001/1
|
Pages: 1 Prev: table relationships and subdatasheets Next: time and colums |