3 combo boxes in 3 subforms

[reham]

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, 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?

 

[rzw0wr]

The first combo box is filled with the data to filter the 2nd combo box and the 2nd combo box is the filter for the 3rd combo box using the WHERE clause.

To be more specific we would need more information as to what data you are using and exactly what you want to filter.

Dale

 

[reham]

well the three combo boxes are bounded combo boxes, located in three nested subforms.

row source is a query:

cbocampaign: SELECT tblCampList.CampID, tblCampList.CampName FROM tblCampList WHERE (((tblCampList.Active)=True)) ORDER BY tblCampList.[CampName];

Same things goes for cboProdFamily and cboProduct .

the cboCampaign has the list of campaigns available, if the user to chooses "Winback" for example, he needs to have 4 Family Products in the cboProdFamily Retention, LDP, DSL and Mobile. once the user select a family product (lets say LDP), a cboProduct dropdpwn needs to filter the products related to LDP only.

My problem is how to refereance the nested subforms in my queries. i have attached a Picture of my form:

as you can see, the customer information box is the main form (frmCust)
the order information box is a subform in frmCust (frmOrd)
the campaign drop down is in the frmCamp which is a subform to frmOrd
the Product Family drop down is in the frmProdFam which is a subform to frmCamp
the Product drop down is in the frmProd which is a subform to frmProdFam.

I would really really really appreciate any ideas

 

[rzw0wr]

Unbind your combo boxes.
2nd box would be something like ,
WHERE tblCampList.CampID = The ID of your third form.;
If they are linked by ID and NOT name.

Dale

 

[reham]

i am sorry for the misunderstanding, the combo boxes ARE unbounded. and yes they are linked by ID and not name. i will go ahead and try more WHERE statements see if it works. i appreciate the reply though

 

[JHB]

If you can't get it to work, then post your database with some sample data, (zip it because you haven't post 10 post yet).

 

[reham]

hey guys i am still having troubles with this silly filtering combo boxes thingy.

I just cant find a way to link the forms !
a gentleman suggested that i use

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

in afterUpdate event, but i didnt get it to work.

any ideas?