Generate Email Template from Form

[ryancgarrett]

I'm trying to create a database that will generate an email template for our customer service department to use. When a customer orders a service from us, there are various documents we need them to upload. I would like a form where the customer service rep selects check boxes for all of the services in a given order, and an email template is generated telling the customer which documents they need to upload.



I have the following tables:

Services
Documents
Customers
Orders

I need to create a 1 to many relationship between services and documents, i.e. each service can require multiple documents, but for some reason Access will only let me do it the opposite way.

Also, I'm unsure how to use check boxes on a form (1 check box for each service) to generate the email template? Any suggestions on how to easily accomplish this?

[June7]

The check box would have to be bound to a field in table.

Then options are:

1. build a report that shows the document info and send email with the report as an attachment

2. build a string in VBA that lists the document info that would serve as email body.

Option 2 requires more complicated VBA code.

If Access won't allow 'opposite way' it is because of the db design and established relationships.

If this is a multi-user database, the db should be split design.

[ryancgarrett]

Thanks for the reply, can you give an example of a field to bind the checkbox to? I'd like to have each record in the table attached to a check box, so the rep can select "Service 1", "Service 2", and "Service 4" if that's what the order contained. I just started building this DB about an hour ago, so not too hard to change the design, or scrap everything if needed.

[June7]

A bound checkbox cannot offer 3 options.

I don't know your data structure or business processes so I have no idea where this field would belong.

However, an alternative might be possible with UNBOUND controls.

All depends on data structure and what data you require to be retained for future reference.

How many documents could be involved for any service?

Do you have a table that associates services with documents?