Duplicate Line When Printing Report (1 Viewer)

[ErinL]

Hi everyone -

I have posted this issue on this forum in the past so I apologize for posting it again but I have not found a solution and am getting to a point that I have to roll out this database and I need to get this resolved.

My issue is when there are 4 records (store numbers) for any "row" on the load diagram, the last store's information is duplicated when the report is printed. It only appears once in the table, it only appears once in the query, but when the report prints it prints two lines. It also does not include the duplicate line in the totals at the bottom of the report. This leads me to believe that the issue is in my PrintLines function which is in the modPrintLoadDiagram but I am only a beginner when writing code and have not been able to figure it out on my own.

I am attaching a version of the original database. Please open rptLoadDiagram in Print Preview and look at row 3 on the right side. Store 5449 is showing up twice but should only be on there one time.

This is the only issue holding up my roll out of the database.

Could someone please look at what I have attached?

I would appreciate any and all assistance. Thank you in advance.

 

[GinaWhipp]

That has been a problem with that code since I can remember... sorry, I have never found a solution and judging by the fact that it is still a problem I'm willing to bet others have found alternate solutions.

 

[JHB]

What is the purpose of your code, what do you want to achieve, (if only to print 5 lines then the code doesn't work)?

 

[vbaInet]

Yes, I believe the poster wants to have fixed number of rows. I will write one later (if you don't get there before JHB) and post.

 

[vbaInet]

That has been a problem with that code since I can remember... sorry, I have never found a solution and judging by the fact that it is still a problem I'm willing to bet others have found alternate solutions.

I've never seen that particular code before! It doesn't look right.

 

[JHB]

Replace the PrintLines function with the below code, then the function works for max. 4 Store-numbers:

Function PrintLines(R As Report, TotGrp)
   TotCount = TotCount + 1
   If TotCount = TotGrp Then
        R.NextRecord = False
   ElseIf TotCount > TotGrp And TotCount < 5 Then
        R.NextRecord = False
        R![RowNumber].Visible = False
        R![Position].Visible = False
        R![Store].Visible = False
        R![GreyTotes].Visible = False
        R![PlasticPallets].Visible = False
        R![PlasticStackers].Visible = False
        R![CardboardStackers].Visible = False
        R![ChepPallets].Visible = False
        R![PecoPallets].Visible = False
        R![IGPSPallets].Visible = False
        R![WoodPallets].Visible = False
        R![SmallBlueTotes].Visible = False
        R![Displays].Visible = False
   ElseIf TotCount >= 5 Then
        R![RowNumber].Visible = False
        R![Position].Visible = False
        R![Store].Visible = False
        R![GreyTotes].Visible = False
        R![PlasticPallets].Visible = False
        R![PlasticStackers].Visible = False
        R![CardboardStackers].Visible = False
        R![ChepPallets].Visible = False
        R![PecoPallets].Visible = False
        R![IGPSPallets].Visible = False
        R![WoodPallets].Visible = False
        R![SmallBlueTotes].Visible = False
        R![Displays].Visible = False
   End If
End Function

 

[JHB]

I've never seen that particular code before! It doesn't look right.

It is public by Microsoft Support http://support.microsoft.com/kb/210350
It only works if the amount of records for each group, is lesser as the lines you want to print for each group, (personally I don't like it ).

 

[GinaWhipp]

@JHB

I agree, don't like it much either but it's used quite often.

 

[vbaInet]

It's an effective piece of code though. I've attached two ways in which it can be done. The first one is similar to Microsoft's. The reports are srptLoadDiagramRight_New and srptLoadDiagramRight_New2

 

[ErinL]

Thank you all so much for your help!!!

I replaced the original code with the code JHB posted and it works just how I want it to now! I racked my brain for so long on this :banghead: but, like I said, I'm a beginner and learning VBA code as I go so I'm nowhere near the level of expertise that you all have.

Thank you again for lifting that huge weight off my shoulders.

 

[JHB]

You're welcome, luck with it, but have a look at what vbaInet has made, it is even better, because with his solution also the last line can be used.

 

[ErinL]

Ah! I do see that! That would give them another line to use. I will try implementing that too. Thanks again!