Selection by user from result of query for mailmerge

[Nvijayk]

Hi

I have a table for enquiries from customers and I populate this through a form.I have a field ( combo getting its values from Categories table) for category of product under enquiry. I have a separate suppliers table with a field for category to query it to get list of suppliers under the relevant selected category criteria.

What I need is as follows. Based on the data input in the category field in the customer enquiry form, I need a command button clicking which will produce the relevant list of back suppliers in a list box or similar, visible in the form or separately where I can then make a selection of the suppliers from that shortlist to send out requests for quotes by a mailmerge operation. Is this possible or could someone please advise an alternate way to achieve the result.

Please help.

nvk

 

[jebry]

Hi!

This is certainly possible and the easiest way to get the list is to set the supplier's list box's row source to a query that includes in the criteria line of the category field a reference to the category list/combo box. In the after update event of the category list box requery the supplier list box so that it is then limited to the suppliers in that category. You can then use the button click to open the Word doc and run its mail merge using the query we just discussed. Actually, the mail merge will probably be somewhat more complex then that because Word (2k anyway) opens a new instance of Access to do a mail merge and so you category selection will not be available. There is a couple of ways around this:

1.) Instead of doing a mail merge doc, set up a .dot with bookmarks and loop through the selected items in the supplier list box, exporting the appropriate values to correct bookmark and printing each letter when it is complete.

2.) Use the query to make a temporary table (which you will need to delete first) and use the temp table as the data source for the mail merge. That way, when you do the merge the correct information is available to the document.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Nvijayk]

Hi Jeff

Works perfectly.Many many thanks.I opted to make a temp table as I did't fully understand your first option of .dot etc/ loop method.

Cheers

Vijay