I have simplified my earlier design and now have new issues, this time with forms.
I want to set up forms so that the users select from a limited list in certain fields. I have tried combo boxes and list boxes, but when I select a value it either changes the value in that field for another record on the form, or it removes or changes the value from the underlying table. I’ve tried changing the type of relationship, referential integrity, and cascade update with no success. Right now my main issues are setting up three specific forms, all of which need drop-down selections within them.
1) FUNDING FORM
I need a Funding form to include all of the data listed in tblFunding with a few tweaks:
* ProjectNameFK (autonumber from tblProjects.ProjectID) should be visible as ProjectName from tblProjects, rather than the autonumber
* FundingTypeFK (autonumber from tblFundingType.FundingTypeID) should be visible as FundingType from tblFundingType, rather than the autonumber
* FundingSourceFK (autonumber from tblFundingSource.FundingSourceID) should be visible as FundingSource from tblFundingSource, rather than the autonumber
*ALSO, the user needs to be able to enter or change the FundingType and FundingSource from lists (tblFundingType.Funding Type and tblFundingSource.FundingSource). This is where I have been getting really hung up, it keeps changing the underlying Type and Source tables, which I don’t want.
2) PLANNING FORM
I also need a ProjectPlanning form, to include the first 10 fields from tblProjects, where the user can enter the ProjectType from a list that comes from tblProjectType.ProjectType.
3) PERSONNEL ASSIGNMENT FORM
Another user was kind enough to set up an example for Personnel in an earlier version I posted (thanks again, ssanfu!), but I’m just not getting how to translate that into setting up my own from scratch. I’d like to keep the PersonnelPositionProject form for reference, but I’d like to make a new form grouped by project, where I can see a project name (from tblProjects.ProjectName) and under that have a place to select people who have been assigned (combined LastName, First Name from tblPersonnel) and to indicate what their position is (from tblPositions.PositionTitle). There may be one person or several people assigned to any project, but each person will only have one position title.
I know I’m missing some really basic concepts here (please be gentle!), but I’ve been searching and haven’t found an explanation that clicks for me, so I really appreciate any help.
I’ve attached a screenshot of the relationships, and a 2000 version of the database if you'd like to play with the real thing.
Thanks in advance!