Modify DataSource

[advin]

Hi,

is there a possibility to modify the DataSource that is beíng used for the Mailmerge in e.g the MailMergeBeforMerge or MailMergeBeforeRecordMerge- event?

I basically need to delete/exclude some of the lines/rows in the DS.

Any help is highly appreciated

[macropod]

Hi advin,

In Word, you can apply a filter to the data, or you can use a SKIPIF field. Either approach can be used to exlude the unwanted records.

[advin]

Hi macropod,

thanks for the information. Unfortunately my scenario is a bit more complex.
The thing is, that i need to use the content of a few fields from the line items I dont want to be used as a seperate record by the MailMerge- routine.
It like this:
DataSource:
Record A - field1, field2,field3
Record B - field1, field2,field3

Final Document:
Record A - field1,field2,field3, field3 (from record B - inserted via macro??)

So, is there a possibility to step through the records in the MailMergeBeforeRecordMerge event and to modify the datsource in VBA then?

Cheers, advin
Record

[macropod]

Hi Advin,

Your description suggests there's some commonality between records A & B. This is common when you're trying to generate a mailmerge to clients listing their outstanding Invoices, for example. In that's something like your case, you can probably use Word's Catalogue/Directory Mailmerge facility for this (the terminolgy depends on the Word version). To see how, check out my Word 97-2007 Catalogue/Directory Mailmerge Tutorial at:
http://www.wopr.com/index.php?showtopic=731107 (mhtml:{2603C627-B617-484B-B3E3-521BCFB808E8}mid://00000174/!x-usc:http://www.wopr.com/index.php?showtopic=731107)
or
http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip (mhtml:{2603C627-B617-484B-B3E3-521BCFB808E8}mid://00000174/!x-usc:http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip)
Do read the tutorial before trying to use the mailmerge document included with it.

[advin]

Hi macropod,

actually I allready have a line-item template inlcuding footer etc. established.
I'll try to explain the situation:
DB-structure:

the main entity is a invoice,.. the invoice has multiple invoice products <- its not problem to build a template that's capable to deal with this structure.
BUT the invoice has multiple, lets say, Invoice-descriptions too. so the record-set in Word contains a line for each combination of invoice-products and invoice-descriptions..

wo with 2 inv-products and 3 inv. descrp. i get a total of 6 lines. But i just need 2 of them with content of ALL 3 descr. in the 2 lines.

<- sorry, that's quite confusing!!

So what i think that needs to be done is to delete the 2nd,3rd line and the 5th, 6th line from the recordset After saving the inv-descr. from this fields.
I can then insert this content in VB easily.

Hopefully you can understand what i mean and give me a hint on the VB- code that needs to be used (basically how to modifiy the datasource programmatically)

Best Regards, advin

[macropod]

Hi Advin,

According to your previous post, the source data are laid out as:
Record A - field1,field2,field3
Record B - field1,field2,field3
That's only two lines per record set. Not the 6 you referred to. Does one of the fields in records A & B identify the customer?

Did you take a look at the tutorial and how it handles multiple records?

Perhaps you could post an file containing a demonstration of how the source data are laid out and how you want the same data to appear in the final output.

[advin]

Hi,

i did take a look into your tutorial. (it was very informing and helpful)

Attached you can find 3 files:
1; Example-template: a simple layout of the document
2; testscenario.mdb: The data is it is loaded to Word. You will see 6 lines although i basically just need 2 of them
3; testscenario_modified.mdb: how i need the datasource to be basically.
As you can see i have to do a lot of modification to convert [2] to look like [3].

anyway, thanks a lot for your help so far! And , as always, any hint is highly appreciated.

Regards, advin

[macropod]

Hi Advin,

I take it that your 'modified' db represents how you'd like the data to appear in the mailmerge output. In that case, all you need to do is to take the 'multi-column format' approach mentioned on page 4 of my tutorial (note: I've just uploaded a new version to http://www.wopr.com/index.php?showtopic=731107 (mhtml:{2603C627-B617-484B-B3E3-521BCFB808E8}mid://00000174/!x-usc:http://www.wopr.com/index.php?showtopic=731107) - your current page # may be different).

My 'multi-column format' approach doesn't generate a table, per se, but you could easily enough run a macro over the output after the merge has completed, to turn the output into such tables.

[advin]

Hi,

thanks again.
I did notice this feature before but it is required that the datasource really is like the "modified"-db afterwards as an email-send procedure is triggered which steps through the records again and therefore creates a records and sends a mail for each of them.

Is there any possibility to modify the datasource in VB?

Best Regards, advin

[macropod]

Hi Advin,

There is no need to modify the db for that to work. If you take a look at the data source for my tutorial, you'll see there's only one line per record, but the output can turn this into multiple columns.

Yes, I suppose one could modify the datasource with vba. However, you'd then end up with a completely different database for which you'd need to know in advance of doing the merge how many records there might be and code the mailmerge to both accommodate the maximum number of fields and deal with however many empty fields there are in each a given record.