Help with basing one combo box on another in a subform

[docsix]

I am having trouble basing a combo box in a subform that gives selected data from the mainform combo box. Basically combo box1 gives a restricted data set to combo box2.

Here is an small sample of the data, the data comes from two lists.

Table:Facilities -
FacilityID ... FacilityType
1 ............ Bus
2 ............ Library

Table: Additional
DetailID .... Details .......... FacilityType
1 ..............TImetable .......... Bus
2 ............... Sheltered ......... Bus
3 .............. Opening hours .... Library
4 ............. Borrowing ........... Library

The form is saved in a third table called "Saved"
Table: Saved
SavedID .... FacilityID .... DetailID

But this is a single form. I have this working, but I cannot add descritive data for the 'Details' set. I need to make it a subform and have an extra descriptive column to key in the data for the 'details'. I haved looked into the multi-subform solution, but this just makes me have too many tables and too many forms, I like to keep the data in a single table and a single form.

[NeoPa]

In your Additional table you need the FacilityID rather than the FacilityType.
I can't see any reason why you'd want a Saved table.
A SELECT query will happily return the data you want, JOINing the tables if necessary.
If the ComboBox is on the same form then simply refer to it by name.
If on the subform or parent form, then I only know it's less straightforward.

I suggest you give your design a bit of a rethink though. The whole job may work out simpler if you work with the system ;).

[docsix]

In your Additional table you need the FacilityID rather than the FacilityType.
I can't see any reason why you'd want a Saved table.
A SELECT query will happily return the data you want, JOINing the tables if necessary.
If the ComboBox is on the same form then simply refer to it by name.
If on the subform or parent form, then I only know it's less straightforward.

I suggest you give your design a bit of a rethink though. The whole job may work out simpler if you work with the system ;).

You were right about the naming of the column. The Saved table is there to record the changed made from the combo boxes. The main form will also contain at least another 10 more fileds.

Currently all I have is a single form with two combo boxes. Combo box 1 is the facilities and combo box 2 is details. I like to move combo box 2 into a subform and add a second set a data which is keyed in by the user.

When I put combo box 2 into a subform, the populating of that box dosent work. The row source definition that I use on the single form is:

SELECT Additional.DetailID, Additional.Details, Additional.FacilityID FROM Additional WHERE (((Additional.FacilityID)=Forms!Saved!Facility)) ORDER BY Additional.Details;

Also combo box 1 has an after update event

Option Compare Database

Private Sub Facility_AfterUpdate()
Details = Null
Details.Requery
Details = Me.Details.ItemData(0)
End Sub

Private Sub Form_Current()
Details.Requery
End Sub

Private Sub Form_Load()
If IsNull(Facility) Then
Facility = Me.Facility.ItemData(0)
Call Facility_AfterUpdate
End If
End Sub

What do I need to do to make this work in a subform