Multiple instances of an Access report

[DeonO]

Hi,
I have an Access 2016 Database where I am opening multiple instances of one report.
Everything is running as it should and the multiple reports are opened (up to 50 at one time).
In the report's Load event, there are code running that will retrieve data from the different tables with SQL statements. This data must then be inserted on the reports. Every time the report is opened, the Load event executes.
My problem is that when I debug the report, all the data is transferred to the fields on the report, but when all 50 reports are open, none of them is displaying any data!! They are all blank reports.
I use the below code to open the reports, which I found online. This code is run from the form and not within the report:

While Not Rst3.EOF
    Set rpt = New Report_Rpt_StudentReg
    rpt.Visible = True
    rpt.Caption = rpt.hwnd & " Student Reg. " & Rst3.StudentID
    'Append it to the collection.
    clnClient.Add Item:=rpt, Key:=CStr(rpt.hwnd)
    Rst3.MoveNext
    DoEvents
Wend

The report's Load event, contains the following code and is executed every time the report opens:

Private Sub Report_Load()
Set Dbs = CurrentDb: Set Dbs1 = CurrentDb


Select Case Mode
    Case "WithData"
        If MyMode = "Certification" Then
            SQL1 = "SELECT StudentID FROM Tbl_Certification WHERE CertNumber = " & Rst3!CertNumber & " ORDER BY StudentID;"
            Set Rst1 = Dbs1.OpenRecordset(SQL1)
            SQL = "SELECT * FROM Tbl_StudentReg WHERE IDNumber = " & TempStudentHolder(Z) & ";"
            Set Rst = Dbs.OpenRecordset(SQL)
            Rst1.Close
            Z = Z + 1
        Else
            SQL = "SELECT * FROM Tbl_StudentReg WHERE IDNumber = " & Forms!Frm_StudentReg_Edit.IDNumber & ";"
            Set Rst = Dbs.OpenRecordset(SQL)
        End If
        Me.FirstName = Rst!FirstName
        Surname = Rst!Surname
        PrevSurname = Rst!PrevSurname
        IDNumber = Rst!IDNumber
        BirthDate = Rst!BirthDate
        AltIDNumber = Rst!AltIDNumber
        AltIDType1 = Rst!AltIDType
        '-------------------
        SQL1 = "SELECT * FROM Tbl_AltID WHERE AltID = " & Rst!AltIDType & ";"
        Set Rst1 = Dbs1.OpenRecordset(SQL1)
        AltIDTypeDescr1 = Rst1!AltIDDescr
        '-----------------------------
        Nationality1 = Rst!Nationality
        SQL1 = "SELECT * FROM Tbl_Nationality WHERE NationalityNo = " & Rst!Nationality & ";"
        Set Rst1 = Dbs1.OpenRecordset(SQL1)
        NationalityDescr1 = Rst1!NationalityDescr
        '-------------------------------
        Gender1 = Rst!Gender
        SQL1 = "SELECT * FROM Tbl_Gender WHERE GenderNo = " & Rst!Gender & ";"
        Set Rst1 = Dbs1.OpenRecordset(SQL1)
        GenderDescr1 = Rst1!GenderDescr
        '-------------------------------
        CitizenCode1 = Rst!Citizen
        SQL1 = "SELECT * FROM Tbl_Citizen WHERE CitizenCode = " & Rst!Citizen & ";"
        Set Rst1 = Dbs1.OpenRecordset(SQL1)
        CitizenCodeDescr1 = Rst1!CitizenDescr
        '----------------------------
        Socio1 = Rst!SocioStatus
        SQL1 = "SELECT * FROM Tbl_Socio WHERE SocioCode = " & Rst!SocioStatus & ";"
        Set Rst1 = Dbs1.OpenRecordset(SQL1)
        SocioDescr1 = Rst1!SocioDescr
        '----------------------------------------------
        Disability1 = Rst!Disability
        SQL1 = "SELECT * FROM Tbl_Disability WHERE DisabilityCode = " & Rst!Disability & ";"
        Set Rst1 = Dbs1.OpenRecordset(SQL1)
        DisabilityDescr1 = Rst1!DisabilityDescr
        '-----------------------------------
        HomeLang1 = Rst!Homelang
        SQL1 = "SELECT * FROM Tbl_HomeLanguage WHERE HomeLangCode = " & Rst!Homelang & ";"
        Set Rst1 = Dbs1.OpenRecordset(SQL1)
        HomeLangDescr1 = Rst1!HomeLangDescr
        '----------------------------------
        Employer = Rst!EmployerNo
        SQL1 = "SELECT * FROM Tbl_Employer WHERE EmployerNo = " & Rst!EmployerNo & ";"
        Set Rst1 = Dbs1.OpenRecordset(SQL1)
        EmployerName = Rst1!EmployerName
        Contact = Rst1!Contact
        ContactCell = Rst1!ContactCell
        ContactTel = Rst1!ContactTel
        '-------------------------------------------
        Province1 = Rst!Province
        SQL1 = "SELECT * FROM Tbl_Province WHERE ProvinceCode = " & Rst!Province & ";"
        Set Rst1 = Dbs1.OpenRecordset(SQL1)
        ProvinceDescr1 = Rst1!ProvinceDescr
        '----------------------------------
        Equity1 = Rst!Equity
        SQL1 = "SELECT * FROM Tbl_Equity WHERE EquityCode = " & Rst!Equity & ";"
        Set Rst1 = Dbs1.OpenRecordset(SQL1)
        EquityDescr1 = Rst1!EquityDescr
        '----------------------------------
        GradePassed = Rst!HighestGradePassed
        '----------------------------------
        PostAddress1 = Rst!PostAddress1
        PostAddress2 = Rst!PostAddress2
        PostAddress3 = Rst!PostAddress3
        PostCode = Rst!PostCode
        HomeAddress1 = Rst!HomeAddress1
        HomeAddress2 = Rst!HomeAddress2
        HomeAddress3 = Rst!HomeAddress3
        PostCodeHome = Rst!PostCodeHome
        OfficeTelNumber = Rst!OfficeTelNumber
        CellNumber = Rst!CellNumber
        OfficeFax = Rst!OfficeFax
        MyEMail = Rst!EMail
        Rst1.Close
        Rst.Close
        'Open the waiver report to insert the last data
        'If StudentCounter = 1 Then
        '    DoCmd.OpenReport "Rpt_Indemnity", acViewPreview
        '    Exit Sub
        'End If
        Set rptWaiver = New Report_Rpt_Indemnity
        rptWaiver.Visible = True
        rptWaiver.Caption = "Indemnity " & Rst3.StudentID
        'Append it to our collection.
        clnClient.Add Item:=rptWaiver, Key:=CStr(rptWaiver.hwnd)
        
        '-------------------------------------------
    Case Else

