How to allow users to lookup and select records for display in a subform

I am new to MS Access 2007 but worked with an xBase product named Alpha 4
over 20 years ago; needless to say I am a novice when it comes to RDBMS
design and development. I am attempting to design a simple tracking system
for a small consulting unit which tracks information regarding the unit's
projects.

I have three tables:
(1)‘Engagement’ table which holds records with fields containing data
specific to the consulting project (i.e. project start date, project end date,
project type, etc.).
(2)‘Contacts’ table which holds records of people who are associated with a
specific project including customer-side folks, consultants within my small
team and third party contractors if needed. Fields are typical contact
fields including Last Name, First Name, Company, Cell Phone, etc.
(3)'Engagements and Contacts (a Transition Table) which is on the 'many' side
of a one to many relationship w/'Engagement' table via the Engagement Table
Primary key (Engagement ID). This table is also on the 'many' side of a one
to many relationship w/'Contacts' table via the Contacts table Primary key
(Contact ID).

I’m attempting to develop a Form which:

(1) lists information from the single Engagement table on the top part of
the form and
(2) lists multiple records from the Contacts table (I'm assuming adding a
subform is the proper approach) of the various people associated with the
single engagement. Ideally I’d like the user when populating the subform be
able to search for a contact name by last name, first name and company within
a drop down box and if present, select it and have the related information
(cell phone, title, etc.) listed within the subform area.

The associated process would work as follows:
1) User opens Engagement Form (based on Engagement Table) and enters
information specific to the engagement (project start date, project type, etc.
)
2) User would then enter the subform area and lookup and select the specific
contacts associated with the engagement. These contacts will most likely
grow as the project progresses. If the Contact isn't listed within the
lookup function, the user can exit the engagement form and populate a form
for the contact table.

My initial thought is to create a Form based on the ‘Engagement’ table with a
SubForm section for the ‘Contact’ table. Specific to contacts, my problem if
I go this route is how do I create the search/lookup functionality to ensure
a user doesn’t enter a contact twice? I think the correct way to go would be
to use a combo box, but I'm not sure how to do this.

Any guidance is appreciated.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200904/1

I find a working sample to be a good way to figure out how to do things.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ImplementingM2MRelationship.mdb" which I think will answer
your questions. You can find it he
http://www.rogersaccesslibrary.com/f...ts.asp?TID=342

--
--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/...UBED1=ACCESS-L


"DonAdd via AccessMonster.com" u51371@uwe wrote in message
news:9508854f6fd84@uwe...
I am new to MS Access 2007 but worked with an xBase product named Alpha 4
over 20 years ago; needless to say I am a novice when it comes to RDBMS
design and development. I am attempting to design a simple tracking
system
for a small consulting unit which tracks information regarding the unit's
projects.

I have three tables:
(1)'Engagement' table which holds records with fields containing data
specific to the consulting project (i.e. project start date, project end
date,
project type, etc.).
(2)'Contacts' table which holds records of people who are associated with
a
specific project including customer-side folks, consultants within my
small
team and third party contractors if needed. Fields are typical contact
fields including Last Name, First Name, Company, Cell Phone, etc.
(3)'Engagements and Contacts (a Transition Table) which is on the 'many'
side
of a one to many relationship w/'Engagement' table via the Engagement
Table
Primary key (Engagement ID). This table is also on the 'many' side of a
one
to many relationship w/'Contacts' table via the Contacts table Primary key
(Contact ID).

I'm attempting to develop a Form which:

(1) lists information from the single Engagement table on the top part
of
the form and
(2) lists multiple records from the Contacts table (I'm assuming adding
a
subform is the proper approach) of the various people associated with the
single engagement. Ideally I'd like the user when populating the subform
be
able to search for a contact name by last name, first name and company
within
a drop down box and if present, select it and have the related information
(cell phone, title, etc.) listed within the subform area.

The associated process would work as follows:
1) User opens Engagement Form (based on Engagement Table) and enters
information specific to the engagement (project start date, project type,
etc.
)
2) User would then enter the subform area and lookup and select the
specific
contacts associated with the engagement. These contacts will most likely
grow as the project progresses. If the Contact isn't listed within the
lookup function, the user can exit the engagement form and populate a form
for the contact table.

My initial thought is to create a Form based on the 'Engagement' table
with a
SubForm section for the 'Contact' table. Specific to contacts, my problem
if
I go this route is how do I create the search/lookup functionality to
ensure
a user doesn't enter a contact twice? I think the correct way to go would
be
to use a combo box, but I'm not sure how to do this.

Any guidance is appreciated.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200904/1

Thanks for the prompt assistance. I've downloaded the samples and will
review them tomorrow.

Roger Carlson wrote:
I find a working sample to be a good way to figure out how to do things.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ImplementingM2MRelationship.mdb" which I think will answer
your questions. You can find it he
http://www.rogersaccesslibrary.com/f...ts.asp?TID=342

I am new to MS Access 2007 but worked with an xBase product named Alpha 4
over 20 years ago; needless to say I am a novice when it comes to RDBMS
[quoted text clipped - 61 lines]

Any guidance is appreciated.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200904/1

Thanks for providing the working sample. I now understand creating a form
based on a query and incroporating it into the main form is the proper way to
accomplish my goal and have done so. My only remaining task is to create a
combo box which provides the lookup functionality. Wish me luck.

Roger Carlson wrote:
I find a working sample to be a good way to figure out how to do things.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ImplementingM2MRelationship.mdb" which I think will answer
your questions. You can find it he
http://www.rogersaccesslibrary.com/f...ts.asp?TID=342

I am new to MS Access 2007 but worked with an xBase product named Alpha 4
over 20 years ago; needless to say I am a novice when it comes to RDBMS
[quoted text clipped - 61 lines]

Any guidance is appreciated.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200904/1