Filtering a form through cascasing combo/list controls

[jackson42]

I have spent almost two weeks and countless hours trying to make this work and am ready to give up. I really need some help.

I have a form that needs to load with all controls null. The user then chooses a project number from an unbound combo box. This selection populates an unbound list box with the description of each record associated with that project. All other controls must remain null. After the user makes a selection from the list box, the remaining controls populate with those associated records. This is essentially my way of letting the user filter down to the desired information through two cascading controls. But it doesn't quite work.

The combo box properly populates the list box, but when I make a selection from the list box, the record navigators display more records that should be loaded, and the remaining controls display the wrong record and seem to be locked to this value - they do not update according to the list box's selection.

The record source of the form is:

SELECT tblQuotations.*, [Forms]![frmQuotations]![lboQuotationNumber] AS Expr1 FROM tblQuotations WHERE ((([Forms]![frmQuotations]![lboQuotationNumber]) Is Not Null));

And the load event is:

Private Sub Form_Load()
Me.cboProjectNumber = Null
Me.lboQuotationNumber = Null
End Sub

The record source of the combo box is:

SELECT tblProjectInformation.ID, tblProjectInformation.ProjectNumber FROM tblProjectInformation ORDER BY tblProjectInformation.ProjectNumber;

And the after update event is an embedded macro:

SearchForRecord ="[ProjectID] = " & Str(Nz([Screen].[ActiveControl],0))
Requery lboName

The record source of the list box is:

SELECT tblQuotations.ID, tblQuotations.EquipmentDescription FROM tblQuotations WHERE (((tblQuotations.ProjectID)=[forms]![frmQuotations]![cboProjectNumber]));

And the after update event is an embedded macro:

SearchForRecord ="[QuotationID] = " & Str(Nz([Screen].[ActiveControl],0))
Requery


I know this is a lot of information but I am hoping there is an expert here who can spot my problem right away. I've lost too much hair trying to make this work and can't tell you how much I appreciate your help.

Thanks!!

 

[pono1]

I understand what you're attempting to do but am confused by the record sources. I have attached a sample which hopefully will get you closer to a solution... Also, I recommend, if at all possible, eliminate the first step in your process. It makes your job more difficult and lengthens the user's journey to the promised land.

Regards,
Tim

 

[jackson42]

Thank you for your post. I haven't tried resetting the record source based on the users selection as in your sample. My VBA skills are lacking but I think the example might help. Unfortunately I don't see a way to eliminate the first step as there are hundreds of projects, each having as many as 15 quotations and I'm not sure how else to help the user easily locate the correct quotation. I will try the technique in your sample shortly. Thanks.