Can anybody please help?

Thanks
Deon

 

[Ranman256]

What is the point of opening 50 reports at 1 time ,when you can only read 1 at a time?

 

[MajP]

I do not get it either, but the code also makes no sense to me. For what reason is this unbounded? This should be a simple bound form based on a query. Very inefficient code to do a simple task.

 

[Dreamweaver]

If you only want to Multiple Say Employees you could create a form with a list box with multi select and seleced all Employees/Items and loop through the selected items and add them to a temp table or create a where string to display each required item.

I'm sure I did something like that in one of my examples I think the employee v4

 

[DeonO]

Hi all,
Let me explain:
The user captures between 1 and 50 students at a time. The ID numbers are stored in an array as they capture the information as each report is based on the ID number. Once all the students are captured, they click on a button "Print all Documents". Then all the reports are opened in preview, along with some other reports pertaining to the course being presented. Once all the student registration forms are opened in preview, the user prints the reports one by one.

If there is a simpler way of doing this, please let me know.

Thanks

 

[Gasman]

If there is a simpler way of doing this, please let me know.

Print the reports while looping through the array?
Print the ancillary reports at the start/end of that process.

 

[DeonO]

Hi Gasman, Yes, printing while looping through the array is an option, but it is not what my customer wants. He wants all the reports to be opened in preview mode. They then decide which of the 50 reports they want to print.
It seems the issue is because the reports are opened in preview. Printing directly to the printer does not seem to be a problem.

 

[isladogs]

@DeonO
You really do like making things unnecessarily difficult.

Use a multiselect listbox to select all the students required at that time.
Now modify your report filter so only one copy of the report is opened but with all the selected students included...on separate pages.
Open in print preview and print all pages/selected pages

 

[DeonO]

Hi isladogs,
I get your suggestion and under normal circumstances, it is the solution, but not in this case.
Firstly, I cannot use a multiselect listbox as the students has not yet been captured. On my capture form, I capture new records. The user enters the course number and each course can have between 1 and 50 students. When capturing, the system assigns a "Certification Run" number to the students being captured. After capturing the students data, the user clicks on a button on the form "Print Documents". From here all the students that was captured on the specific "Certification Run", is opened in multiple instances of the same report, using the code above.

In the meantime, I have changed the code so that every instance of the report that is opened, runs on its own criteria. This resolves the issue and minimized the amount of code by more than half, but now a new issue has arised:
If I run the program step by step (F8), everything is fine and all reports are opened in preview. All data is appearing on the reports and is printing correctly.
If I run the code without stepping, some reports have the data and others are blank. Some data is also put onto the wrong reports.

Does anybody perhaps know why this will happen?

Thanks

 

[Mike Krailo]

Sounds like a timing issue. You will need to find the line of code that requires more time to execute and try using the doevents command right after it. If that doesn't work, try searching the forum on timing issues. You will get lots of suggestions.

 

[DeonO]

Hi Mike.
Thanks, yes it was a timing issue - all resolved.

Thanks!!

 

[Gasman]

Hi Mike.
Thanks, yes it was a timing issue - all resolved.

Thanks!!

Well for the benefit of others who might end up with the same problem, please explain how you solved it?