Adding Cascading Combo to my Search Form

[rescobar]

TOA_PSG_FAQ_Ver_18.zip

Attached is my zipped database made in Access 2007. Here's my issue. This is a support FAQ database. To my Search form I added combo boxes for Product and Model that write to the unbound text boxes of the same name. Now I'm trying to make them cascade so Model is dependent on Product, this would be the same behaviour as the TOA_PSG_FAQ form. I'm currently stumped.

[Missinglinq]

Your current Table only shows one Model for any given Product. Is this, in fact, always going to be the situation? If you're never going to have more than one Model for any given Product, you don't need Cascading Comboboxes; you simply need to have a single Combobox, with two Columns, one for Product and one for Model. When a selection is made, you then assign the second Column, aka the Model, to a Textbox.

Something like this:

Set up your Combobox using the Wizard and include the Fields you need, from Left-to-Right.

If in the Combobox they appear as

Product | Model

the code would be

Private Sub cboComboBoxName_AfterUpdate()
   Me.txtProduct =  Me.cboComboBoxName.Column(0)
   Me.txtModel = Me.cboComboBoxName.Column(1)
 End Sub

Notice that the column index is Zero-based.

Linq ;0)>

[June7]

Might not even need code. Try expression in textbox ControlSource:

=[comboboxname].Column(1)

[rescobar]

There will be many more than models per product, the table was only like that to make the intial design simple. However now that you have pointed this out I have a problem. Lets take Product Amplifiers Model 9000M2. I add another record for Product Amplifiers Model 900 Series in the Modles Table. Now my Product Combo Box is showing Amplifiers 2 times. DOH! So before I can address the orignal issue I need to find a solution for this new one.

[June7]

Try:

SELECT DISTINCT Product FROM tablename ORDER BY Product;

[rescobar]

TOA_PSG_FAQ_Ver_19.zip

I have attached an updated version.
Open Form TOA_PSG_FAQ
Note that Product Combo box only list a Product once but has more than one for the model.
Inspect the Table Models and there are duplicate entires for Product.
Result: The form TOA_PSG_FAQ is working as I would expect.
Issue I need to duplicate this same combo behaviour with my Search Form.
Open the Search Form and notice the Product is being listed multiple times in the Product field. Also a Product is not have the
cascading abiltity for the Model list like it does with the Form TOA_PSG_FAQ
This where I am stuck

[June7]

mainquery references the textboxes as parameters, not the comboboxes.

Use the same RowSources for Search comboboxes as you used on the other form. And if you want Model to be dependent on Product, use the same VBA code

[rescobar]

TOA_PSG_FAQ_Ver_21.zip

Almost there, my updated DB is attached. Open the Search Form and try the Product Combo box. It will work as expected, Products listed once and enters select item into unbound Product text field. In the Search foem, The Model Combo is blank when it drops down.

[June7]

It's blank because name of product combobox is sProduct but the model combobox references cboProduct in its RowSource. And the product combobox AfterUpdate event also references the wrong combobox names. Copy/paste strikes again?

[rescobar]

I got it working now. Thanks for the help.