Data Entry into Many-to-Many forms in Access 2007

S

Saji Ijiyemi

I am developing a partners database for a my company

tblPartners
PartnersID - PK
ManagerID - FK
LastName
FirstName
CompanyName
EmailAddress
JobTitle
BusinessPhone
Ext
MobilePhone
FaxNumber
SuiteNo
Address
City
State/Province
ZIP/Postal Code
Country/Region
WebPage
CompanyQuals
Notes
MainTel
MainFax

tblManager
ManagerID - PK
Manager

tblBusinessSize
BusinessSizeID - PK
BusinessSize
PartnersIDFK

tblCertification
CertificationID - PK
CertificationType

tblPartnersCertification
CertificationID - FK
PartnersID - FK

tblNAICSCode
NAICSCodesID - PK
NAICSCode
Description

tblPartnersNAICSCode
NAICSCodesID - FK
PartnersID - FK

tblCapability
CapabilityID - PK
Capability

tblPartnersCapability
CapabilityID - FK
PartnersID - FK

tblClients
ClientsID - PK
Office
Description

tblPartnersClients
ClientsID -FK
PartnersID - FK

tblContractVehicle
ContractVehicleID - PK
ContractVehicle

tblPartnersContractVehicle
ContractVehicleID - FK
PartnersID - FK

1) I need to design a form where For each Partner (tblPartner), a user can select one or multiple field from:

* tblCertification
* tblNAICSCode
* tblCapability
* tblClients
* tblContractVehicle

2) I need a report where users can query the database via a form-like interface

Note: tblPartnersCertification, tblPartnersNAICSCode, tblPartnersCapability, tblPartnersClients, tblPartnersContractVehicle are all Junction tables

I have done several experiments with subforms and read countless many-to-many advise but still frustrated, I need a savior.

Thanks



EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorial...f-32b2d802ae17/wpf-datagrid-custom-pagin.aspx
 
R

Roger Carlson

You don't really tell us what you've tried or where your frustration comes
from. But I'd like to suggest a few samples from my website that might
help.

ImplementingM2MRelationship.mdb
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=342
(Method 2 only)

ReallyBadDesignDecisions.doc
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=291
(there are two databases, one shows you how NOT to design a form with
multiple many-to-many relationships and the other shows you how to fix it.)

ChooseReportFieldsRows.mdb
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=384
This sample very roughly illustrates how to create a query programmatically
which chooses the FIELDS to be reported on a report based on the selected
values of a multi-select list box and then allows the user to specify the
ROWS to be reported.

Also, Duane Hookom's "Query By Form"
http://www.access.hookom.net/Samples.htm

Hopefully I haven't just added to your frustration.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
K

KARL DEWEY

Your --
tblBusinessSize
BusinessSizeID - PK
BusinessSize
PartnersID - FK
will allow only one partner for a particular size business. You need
a union table like the other attributes.

I think this form setup would work for you --
Main form - tblPartners.PartnersID (not visible), other partner information

Subform Certification - CertificationID (not visible), CertificationType
(Combo)
Master/Child links - tblPartners.PartnersID,
tblPartnersCertification.PartnersID

Subform NAICSCode - NAICSCodeID (not visible), NAICSCode (Combo), Description
Master/Child links - tblPartners.PartnersID, tblPartnersNAICSCode.PartnersID

Subform Capability - CapabilityID (not visible), Capability (Comobo)
Master/Child links - tblPartners.PartnersID, tblPartnersCapability.PartnersID

etc...
 
S

sAji

Your --
tblBusinessSize
BusinessSizeID - PK
BusinessSize
PartnersID   - FK
       will allow only one partner for a particular size business.. You need
a union table like the other attributes.

I think this form setup would work for you --
Main form - tblPartners.PartnersID (not visible), other partner information

Subform Certification - CertificationID (not visible), CertificationType
(Combo)
Master/Child links - tblPartners.PartnersID,
tblPartnersCertification.PartnersID

Subform NAICSCode - NAICSCodeID (not visible), NAICSCode (Combo), Description
Master/Child links - tblPartners.PartnersID, tblPartnersNAICSCode.PartnersID

Subform Capability - CapabilityID (not visible), Capability (Comobo)
Master/Child links - tblPartners.PartnersID, tblPartnersCapability.PartnersID

etc...
Click to expand...

Thanks all.

I've created the necessary tables, forms and subforms I needed. This
is a great forum and Mike Infiesto has been of great assistance. What
I did was in line with what KARL explained, though I have
BusinessSizeID inside my tblPartners, hope it'll give the same result.

The next two challenge is to:

1) Tweak my subform so to prevent users from selecting duplicate field
from each attributes (certification, client, Capabilities, etc).
Currently if I select "Certification 1" from tblCertification, If I
clikc on the next field on the subform, I would still be able to
select "Certification 1" for the same PartnerID. I want prevent
duplicates and possibly have a MsgBox alerting users that they have
already selected "Certification 1"

2) Create a form-based report that will allow users combine different
criteria and generate a report on the fly based on their criteria. I
was thinking I could use the same form design?

Thanks.
 
Top