Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Multiple Selections write record for each selection

Posted on 2012-03-29
11
Medium Priority
?
298 Views
Last Modified: 2012-07-16
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!!
0
Comment
Question by:CompTech810
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37781850
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

me.lstContacts.rowsource=sql

end sub


post back if you have any question..
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37781869
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"
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 664 total points
ID: 37781910
Are you familiar with using subforms?
http://office.microsoft.com/en-us/access-help/create-a-form-that-contains-a-subform-a-one-to-many-form-HA010098674.aspx

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.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 40

Assisted Solution

by:als315
als315 earned 1336 total points
ID: 37782085
I agree with mbizup. May be this example can help you
DBcontacts.mdb
0
 
LVL 40

Accepted Solution

by:
als315 earned 1336 total points
ID: 37782133
You can also add new contacts if default value of vendorID field in contacts subform will be set to vendorID from vendors subform
DBcontacts.mdb
0
 
LVL 2

Author Comment

by:CompTech810
ID: 37782138
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.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37782300
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:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27512608.html
0
 
LVL 2

Author Comment

by:CompTech810
ID: 37787663
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....
0
 
LVL 40

Expert Comment

by:als315
ID: 37790078
Can you show example of expected RFQ record?
0
 
LVL 2

Author Comment

by:CompTech810
ID: 37795774
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.....
0
 
LVL 2

Author Closing Comment

by:CompTech810
ID: 38190166
I thought I closed this.  Thanks all!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question