Super Easy Mail Merge and subforms

[RCheesley]

Hi all,

I wondered if anyone has got Super Easy Mail Merge to incorporate subforms when doing the mail merge at all?

It does just what I want it to do, but isn't picking up any of the fields from my subforms.

Here's the link to the information & sample database, you just have to import the two modules & forms and create a command button.

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

Thanks in advance

Ruth

 

[GaryPanic]

Solution is to have a temp table that has the fields you require to be merged then put all of the fields on the form you are using (with the properties visible no )

Reason it only picks up the active form you are on and the fields on this form

hope this helps

 

[RCheesley]

Hi Gary,

Thanks for the speedy response, not sure I understand what you mean though! All the fields on the subform are using completely different queries to pull the data in, and as none of these fields are on the field list for the master form, how would I make a table with these fields in?

I see the concept but not quite sure of how to actually do it.

Ruth

 

[GaryPanic]

OK - this is how I got to do it

have a temp table with all of the fields populated from what ever sub tables are required (max 255 fields)
now make your from based on this table

I have this working like this

open the sub form (behind the scene a qry appends the data into atemp table) all of the fields in the table are now on the form - but with properties visible - no (apart from ones that i want to tweak before the merge )
merge

once i naviagate off the form - I have a delete qry empty the temp table - ready for the next merge ..

hope this helps

I am at work at the moment - but have notes on how to do this at home...

 

[jsv2002]

Although I have not used the function you can also use a query to export the fields that you want:
http://www.members.shaw.ca/AlbertKallal/wordmerge/page2.html

Found at the above link:

"If you need NO prompts, and want to specify the from/to document name, then use
MergeNoPrompts strFromDocTemplate,[strDir],[bolFullPath],[strOutPutDoc],[strSql],[bolPrint],[strPrinter]


strSql = optional - this can be any sql/query to provide the data for the merge in place of the "one" current form."​

So you could set up your query and limit to current record then use that to populate the word document.

Must say is an excellent access app and many thanks to Albert Kallal for making it available

Good luck John

 

[RCheesley]

Still none the wiser with any of the above .... Guess I don't have my l337 access brain with me today and need some fisher price spoon feeding

Ruth

 

[jsv2002]

Guess I don't have my l337 access brain with me today and need some fisher price spoon feeding

Ruth

Sounds like me apart from the random bouts of lucidity...

 

[GaryPanic]

Although I have not used the function you can also use a query to export the fields that you want:
http://www.members.shaw.ca/AlbertKallal/wordmerge/page2.html

Found at the above link:

"If you need NO prompts, and want to specify the from/to document name, then use
MergeNoPrompts strFromDocTemplate,[strDir],[bolFullPath],[strOutPutDoc],[strSql],[bolPrint],[strPrinter]


strSql = optional - this can be any sql/query to provide the data for the merge in place of the "one" current form."​

So you could set up your query and limit to current record then use that to populate the word document.

Must say is an excellent access app and many thanks to Albert Kallal for making it available

Good luck John

I don't think this is going to help....

 

[GaryPanic]

get your information into one table and work form this .

 

[RCheesley]

Do you mean re. doing the query, that i could make a query with all the relevant fields (from the different tables, presumably?) and use this for the mailmerge? Presumably I would filter it based on the patient ID currently being viewed?

Would I edit the code you posted above in the module? Still all shiny and new when it comes to playing with modules

Ruth

 

[jsv2002]

No its the button you have on your form which uses the MergeSingleWord, this command calls the GuiWordTemplate form you can use MergeNoPrompts and specify your own criteria if you already have the template set up and don't require any user interaction.

However as I said I have not used this function yet so cannot give you any specifics I do currently specify my output folder and name of the created document using:

MergeSingleWord , , strFileName & "\" & Me.QID.Value

