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:
The report's Load event, contains the following code and is executed every time the report opens:
Can anybody please help?
Thanks
Deon
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:
Code:
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:
Code:
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