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
|
|||
|
|||
2 subforms linking to a main form
Seems like this should be easy but it's not working for me . . .
I'm creating a db that will contain basic employee address information, the states in which they've worked, and the companies for which they've worked. In this case each employee would only have one current address, etc., but could have worked in multiple states and for multiple companies. I would like to create one form that allows me to enter all of this info at one time. So I've created 3 tables: Name: contains ID (pri key), FirstName, LastName, and other basic address fields State: contains State ID (pri key), ID, and State fields Company: contains Company ID (pri key), ID, and Company fields The ID fields in the State and Company tables have a 1 to many relationship with the ID field in the Name table. I've created a form with FirstName, LastName, State, and Company fields using the design wizard, however when I open the form to enter data, it is blank (all of the fields exist when I switch to design view, however). If I remove the Company from the form, it works as expected with several address fields and a sub-field for entering multiple states. If I create Company as a subform of State, the form contains both subforms, however the report will not work correctly because it thinks that each state has several firms, which isn't necessarily the case. How do I correctly link the two independent sub-tables to the main table? |
#2
|
|||
|
|||
A first observation -- change the name of your table named "Name". "Name"
is a reserved word in Access, so you will only confuse both Access and yourself unless you change it. A common table name for information about persons is ... Person, or tblPerson. You may need to go back to the drawing board (no, literally!). Shut off your computer and grab paper and pencil. Your data structure may benefit from a bit more normalization. Designing the underlying data structure is something you do with paper and pencil, sketching out possible tables and relationships. Based on your description, it sounds like you have persons, states and companies. So far, so good. But you also described what sounds like relationships involving persons and states, and persons and companies. Your current design doesn't have any tables to hold these. For example, if a person can have worked at multiple companies, and the same company could have multiple persons who had worked there, you have a many-to-many relationship. You can only resolve this in Access using a third table, perhaps something like: trelPersonCompany PersonCompanyID PersonID (from the tblPerson) CompanyID (from the tblCompany) DateEmployed (date person started at company) DateLeft (date person left the company) A similar situation would apply if you have a many-to-many relationship between persons and states. -- Good luck Jeff Boyce Access MVP |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Open pop-up form with linked criteria ??? | Dave Elliott | Using Forms | 3 | September 21st, 2004 02:38 PM |
Default values to load up automatically in a form based on value entered in another form | Anthony Dowd | Using Forms | 8 | August 12th, 2004 08:53 AM |
dlookup | miaplacidus | Using Forms | 9 | August 5th, 2004 09:16 PM |
updating a "sub" table from a "main" form | PHIL | Setting Up & Running Reports | 0 | July 29th, 2004 12:54 AM |
Linking Excel data through Access to use a Form as an Interface | Laura | Links and Linking | 0 | March 23rd, 2004 03:59 PM |