Hi All-

Thanks to the wonderful Albert Kallal, I can now use my Access form and populate a Word template with information on the form. I downloaded Albert's Super Easy Mail Merge, followed easy instruction for transferring code to my database, and have been a happy camper since..

I have, however, stumbled on a situation where when a particular field on my form is filtered (say LastName "Smith), filtered records do not show on the Word document. I have emailed Albert for advice and he replied with the following:

"Yes, you can use any SQL you want and you can use the current forms filter also.

For SQL, you just go:

Dim strSQL As String

strSQL = "select * from contacts where City = 'Edmonton' "

MergeAllWord strSQL


So in the above in place of MergeSingleWord, we use the command MergeAllWord. This also lets you build a query in the query builder, and use that also.

Eg:

MergeAllWord "query1"

To use the current forms filter, you can go:

Dim strSQL As String

strSQL = "select * from contacts where " & Me.Filter

MergeAllWord strSQL

So, you are not restricted to merging “just” the one record, but you can use a query with conditions that you build, or as a above simply build the sql and your “conditions” on the fly. And as the last example shows, you can build a sql query based on the current forms filter. So you can merge “many” records based on filters or queries or sql that you write." (Albert Kallal)

The problem I am having is where to put the codes. I hope someone can give further advice on this. Here are my questions:

- The form I use is sourced by a Table, so I don't have an SQL in place - assumed by the codes above. Is there a workaround for this? When I tried either codes, I get "an SQL hasn't been set up...." message.

- I use the filter on the ribbon to filter records. The form that Albert provided has a "filtered/unfiltered" option. Does it matter which filter I use for the code to work?

I can post the entire Mailmerge code that I got from Albert, but I am hoping a simple solution exists for merging filtered records to Word via Albert's mail merge method.

Thanks, in advance, for your attention!!

-Tommy

I currently use 2010 Access.

-Tommy

You should be able to just replace the table Contacts with the table name you're using in your form:

strSQL = "select * from [YourFormsTableName] where " & Me.Filter

or

strSQL = "select * from " & Me.Recordsource & " where " & Me.Filter

Thanks for the quick response! I tried your suggesstions and got this message for both:

no data for this merge

No data was created for this merge. Make sure the sql is correct.
sql was

select * from KT VANPOOL DRIVER RECORDS where ([Contacts].[LastName] = "Smith")

Any idea why I got the message?

-Tommy

If your table name has spaces, you need to surround it with square brackets. Recommended practice is to remove any spaces from table/query/field names etc. so that you can avoid these situations.

select * from [KT VANPOOL DRIVER RECORDS] where ([Contacts].[LastName] = "Smith")

I'm also not sure how your referencing table KT VANPOOL DRIVER RECORDS, but then using the Contacts table in your Where clause. This would imply that you have two tables on your form somehow, or that you are in fact using a query, but you still shouldn't be referencing two different tables unless there is a Join between the two.

darnellk - your advice was spot on! I got the mail merge to work using filtered records.

Thanks so much!!

-Tommy

Glad you got it working.