I use MS ACCESS vba to do mailmerge word documents with the following queries (qryTReapptNomination_HCAP) and (qryTNewNomination_HCAP) separately
Run frmReport
Re-appointment - > Honorary Clinical Assistant Professor
It works fine with qryTReapptNomination_HCAP
Its query structure is shown bleow
SELECT tblProcessTReAppt.TRef, tblTutor.UID, tblTutor.Title, tblTutor.LastName, tblTutor.FirstName, tblTutor.Alias, tblTutor.ClinicAdd1, tblTutor.ClinicAdd2, tblTutor.ClinicAdd3, tblTRank.HDesc, tblTAppt.JobPosition, tblTAppt.Company, tblTAppt.Honorarium, tblTAppt.Allowance, tblTAppt.FCF_No, tblTAppt.budget
FROM ((tblProcessTReAppt LEFT JOIN tblTutor ON tblProcessTReAppt.TRef = tblTutor.TRef) LEFT JOIN tblTAppt ON tblProcessTReAppt.TRef = tblTAppt.TRef) LEFT JOIN tblTRank ON tblTAppt.RankCode = tblTRank.ID
WHERE (tblTAppt.AppointedBy = "UG") and (tblTAppt.RankCode=1) and (tblProcessTReappt.ReplyToInvitation= "Y")
ORDER BY tblTutor.LastName, tblTutor.FirstName, tblTutor.Alias;
However, run frmReport
New Appointment - > Honorary Clinical Assistant Professor
pops up of “Confirm Data Source” and then runs into error "5922: Word was unable to open the data source".
qryTNewNomination_HCAP
SELECT PT.TRef, PT.ApptDtStart, PT.ApptDtEnd, T.UID, T.Title, T.LastName, T.FirstName, T.Alias, T.EMail_1, T.BQual, T.PQual1, T.PQual2, T.PQual3, T.ClinicAdd1, T.ClinicAdd2, T.ClinicAdd3, TR.HDesc, NA.JobPosition, NA.Company, NA.Honorarium, NA.Allowance, NA.FCF_No, NA.budget
FROM ((tblProcessTNewAppt AS PT INNER JOIN tblTutor AS T ON PT.TRef = T.TRef) INNER JOIN tblTNewAppt AS NA ON PT.TRef = NA.TRef) INNER JOIN tblTRank AS TR ON NA.RankCode = TR.ID
WHERE (PT.ReplyToInvitation= "Y") AND (NA.RankCode = 1)
ORDER BY T.LastName, T.FirstName, T.Alias;
I have inspected several times and found nothing wrong with the structures and queries. It's weird that one query works fine but a similiar query doesn't work for the same VBA codes.
Pls. find the attached application for details.
Thank you in advance for any idea on this problem.
Run frmReport
Re-appointment - > Honorary Clinical Assistant Professor
It works fine with qryTReapptNomination_HCAP
Its query structure is shown bleow
SELECT tblProcessTReAppt.TRef, tblTutor.UID, tblTutor.Title, tblTutor.LastName, tblTutor.FirstName, tblTutor.Alias, tblTutor.ClinicAdd1, tblTutor.ClinicAdd2, tblTutor.ClinicAdd3, tblTRank.HDesc, tblTAppt.JobPosition, tblTAppt.Company, tblTAppt.Honorarium, tblTAppt.Allowance, tblTAppt.FCF_No, tblTAppt.budget
FROM ((tblProcessTReAppt LEFT JOIN tblTutor ON tblProcessTReAppt.TRef = tblTutor.TRef) LEFT JOIN tblTAppt ON tblProcessTReAppt.TRef = tblTAppt.TRef) LEFT JOIN tblTRank ON tblTAppt.RankCode = tblTRank.ID
WHERE (tblTAppt.AppointedBy = "UG") and (tblTAppt.RankCode=1) and (tblProcessTReappt.ReplyToInvitation= "Y")
ORDER BY tblTutor.LastName, tblTutor.FirstName, tblTutor.Alias;
However, run frmReport
New Appointment - > Honorary Clinical Assistant Professor
pops up of “Confirm Data Source” and then runs into error "5922: Word was unable to open the data source".
qryTNewNomination_HCAP
SELECT PT.TRef, PT.ApptDtStart, PT.ApptDtEnd, T.UID, T.Title, T.LastName, T.FirstName, T.Alias, T.EMail_1, T.BQual, T.PQual1, T.PQual2, T.PQual3, T.ClinicAdd1, T.ClinicAdd2, T.ClinicAdd3, TR.HDesc, NA.JobPosition, NA.Company, NA.Honorarium, NA.Allowance, NA.FCF_No, NA.budget
FROM ((tblProcessTNewAppt AS PT INNER JOIN tblTutor AS T ON PT.TRef = T.TRef) INNER JOIN tblTNewAppt AS NA ON PT.TRef = NA.TRef) INNER JOIN tblTRank AS TR ON NA.RankCode = TR.ID
WHERE (PT.ReplyToInvitation= "Y") AND (NA.RankCode = 1)
ORDER BY T.LastName, T.FirstName, T.Alias;
I have inspected several times and found nothing wrong with the structures and queries. It's weird that one query works fine but a similiar query doesn't work for the same VBA codes.
Pls. find the attached application for details.
Thank you in advance for any idea on this problem.