If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
How to allow users to lookup and select records for display in a subform
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 |
#3
|
|||
|
|||
How to allow users to lookup and select records for display in a subform
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 |
#4
|
|||
|
|||
How to allow users to lookup and select records for display in a subform
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 |
Thread Tools | |
Display Modes | |
|
|