nested subform with lookups

Question

I'm trying to create a form with two nested subforms.

The top level form is School and the subforms are Sports and Deliverables

A school can offer zero or more sports.

Each sport can have zero or more deliverables

The school/sports relationship is in a join table called Offered

Offered

id

school id - foreign key

sport id - foreign key

The sport table has:

id

name

School

name

Deliverable

id

Name

The join table linking an offered support to the set of deliverables

OfferedDeliverable

id

offered_ id - foreign key

deliverable_id - foreign key

All id fields are the primary key.

I'm using the form wizard to create the form and subforms in a single pass.

The aim of the form is to allow users to create Schools and associate deliverables and sports

The form should allow the user to select a Sport from a combo box.

I have most of this sort of working.

If I have data already populated in the db it is rendered correctly in the form.

My first problem is that I'm not certain what fields I should select when it asks me what fields I want on the form:

I've tried:

School Name

Deliverable.Name

Sport.Name

This problem is that the sports and deliverable subforms contain tables (which is good) but the sport and deliverable fieds are editable when I want them to be a combobox.

I then change these fields to combo boxes.

This gives me a combobox but they combo box is empty.

The record source for the sports combo is:

SELECT [Sport].[Sport], [Offered].[ID], [Offered].[School_ID] FROM Sport INNER JOIN Offered ON [Sport].[ID] =[Offered].[Sport_ID];

This makes some sense but I'm expecting to have two queries associated with the combo box.

1. the query above which links the combobox to the join table.
2. a second query that populates the contents of the combobox from the Sport table.

I'm I heading in the correct direction?

If so how do I populate the combobox.

And for the record, even though this project is about schools, this is not a school project. Its actually for an organisation that runs sporting events in schools :)

Answer 1

It appears you are heading in the right direction, but you have put some speed bumps in your way.

The Main form should be bound to the School table only. It should only have controls bound to fields in the School Table, plus the subform controls.

When dealing with subforms, I always design the subform separately, then use the subform wizard to embed the existing form on the main form.

I would create the Sports subform using the Form wizard. I would add the Sports field first, then the SchoolID and the Junction table ID (Note I name my PKs tablenameID to make them easily identifiable). Use a tablular layout for form. Then go directly into modifying the form. When you change the textbox control to a combobox, you have to fill in the properties. You have to set a Rowsource, Bound Column, Column Count and Column Widths as a bare minimum. It might be easier delete the textbox control and use the combobox wizard.

You refer to the "record source" of the Sports combo, but combos have a RowSource, not a recordsource. And the SQL Statement you show is incorrect. The RowSource should be:

SELECT SportID, SportName FROM Sports ORDER BY SportName;

(Note: Name is a reserved word in Access and shouldn't be used for an object name. And I wouldn't just use the default ID name for the PK).

I would then Set the Visible property for the ID and SchoolID to No, so the subform only shows the Sport Combo.

For the Deliverables, I'm not sure I'm following what Offered is. If the Deliverable is specific to a Sport, then the SportID should be a foreign key.