Mail Merge 1

[TheWebDataGuy]

I have posted this problem before but no one replied. I have already spent many hours figuring this out with no luck. May be I could not explain it to you guys. I will try again:

I have to create a mail merge from an Access form. That form is a combination main form/Subform. Main form has a FileID (as primary key) and gives details of that File. The subform list the names of people associated with that file(FileID + StaffID is the composite PK there).

Users want one letter per FileID that will contain information on ALL the names (StaffID) associated with that FileID. Now once the users filter the data in the form, they export it in excel format to be used for mail merge. This exported data unfortunately contains information on the Main Form ONLY. Names (StaffIDs) in the subform are not exported.

My last resort seems to be denormalizing the database itself! (FileID, StaffID1, StaffID2, StaffID3.....)Its such a stupid thing to do for a database designer but I am running out of choices here!

I would appreciate your help.

 

[jobo123]

Hi WebDataGuy,

Sorry you have had no responses so far - I'm no expert on this but will try to help. If I understand you right, you are trying to launch a mail merge from records in a sub form and with a filter.

You could try creating a new table with fields as in your subform, called tblCriteria (containing just one blank row).

When the filter is applied (ie the On Apply Filter Event) on the sub form, run a macro or code to use the values from the filter (ie all the criteria entered on the sub form) to update the values of the single row in tblCriteria.

Then, also in the On Apply Filter Event, run a select query (called qrySelectedRecords) based on all the possible records in the subform (ie based on the underlying table or query behind the subform). Within qrySelectedRecords use DLookUp (slow but effective) to feed the values of each of the fields in tblCriteria into the criteria row of qrySelectedRecords. You now have a query which gives the same results as your filter results in the subform.

A button on the main (which you might want to hide depending on whether you are in filter mode or not) can now run a merge based on the contents of qrySelectedRecords rather than by trying to extract the records directly from the filtered subform.

Its not a very elegant solution, and others may be able to give methods which catch the records directly from the filtered subform, but it might be a way the achieve what you need using good old fashioned queries and not to much code. I've not tested it so apologies in advance if there are any holes in my reasoning.
Best of luck jobo123.

 

[TheWebDataGuy]

Thanks Jobo!

You have given a fresh new idea. I was so stuck there.

 

[jhowley58]

I use VBA to create the correct query when a 'mailmerge' command button is pressed.. In fact I create a temporary table filled by the query and link the document to the table using ODBC. Every then fires up from the command button.

If you use VBA I will send you the code.

John

 

[Isadore]

John --

getting ready to hook up a list of contacts with a field having email address - certain queries will send out batch emails based on results - wd like to take a look at your code if you don't mind...

Isadore