Query With Join For VBA Mail Merge, Access 2016

[nt100]

I developed an application as attached with MS ACCESS VBA to do mailmerge word documents with the following queries (qryTReapptNomination_HCAP) and (qryTNewNomination_HCAP) separately

I 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, when I 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;


The queries work from MS Word mail merge utilities directly.

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 and tests.

Thank you in advance for any idea on this problem.

[June7]

I have to ask why are you bothering with mail merge instead of just using Access report?

First thing I did with your code is run Debug > Compile. It errors on a reference to fraNewApptLetDelivery.

Why do you have code for buttons that don't exist?

[nt100]

I am sorry for removing some controls that I thought is not relevant my question. Since my application is very big. I just wanted to simplify the matter. Now, the latest app (Appt_1.zip) Appt_1.zipas attached has that you wanted

[June7]

When the mail merge initiates and tries to OpenDataSource, I get a popup "Appointment.accdb is locked for editing by 'another user'." I select 'Open read-only copy'. The Word template opens but get another popup "Header Record Delimiters". I cancel that popup and now I get the 5922 error Unable to open the data source.


Again, why do you want to endure this torture?