Good day all,
I am running a QueryDef to create a query for a report. When the code is executed, I receive the below error:
"The specified field '[FieldName]' could refer to more than one table listed in the FROM clause of your SQL statement."
I understand the error, but I cannot find the error in the SQL statement. Below the code:
Code:
qDef.SQL = "SELECT DISTINCT Tbl_Certification.StudentID, Tbl_StudentReg.AltIDNumber, Tbl_StudentReg.UseAltIDOnDocs, " _
& "Tbl_StudentReg.FirstName, Tbl_StudentReg.Surname, Tbl_Certification.CourseNumber, " _
& "Tbl_Courses.CourseDescription, " _
& "Tbl_Certification.StartDate , Tbl_Certification.EndDate, Tbl_Certification.AssDate, Tbl_Certification.CertDate, " _
& "Tbl_Certification.Facilitator, Tbl_Facilitators.FacilitatorName, " _
& "Tbl_Certification.Assessor, Tbl_Assessors.AssName , Tbl_Certification.Moderator, Tbl_Assessors_1.AssName, " _
& "Tbl_Certification.Employer, Tbl_Employer.EmployerName, Tbl_Certification.CertNumber, " _
& "Tbl_Certification.Result_Theory, Tbl_Certification.Result_Practical , Tbl_Certification.Result_Assignment, " _
& "Tbl_Certification.Result_Final_C_NYC, Tbl_Certification.AltCourseDescriptor, " _
& "Tbl_Certification.AltCourseSequenceNo, Tbl_CertificateNumbers.CertificateNumberPrefix, " _
& "Tbl_CertificateNumbers.CertificateMonthYear " _
& "FROM Tbl_CertificateNumbers INNER JOIN ((((((Tbl_Certification INNER JOIN Tbl_StudentReg ON " _
& "Tbl_Certification.StudentID = Tbl_StudentReg.IDNumber) INNER JOIN Tbl_Courses ON " _
& "Tbl_Certification.CourseNumber = Tbl_Courses.CourseNumber) INNER JOIN Tbl_Facilitators ON " _
& "Tbl_Certification.Facilitator = Tbl_Facilitators.FacilitatorID) INNER JOIN Tbl_Assessors ON " _
& "Tbl_Certification.Assessor = Tbl_Assessors.IDNumber) INNER JOIN Tbl_Assessors AS Tbl_Assessors_1 ON " _
& "Tbl_Certification.Moderator = Tbl_Assessors_1.IDNumber) INNER JOIN Tbl_Employer ON " _
& "Tbl_Certification.Employer = Tbl_Employer.EmployerNo) ON " _
& "(Tbl_Certification.CertNumber = Tbl_CertificateNumbers.CertificateCertNumber) AND " _
& "(Tbl_CertificateNumbers.CertificateStudent = Tbl_StudentReg.IDNumber) " _
& "WHERE Tbl_Certification.CertNumber = " & Val([Forms]![Frm_Results].[Text0]) & ";"
qDef.Close
db.Close
Set qDef = Nothing
Set db = Nothing
Application.RefreshDatabaseWindow
The field in question, is "AssName". As can be seen, I am preceding the field name with the table name, but I still receive the error.
I am opening a report from the main form. The above code is in the "Report_Open" event. While the code executes in the "Open" event, no error occurs. When the "Exit Sub" runs in the "Open" event, the code jumps back to the Main form and displays the error message. The report's "Load" event does not run.
I trust I have explained the issue in enough detail.
Your assistance is highly appreciated
Thanks