Where strFileName is a Dlookup from a table that the user has specified the output folder for generated documents Me.QID.Value is a control on the form with the document name. (the ,, represent the module call defaults for the folder templates are stored in e.g. Word)

will have a play and see if what I am thinking can be easily done and get back to you.

John

 

[GaryPanic]

are you ok with append qry's?
get all the information you require into the qry and append into the temp table (call it something like mailmergetmp)
run the qry off your record id forms!formname!fieldname!
this will append the data into the table once it is run
have all of the fields on your form - but with properties visible - no
(You can make some of them visible and over type if you want- this will not change the underlying table /info ) then use the mail merge as you ahve it and then once you come off the form or out of the sub form have a delete qry to delete the data from the temp table Mailmergetmp

I have found that the mail merge will only work off 1 table and 1 form using the temp table and basing the form on this works but you are limited to 254 fields .

 

[RCheesley]

I am a noobie to access and hence no, not happy with temp tables and append queries.

I think I'm almost there with running the SQL from a query but it's not seeing the entire query, it stops just before the FROM statement:

Private Sub cmdStartRCA_Click()
Dim strSQL As String
strSQL = "SELECT tblPatientDetails.PatientDetailsID, tblPatientDetails.PatientDetailsNHSNumber," & _
"tblPatientDetails.PatientDetailsSurname, tblPatientDetails.PatientDetailsFirstName," & _
"tblPatientDetails.PatientDetailsDoB, tblPatientDetails.PatientDetailsGP, tblSpecimen.SpecimenID," & _
"tblSpecimen.SpecimenDate, tblSpecimen.SpecimenOrganism, tblAdmissions.AdmissionsHospitalCode," & _
"tblAdmissions.AdmissionsDateOfAdmission, tblAdmissions.AdmissionsDateOfDischarge," & _
"tblAdmissions.AdmissionsWard, tblRCA.RCAID, tblRCA.RCAPatientInitials, tblRCA.RCAPatientGPName," & _
"tblRCA.RCAGroupMember1, tblRCA.RCAGroupMember2, tblRCA.RCAGroupMember3, tblRCA.RCAGroupMember4," & _
"tblRCA.RCAPMH, tblRCA.RCAAntibioticsforCdifficile, tblRCA.RCAPPITherapy, tblRCA.RCAOtherRiskFactors," & _
"tblRCA.RCAAuditResults, tblRCA.RCADiscussion, tblRCA.RCAAvoidable, tblRCA.RCARootCause, tblRCA.RCAAction1," & _
"tblRCA.RCAAction1Lead, tblRCA.RCAAction1DateCompletion," & _
"tblRCA.RCAAction1DateCompeted, tblRCA.RCAAction1Evidence, tblRCA.RCAAction2, tblRCA.RCAAction2Lead," & _
"tblRCA.RCAAction2DateCompletion, tblRCA.RCAAction2DateCompeted, tblRCA.RCAAction2Evidence," & _
"tblRCA.RCAAction3, tblRCA.RCAAction3Lead, tblRCA.RCAAction3DateCompletion, tblRCA.RCAAction3DateCompeted, tblRCA.RCAAction3Evidence" & _
"FROM ((tblPatientDetails INNER JOIN tblAdmissions ON tblPatientDetails.PatientDetailsID =" & _
"tblAdmissions.PatientDetailsID) INNER JOIN tblRCA ON tblPatientDetails.PatientDetailsID =" & _
"tblRCA.PatientDetailsID) INNER JOIN tblSpecimen ON (tblPatientDetails.PatientDetailsID =" & _
"tblSpecimen.PatientDetailsID) AND (tblAdmissions.SpecimenID = tblSpecimen.SpecimenID)" & _
"WHERE (((tblPatientDetails.PatientDetailsID)=[Forms]![frmPatientDetailsWithTabs]![txtPatientDetailsID]));"
MergeAllWord strSQL
End Sub

Gotta go home now .. will be back tomorrow for a scathing review of my sql/query no doubt