Multiple (54) pages of a one-page report being generated

[Cleave_1b]

When I open a report with only one page defined I get 54 pages, each one a copy of the desired one are generated. I have checked the "open" code extensively, finding nothing in the way of a loop that would cause the problem.

The only loop is a "While Not .EOF" and a Wend. The input data is a query and I have seen that it it no longer than expected. I'm counting characteristics of residents of a community and there are only 161 residents and the input query delivers 161 entries.

I have seen nothing that suggests the report is called more than once. An interrupt in the code (outside the loop) is encountered only once during the call to the report.



Aversion of the report is emailed to the webmaster for posting (email database object). You can see the report (PDF'd) at calra.org, click the "Statistics" in the left menu. It only shows one copy (page) of the report!

I have closed and opened the application with the same results.

Suggestions are VERY welcome!

[rpeare]

Do you have a sample database? Are you applying a filter to your report? Or are you restricting your data at the query level? You say you have code running, post the code between [ code ] [ / code ] markers

if you're still working on this anyway.

[Cleave_1b]

No filers, no restrictions.

This is a report which details the number of people in the various parts of a retirement community. Each record of a person contains the "wing" location of the residence and the email address if they have one. Sometimes two people are in the same residence.

Apologies for the non-conformance of the code to te standard style. i grew up in a different coding world.

Private Sub Report_Open(Cancel As Integer)

   Dim LastUnitWing As String
   Dim dbs As Database
   Dim Rec As Recordset

   ResCountCottage = 0
   ResCountEast = 0
   ResCountWest = 0
   ResCountAL = 0
   ResCountSH = 0
   ResCountHC = 0
   ResCount = 0

   UnitCountCottage = 0
   UnitCountEast = 0
   UnitCountWest = 0
   UnitCountAL = 0
   UnitCountSH = 0
   UnitCountHC = 0
   UnitCount = 0

   CottagePix = 0
   EastPix = 0
   WestPix = 0
   ALPix = 0
   SHPix = 0
   HCPix = 0
   ResPix = 0

   CottagePixDec = 0
   EastPixDec = 0
   WestPixDec = 0
   ALPixDec = 0
   SHPixDec = 0
   HCPixDec = 0
   ResPixDec = 0

   EmailUnits = 0
   EmailRes = 0
   LastUnitWing = ""
   Dim EmailUnit As Boolean
   Dim Test As String
   Dim TestInt As Integer
   Dim Declined As Boolean

   Set dbs = CurrentDb
   Set Rec = dbs.OpenRecordset("IL_WingUnit")   'This is how we currently limit to IL units and people

   With Rec
      .MoveFirst
      EmailUnit = False
      While Not .EOF
         a = !LastCommaFirst
         .Edit
         !UtilityCheck = True
         .Update
         If !Unit & !Wing <> LastUnitWing Then   ' We won't count the same unit/wing twide
            If EmailUnit = True Then EmailUnits = EmailUnits + 1: EmailUnit = False
            Select Case !Wing   ' This covers all cases but only IL cases are presented on the report.
               Case "C": UnitCountCottage = UnitCountCottage + 1
               Case "E": UnitCountEast = UnitCountEast + 1
               Case "W": UnitCountWest = UnitCountWest + 1
               Case "A": UnitCountAL = UnitCountAL + 1
               Case "S": UnitCountSH = UnitCountSH + 1
               Case "H": UnitCountHC = UnitCountHC + 1
               Case Else: MsgBox "error in wing ID unit " & !Unit & " wing ID (" & !Wing & "), may be case case."
            End Select
            UnitCount = UnitCount + 1
            LastUnitWing = !Unit & !Wing
         End If
         Select Case !Wing
            Case "C": ResCountCottage = ResCountCottage + 1
            Case "E": ResCountEast = ResCountEast + 1
            Case "W": ResCountWest = ResCountWest + 1
            Case "A": ResCountAL = ResCountAL + 1
            Case "S": ResCountSH = ResCountSH + 1
            Case "H": ResCountHC = ResCountHC + 1
            Case Else: MsgBox "error in wing ID unit " & !Unit & " wing ID (" & !Wing & "), may be case case."
         End Select

         Dim State As String
         State = ""
         If !PD = "Y" Then State = "Declined" Else If Trim(Nz(!PictureLink)) <> "" Then State = "Taken"

         Select Case !Wing & State   'We don't use this at the present time 12/21/15
            Case "CDeclined": CottagePixDec = CottagePixDec + 1
            Case "CTaken": CottagePix = CottagePix + 1
            Case "EDeclined": EastPixDec = EastPixDec + 1
            Case "ETaken": EastPix = EastPix + 1
            Case "WDeclined": WestPixDec = WestPixDec + 1
            Case "WTaken": WestPix = WestPix + 1
            Case "ADeclined": ALPixDec = ALPixDec + 1
            Case "ATaken": ALPix = ALPix + 1
            Case "SDeclined": SHPixDec = SHPixDec + 1
            Case "STaken": SHPix = SHPix + 1
            Case "HDeclined": HCPixDec = HCPixDec + 1
            Case "HTaken": HCPix = HCPix + 1
         End Select

         If !EmailAddress <> "" Then EmailUnit = True: EmailRes = EmailRes + 1

         ResCount = ResCount + 1
         .MoveNext
      Wend
      ResPix = CottagePix + EastPix + WestPix + ALPix + SHPix   'We don't use this at the present time 12/21/15
      ResPixDec = CottagePixDec + EastPixDec + WestPixDec + ALPixDec + SHPixDec + HCPixDec
      If EmailUnit = True Then EmailUnits = EmailUnits + 1
   End With
   'We don't use this at the present time 12/21/15
   If CottagePix = 0 Then CottagePixC = "-" Else CottagePixC = Str(CottagePix)
   If EastPix = 0 Then EastPixC = "-" Else EastPixC = Str(EastPix)
   If WestPix = 0 Then WestPixC = "-" Else WestPixC = Str(WestPix)
   If ALPix = 0 Then ALPixC = "-" Else ALPixC = Str(ALPix)
   If SHPix = 0 Then SHPixC = "-" Else SHPixC = Str(SHPix)
   If HCPix = 0 Then HCPixC = "-" Else HCPixC = Str(HCPix)
   If EmailRes = 0 Then EmailResC = "-" Else EmailResC = Str(EmailRes)
   If EmailUnit = 0 Then EmailUnitC = "-" Else EmailUnitC = Str(EmailUnit)

End Sub

[rpeare]

if the image is the total of your report, you are making this way more complicated than it needs to be.

Can you supply a sample database with garbage data in it, I suspect you can do your calculations with Sorting/Grouping functions on the report itself but it's hard to tell. It looks like you've put your calculations in the detail section of your report which will blow it up rather than, say, in a group footer.

your closing [ /code ] does not need the trailing / to encapsulate your code

Example
[ code ] THIS IS MY TEST CODE [ /code ]

would result in

THIS IS MY TEST CODE

[Cleave_1b]

Not sure how to send the data to you. At the moment I propose to post the query output with only related fields to my web site for youto download, is thee a better way here?

Dick

[rpeare]

yes, you can create a copy of your database, take out any information you do not want to be out in the public sphere (i.e. mask employee names with junk names, etc) just leave enough information in the tables to replicate the problem. Then compact/repair the database, zip it up and use the GO ADVANCED button in the lower right of the posting area to attach a copy of your pared down database.

[Cleave_1b]

Couldn't figure out how to save the results of a query, so exported as xlsx, zipped and (I think) attached to this. I assume you ill either import the xlsx to a table or send me instructions on how to save the query results in a form I can zip and forward.

Dick

[Cleave_1b]

Just realized headings aren't there. So best you tell me how to save a table outside Access to zip and send.

[Cleave_1b]

Perhaps worthy of note the data is the result of a query - it excludes people who have moved out, sent permanently to health care or the Alzheimer's unit. Tje underlying table includes those folks.

[rpeare]

This doesn't really help without being able to see your object names on your report for some of your formulations but...

Example:

Cleave_1b.zip

[Cleave_1b]

Great! I'l remember that technique and I know I'll apply it to a similar report in another organization's application. Thank you very much, I can imagine the amount of effort and time you put into it. Along the way, looking at the code, did you see anything that would cause the multiple copies?

[rpeare]

there is absolutely no way for me to speculate other than what I already said.

If your current report is writing information to field that are in the detail section, that is your problem.

I'd need a sample database to say for sure.

[Cleave_1b]

Again, thanks. Although the cause of my original problem isn't found, an excellent way of solving the requirement having been provided is enough.

Dick

[rpeare]

You might try removing the RECORD SOURCE from the original report if your code is responsible for populating all of the fields you're interested in.

[Cleave_1b]

That did it! Wow - thanks again.