Two Sub-Form Problems

[Keeper]

I'm creating a database which will have information about training events. Two of the fields are lists. The first list field will contain up to 20 performance steps (tasks necessary to complete the training event). This list is non-standard (i.e. none of the individual tasks, let alone the whole list, will be repeated across other training events). The second list will contain up to 5 references. This list can be can standardize (the references will appear across various other training events).I need help figuring out how to create the form for my Events Datasheet; specifically the two lists. I’ve never used sub-forms before. I should note I do not anticipate any of the records changing once they’ve been entered into the database.My idea is to be able to add multiple entries for the Performance Steps field. When I make an entry in the field and hit return, a new blank line would appear. Kinda like the table was embedded directly in the form. I’m not sure if this needs to be a separate table from the main table. The Event Code is the key field, and will have multiple Performance Steps associated with it. As previously mentioned, the performance steps will not be duplicated on other event code records (at the least the chances of this are remote).For the References field, I envision being able to select from previously recorded entries (presumably stored in a separate table) or making new entries. Again, this might look like an embedded table like the Performance Steps list.

[June7]

What exactly is it you are confused about?

How to build form/subform?
http://office.microsoft.com/en-us/ac...010098674.aspx

How to build combobox RowSource?
I don't understand "none of the individual tasks, let alone the whole list, will be repeated across other training events". What will be the source for the combobox list?

[Keeper]

Here's an example record:

Event_Code (Key Field): 1234-ABCD-5678
Name: This Is The Name Of The Event
Eval_Code: No
Sustainment_Period: 06 Months
Description: This is a short 1-3 sentence description of the event.
Condition: This is a 1-2 sentence declaration of the parameters of the event.
Standard: This is a short 2-3 sentence outline of the expected outcome of the event.
Performance_Steps:

1. This is a description of a task.
2. This is a description of another task.
3. This is a description of a different task.
4. This is a description of the last task.

References:

1. Manual ABC
2. Manual XYZ
3. Manual 123

As you can see, the Performance_Steps and References fields are lists. The list items in Performance_Steps are almost entirely unique, as in they are unlikely to appear under other Event_Code records. I don't know if this needs to be a completely seperate table, linked to the main table. The items listed in the References field will definately appear under multiple Event_Code records. It seems logical to make this a seperate lookup table which can be built up.

Beyond the question of whether or not to have three seperate tables in my db, I don't know how to build the form and incororate sub-forms for these two lists (I'll read over the link you provided). With regard to the References list, I'd like to be able to have the option to either select an existing reference previously recorded, or enter a new reference (this is not the case for the other list). The whole list needs to appear though, as it gets built (the is true for the Performance_Steps list as well).

[June7]

If you use a lookup table for references, explore use of the NotInList event of combo and list boxes. If the RowSource is a DISTINCT query of the field in Events table, just requery the combobox after each entry - use the combobox AfterUpdate event: Me.comboboxname.Requery.

Still not understanding the Performance list. If the Performance items are unique for each event, why have a combo or list box? Why not just a freeform entry text field?

[Keeper]

Still not understanding the Performance list. If the Performance items are unique for each event, why have a combo or list box? Why not just a freeform entry text field?

Once the database is created, it may be used later to record completion of certain tasks. I have a separate db which records personnel information. The two would be linked together to track individual completion of tasks (mind you, I haven't even begun to think of how to do this ). The training event is not complete until all tasks are checked off. Therefore, I'd like to record each task independently.

[June7]

Yes, each task is recorded with the event, right? Still doesn't explain how a combobox is useful for the Performance field when entering event record.

Actually, data normalization would mean child tables for the Performance and Reference info.

[Keeper]

What will be the source for the combobox list?

For the References field, I've broken this out to a seperate table.

I've walked through the instructions on the link you provided and tried adding the sub-forms. I haven't been able to get them to work they way I want though. First, I can't figure out how the main table is linked to the lookup table.

[June7]

Main table might not need to be 'linked' to lookup table. But a combobox would have a RowSource that pulls values from the lookup table.

Maybe you don't have the data structure correct. Consider:

tblEvents
EventID (primary key)
etc.

tblReferences (this could be lookup table for combobox RowSource)
ReferenceID (primary key)
ReferenceName

tblEventReference (this would be on subform)
ReferenceID (foreign key)
EventID (foreign key)

tblEventPerformance (this would be on subform)
EventID (foreign key)
PerformanceDescription

[Keeper]

Thanks for the continued assistance. I've attached a copy of the db so you can see where I'm at. I've also attached a spreadsheet to show a couple records wtih the lists included (since this info is missing from the db at this point).

In regards to your last post, I have a couple questions.

1. Why are there two References related tables?
2. I don't understand the foreign key situation. I've never used these before. (edit: I've read up on this and get it now. I'm still not good at building relationships with the dialogbox when linking two tables).

In my db, open the frm_Datasheet and you'll see there are three tabs which are my attemps at playing around with the lists and subforms. Tab 2 is the sub form which is where I would like to record a list of tasks. Tabs 2&3 are different attemps are setting up the references list. This is where I would like to build a list using a combo box, whereby I can either choose a previously recorded reference or I can enter a new one.

[June7]

1. There is one table that is a list of all possible references that can be used as a source to limit user selections when creating records that associate Event with references needed for that event. Then there is a table for records to show the association of an event with its needed references. Your db does not have a table for this.

2. At heart of relational database principles is concept of relating records by associating fields in different tables that have common values (primary key and foreign key). Must understand this before proceeding. Review
http://forums.aspfree.com/microsoft-...es-208217.html
http://www.developerbarn.com/communi...-database.435/

A main reason for the relational concept is to avoid repetition of data input. This makes data entry faster and data storage more compact.

[Keeper]

There is one table that is a list of all possible references that can be used as a source to limit user selections when creating records that associate Event with references needed for that event. Then there is a table for records to show the association of an event with its needed references.

Are you refering to a junction table? I don't want to limit imput to just one standard list. As I build out the database, or references change, I want to update the Reference list. As I populate the database, I don't want to have to type the same reference in over and over. I want to choose from an existing list or add a new reference.

I've built out the tables as you previously described. In the Relationships Tool, I created the relationships so the primary and foreign keys match up as you've outlined. I'm unclear about the direction of the relationship though.

[June7]

Yes, 'junction' table is one way to put it.

Relationships look appropriate.

tbl_References will associate many references with each event but the references to choose will be presented in a combobox. This will control uniformity in data as the same references can be related to multiple events. My suggestion does not limit the references. Records can be added to the references lookup table to offer more choices. Use combobox box to present the choices for the record in tbl_References, which I had suggested be named tblEventReferences.

tbl_Tasks will also associate many tasks with each event but there is no standardization in the data input. Totally free-form entry for each Performance_Step description.