cascading combo boxes in nested subforms

Question

hey guys,

i have been literally bumping my head into walla for the last week.

i am working on a super complicated database with the following:

mainform: frmCust

Subform: frmOrd

subform: frmCamps

Subform: frmProdFam

Subfrom: frmProd

the 5 forms are linked within each other (nested) so frmProd is a subform in frmProdFam and frmProdFam is a subform in frmCamps and so on.

the problem is that i need to synchronize 3 combo boxes:

you choose a campaign type from frmCamps, which needs to filter data is a combo box Product Family in frmProdFam which in returns filters data in combo box producst in frmProd.

usually the formula [forms]![mainforms]![subform]!combobox and the an after update and a requery does the job, but i just cant get it to work in here. any ideas?

Answer 1

There are three processes operating here:

1.  By linking the subform to its parent in the usual way by means of the LinkMasterFields and LinkChildFields properties of the subform control, when you move to a record in the 'master', which might be the main form or a subform which has a subform within it, the value of the foreign key column in every row of the 'child' subform's recordset will be that of the primary key of the current record in the 'master'.

2.  In an unbound combo box in the subform you then just need to reference the foreign key column in the subform's recordset as the parameter in the combo box's RowSource property.  By requerying the combo box in the subform's Current event procedure the combo box's list is restricted to those rows where the value of the column in the combobox's RowSource query matches the current foreign key value in the subform's recordset.  By also setting the value of the combo box to that of the primary key of the subform's recordset the combo box is synchronized with the current record selected in the subform.

3.  The code in the combo box's AfterUpdate event procedure moves the subform to the record selected in the combo box in the way I described earlier.

Answer 2

What the code does is pretty straightforward.  It is based n the fact that in a recordset each row (record) has a Bookmark which identifies that row.  This is not a fixed value, but dynamically assigned.  A form has a RecordsetClone property which is just what it says, an exact clone of the form's recordset.  So the code first finds the row in the clone which matches the value selected in the combo box.  If it finds match it then synchronizes the form's bookmark with that of the row found in the clone, which moves the form's record pointer to the row in question.  By doing this in the AdfterUpdate event procedures of each of the combo boxes in each subform you can navigate to a specific.

One thing I didn't cover in my earlier reply was how you get the combo box in each subform to list only the items which are relevant to the subform's current recordset.  This is similar to correlating combo boxes, but is much simpler to do as you don't need to reference one of the other combo boxes, but the foreign key column in the subform's recordset.  So if we take the frmProdFam subform as an example, the unbound combo box in this would have a RowSource property along these lines:

SELECT ProdFamID, ProdFam FROM ProdFams WHERE CampID = Form!CampID ORDER BY ProdFam;

Note how the Form property is used here to return a reference to the current form object in a very simple way rather than the long syntax of fully referencing the control via the parent form's subform control's Form property.  

You can requery this combo box in  variety of places, but the simplest is probably the subform's event procedure, with:

Me.cboProdFam.Requery

Me.cboProdFam = Me.ProdFamID

This will both restrict the combo box to the relevant ProdFam values and will synchronize it with the current record in the subform.

Answer 3

Thanks a lot Mr. Sheridan,

The code you provided is a little hard for me to understand since i only know VB Basics. but i will go ahead and research it and try to apply it on my data.

Thanks again

Answer 4

With your set-up of subforms you don't need to correlate the combo boxes if each subform is correlated with the one above it in the hierarchy by means of the LinkMasterFields and LinkChildFields properties.  These will ensure that once you navigate to a record in a subform, the subform below it in the hierarchy is automatically requeried to return the records related only to the current selection in the subform above it in the hierarchy.

So what you need to do with unbound combo boxes in each subform is navigate to a record, which you can do with code along the lines of that below in the combo box's AfterUpdate event procedure.  If we take the camps subform as an example, and assume that its recordset is based on a table Camps with a primary key CampID and a non-key column Camp, then the combo box would be set up like this:

RowSource:     SELECT CampID, Camp FROM Camps ORDER BY Camp;

BoundColumn:   1

ColumnCount:    2

ColumnWidths:  0cm

If your units of measurement are imperial rather than metric Access will automatically convert the last one.  The important thing is that the dimension is zero to hide the first column.

The code in its AfterUpdate event procedure would be:

            Dim ctrl As Control

            Set ctrl = Me.ActiveControl

            With Me.RecordsetClone

                .FindFirst "CampID = " & ctrl

                If Not .NoMatch Then

                    ' go to record by synchronizing bookmarks

                    Me.Bookmark = .Bookmark

                End If

            End With

You'd do similarly in the other subforms to navigate to a record.

Edited:  typo in SQL statement corrected