Form design--lists and FKs

[wes]

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!

[June7]

1. Funding Form
Use multi-column comboboxes, just as was done in frmPersonnelPerProject.
I don't understand how values in Type and Source tables could be changed. Those tables are not included in the form's RecordSource.

2. Planning Form
Form RecordSource would be tblProjects, use multi-column combobox for ProjectType

3. Personnel Assignment Form
This would need to be a form/subform arrangement. Main form bound to tblProjects, subform bound to tblPersonnelPositionProject, Master/Child linking on the ProjectID fields.

[wes]

1. Funding Form
Use multi-column comboboxes, just as was done in frmPersonnelPerProject.
I don't understand how values in Type and Source tables could be changed. Those tables are not included in the form's RecordSource.

I had set up a Type list and a Source list, which drew from the Type and Source tables directly, so those tables would have been the RecordSource. So do I need to create a new table that combines Type and Source, and use that new table as the RecordSource for the combobox?

I'm still unclear on one functional piece: when I set up a form from a table that has autonumbers, how do I get the form to show me the text value in the associated field rather than the number?

Thanks also for the great explanation for 2 and 3, I'll give it a try!

[June7]

Look at the comboboxes in frmPersonnelPerProject. They are multi-column. First column is the ID field, it is hidden because width is set to 0. This allows the second column which is the text value to display in the combobox. But because the BoundColumn is column 1 it is the ID value that is saved to table. Look at combobox properties:
RowSource
BoundColumn
ColumnWidths
ColumnCount

Other properties of interest
LimitToList
AutoExpand

And the NotInList event can be useful.

[wes]

Ah, that is exactly what I wasn't seeing (literally!), thanks for the explanation! Now I think I get what I was missing. I can't wait to give it a try.

[ssanfu]

I looked at your database. The main problem I see with your forms is that you are using tables as the record source instead of queries. For the form "frmFunding", you need to have a query with 4 tables (look at the record source).
If "frmFunding" is what you want, then we can start working on fixing the other forms.

I haven't done anything with the other forms because I have a few questions.

---------

1) for table "tblFunding", why do the entries have a number as the first char? ex.: 4Index

2) same question for "tblFundingSource". ex.: 2Contract

3) In table "tblProjects", you have approx 29 date fields. It looks to me that these should be in their own table, linked back to the projects table. Do/will all date fields end up with dates?

Also, in "tblProjects", "FORMAT' is a reserved word and shouldn't be used as an object name.

[wes]

I
1) for table "tblFunding", why do the entries have a number as the first char? ex.: 4Index

2) same question for "tblFundingSource". ex.: 2Contract

3) In table "tblProjects", you have approx 29 date fields. It looks to me that these should be in their own table, linked back to the projects table. Do/will all date fields end up with dates?

Also, in "tblProjects", "FORMAT' is a reserved word and shouldn't be used as an object name.

Hi again ssanfu!

Yes, FrmFunding is what I was picturing, with the additon of list/combo boxes for Source and Type. Is it possible for the entries to be grouped by project so the project name is only visible once, and below the project name the user can enter the data for the other fields? This may not work, as some projects will have a single type and source, and others may have three or four, so the tabular format may be the way to go, which is fine, in reality I'll probably be the only one entering the financials.

I also realized one more thing, in my sample db all of the projects have related financial data, but in reality we will have many that have no financial data. Do I just add to the underlying query a field for FundsNeeded = Yes from tblProjects to ensure that the form only shows projects that need money?

1 and 2) I know the numbers preceding the text in the Source and Type tables are dumb, I was going to take them out before I entered real data. The short story is I was using them as a sanity check to compare the outcome of my attempts at form design with the real data in the tables, but that serves to embarassingly highlight my newbie-ness. They can come out now.

3) I used to have tblProjects, which had the overview information (the first 10 fields, through and including the check boxes), and tblMilestones, which had all of the fields where the user could enter dates when a milestone was reached. Then I thought that they should be combined as they are all items related to a single project. But they are different types of information so it probably makes sense to have them as separate tables.

Regarding the dates, not all the date fields will be completed for each project. Some projects will have as few as two date fields filled out, others will have more, depending on the individual project.

I hope this helps, thanks so much for working with this!

[June7]

I disagree that binding to table is an issue for the Funding form. What is really needed is to change the textboxes to comboboxes.

And if you want records grouped by project and show the projectID only once, easiest method is to do form/subform arrangement.

[ssanfu]

Hi again...

Yes, FrmFunding is what I was picturing, with the additon of list/combo boxes for Source and Type. Is it possible for the entries to be grouped by project so the project name is only visible once, and below the project name the user can enter the data for the other fields? This may not work, as some projects will have a single type and source, and others may have three or four, so the tabular format may be the way to go, which is fine, in reality I'll probably be the only one entering the financials.

I also realized one more thing, in my sample db all of the projects have related financial data, but in reality we will have many that have no financial data. Do I just add to the underlying query a field for FundsNeeded = Yes from tblProjects to ensure that the form only shows projects that need money?

Look at the mdb now. How is this?

1 and 2) I know the numbers preceding the text in the Source and Type tables are dumb, I was going to take them out before I entered real data. The short story is I was using them as a sanity check to compare the outcome of my attempts at form design with the real data in the tables, but that serves to embarassingly highlight my newbie-ness. They can come out now.

I removed them. I frequently do things similar to check out relationships, forms, linking, .... more than a newbie technique!!

[wes]

I disagree that binding to table is an issue for the Funding form. What is really needed is to change the textboxes to comboboxes.

And if you want records grouped by project and show the projectID only once, easiest method is to do form/subform arrangement.

By jove, I think I've got it! I was able to do everything I wanted with comboboxes drawing from tables, rather than queries.

I was able to use tblFundingType and tblFundingSource directly in the comboboxes for my funding form.

For the personnel forms I used the combined table tblPersonnelPositionProject for the combobox, with a select query for the record source
(SELECT tblPersonnelPositionProject.ProjectID_FK, tblPersonnelPositionProject.PositionID_FK, tblPersonnelPositionProject.PersonnelID_FK, tblProjects.ProjectName).

I set up subforms so I could view both funding and personnel assignments by project. In testing everything seems to work great!

This was my first foray into comboboxes and forms, thank you both for the pointers that put me on the right track.

[ssanfu]

By jove, I think I've got it! I was able to do everything I wanted with comboboxes drawing from tables, rather than queries.

Wonderful!

As a side note - I use queries as row sources instead of tables because you can set criteria to limit the rows returned and queries can be sorted - tables cannot.