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
|
|||
|
|||
Is is possible to use a Drop down box in form to populate 2 fields
I have 3 tables set up for company, contact and project. Using these tables
I have a tab form that has three tabs (company, contact and projects). These are used to enter data 1st with the company info, then the contact name info and finally projects for that contact working for the company. One company can have many contacts and the contacts can have many projects. On the projects tab in a sub form I have a drop down box that comes from a query using the contact table (includes 2 fields, contact name and id number). This is for selecting the specific company contact that is working on the project. Once the contact is selected how do I store the id number in a seperate field to run queries off of that field. Currently the dropdown box works and shows the name and id but I can't seem to use the id number to run other queries for reports. Any help? -- LoveJeeps |
#2
|
|||
|
|||
Is is possible to use a Drop down box in form to populate 2 fields
So you have 3 tables, and in the Relationships window you set up
relationships like this: Company table: CompanyID AutoNumber primary key CompanyName Text Contacts table: ContactID AutoNumber primary key ContactName Text CompanyID Number relates to Company.CompanyID Projects table: ProjectID AutoNumber primary key ContactID Number relates to Contacts.ContactID ContactName Text Firstly, if you do have the ContactName in the Projects table, remove it. You are making a rod for your own back if you have the same name repeated in multiple projects. You are guaranteed to get some bad data in this table at some point, where the ContactID and ContactName doen't match. Avoid the errors by storing the ContactID only. Now your question is: when entering projects, can you use a combo box for the ContactID? Yes: place a combo on the projects form, and give it properties like this: Control Source ContactID RowSource SELECT ContactID, ContactName FROM Contacts ORDER BY ContactName, ContactID; Bound Column 1 Column Count 2 Column Widths 0 The combo contains 2 columns. The first one (the ContactID) is zero-width, so Access displays the second one (the contact name) When you choose a name, it stores the ContactID (since the first one is the bound column.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "lovejeeps" wrote in message ... I have 3 tables set up for company, contact and project. Using these tables I have a tab form that has three tabs (company, contact and projects). These are used to enter data 1st with the company info, then the contact name info and finally projects for that contact working for the company. One company can have many contacts and the contacts can have many projects. On the projects tab in a sub form I have a drop down box that comes from a query using the contact table (includes 2 fields, contact name and id number). This is for selecting the specific company contact that is working on the project. Once the contact is selected how do I store the id number in a seperate field to run queries off of that field. Currently the dropdown box works and shows the name and id but I can't seem to use the id number to run other queries for reports. Any help? -- LoveJeeps |
Thread Tools | |
Display Modes | |
|
|