Pulling off email addresses from subform

[MichaelWaimauku]

Hi, I have the following that currently takes the email address from the field Contact on a form, however, I now want to be able to pull off multiple emails should there be, from a subform. Is this possible? This is the current one.

.To = Forms![nonconformity]![Contact]

Form - "nonconformity"
Subform - "conf-contX"
Subform field - "contactID" - lookup on another table

The Subform field contactID displays 2 fields of 3 on selection and only stores the 2nd value on the subform. 1st Primary Key (not seen), 2nd Name (displayed), 3rd Email address (only seen when selecting Name).

I want to take the email address which only displays when selecting on the contactID field.

Also, there may be a number of records in the subform, how do I grab all email addresses to populate the .To in Outlook?

Hopefully this makes sense.

 

[Isskint]

Create a recordset that matches the criteria that selects the data in the subform (EG SubForm.ContactID = MainFormContactID). With this you just iterate through the RecordSet copy pulling out the email addresses you want, to your own criteria.

 

[MichaelWaimauku]

Thank you for your help, but I'm new to VBA so I'm not sure of the terminology that you're refering to.

Are you able to help with how this is written? Also, I guess there needs to be some sort of loop that keeps pulling out an email until there are none left, inserting them intot he To: field of email.

 

[Isskint]

Well the first step is to create the recordset. Perhaps the easiest way for you would be to create a query. The query wants to based on the same Source as the subform. You want to restrict the reports results to only those records that are on the subform so, once you have the query, open it in design view and select the first criteria row under the field that links your Main and Sub Forms (ContactID?). You want an expression here something like =Forms![nonconformity]![Contact] (I will presume your original post indicated the main forms name and key field). With the form open run this query and check the results match the forms.

Let me know when the query is done and we can move on to the code to insert the email addresses.