Multiple Selections write record for each selection

Our purchasing department wants a database created based on multiple vendor's contacts selected.  I have created the form that they are requesting but I am not sure how to create a drop down list to select the vendors, then have the form list the contacts associated to the vendors selected.  The purchaser than can select the contacts they want to email and have the form save each record in the database by vendor.  

I know I can use a list box with multi select set to simple or extended and use duplicate record but how do I duplicate by the vendor selected.

Hopefully this makes sense.....

Attached sample would be great!!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
you can use a combo box (cboVendor) and a list box (lstContacts allow multiselect - simple)
* in the after update event of the combo box (cboVendor) set the rowsource of the listbox

private sub cboVendor_afterupdate()

dim sql as string
sql="select contacts from tblVendor where vendorId= " & me.cboVendor


end sub

post back if you have any question..
Rey Obrero (Capricorn1)Commented:
set the rowsource of the cboVendor to

Select VendorID, VendorName from tblVendors

set the following properties of the combo

Column Count 2
Bound Column 1
Column Widths 0";1"
Are you familiar with using subforms?

It sounds like you should have a mainform (Vendors -'one')

And a subform (contacts - 'many')

If you follow the tutorial, your subform Contact records will automatically include the vendor (or vendor ID when saved).

To search/filter for a particular vendor from a combo on the mainform you would use the following in the AfterUpdate event of your combo:

Me.Filter = "Vendor ='" & me.cboVendor & "'"

Or if an ID (numeric) is being stored:

Me.Filter = "VendorID =" & me.cboVendor.Column(0)  '<-- assumes the Vendor ID is in the fist column of the combo
Me.FilterOn = True

Open in new window

With that combo, finding the vendor spcified, your subform records will automatically show the contacts associated with that vendor, and allow you to add new contact records that are automatically associated with that specific vendor.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

I agree with mbizup. May be this example can help you
You can also add new contacts if default value of vendorID field in contacts subform will be set to vendorID from vendors subform

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CompTech810Author Commented:
Some really good examples!  It is a many to many relationship.  The purchaser wants to be able to select multiple vendors then select multiple contacts for those vendors to email RFQ's to.  Then the main RFQ file needs to be updated by the number of vendors selected so there is only one RFQ form filled out but a record written for each vendor selected.
Just making sure that I'm following...

Your structure looks like this:

Main (RFQ - one) ----->  Vendors (Many/RFQ)  ----> Contacts (Many per Vendor)

So your mainform (RFQ) would have a child form (Vendors)  and a 'grandchild'  form (Contacts)

Take a look at the sample and the solution posted by the Original Poster in this thread:
CompTech810Author Commented:
Mbizup, als315.   It looks like the example that als315 provided would work because of the query to select a Vendor than select the email, select another vendor then select email, and so on.  Now, on to figuring out how to create a duplicate RFQ record for each vendor selected and then create an email for each contact....
Can you show example of expected RFQ record?
CompTech810Author Commented:
I'm still working on closing this question but because of my lack of knowledge with VBA I'm stuck.

How do I create a simple loop to loop through a query that is based on selected contacts and print an email for each.  I have been able to get Access to do the emails with atachments but because I'm not familiar with VBA I think I'm confusing the system reserved words with what to use:  exp.  I see that people are using: Dim db As DAO.Database Dim rs As DAO.Recordset etc.....
CompTech810Author Commented:
I thought I closed this.  Thanks all!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.