How to filter one combo boxed based on the selection of another in a continuous form

[jamieciarams]

Hello,

My problem is that I would like to filter one of my combo boxes (ID_format) based on the selection of another combo box (ID_produit).

ID_produit = product name
ID_format= available product sizes
ID_produit_prix = price of the product depending on the size

When a user selects ID_produit, I only want the product sizes that have a price associated withthem to show up in ID_format... but instead all of them show up.

In summary... i want: ONLY SHOW ME THE SIZES THAT HAVE A PRICE ATTACHED TO THEM BASED ON THE PRODUCT SELECTED.
P.S. cascading combo boxes don't work because it is a continuous form. Thank you.

[Gicu]

can you upload a sample db with just the form and associated tables (empty or with a couple of sample records)?

[ranman256]

example: pick car make combo
which filters the car model combo

the cboModel query always looks at the cboMake combo.
cboMake.rowsource=qsModels1Make
qsModels1Make:= select [model] from tCars where [make]=forms!myForm!cboMake

once user picks a car MAKE in the combo box, the MODEL combo resets to that 'make'.
the 2nd combo sql looks at the 1st combo value.

then once the user picks the MAKE, you must refresh the model list using the afterupdate event:

sub cboMake_Afterupdate()
  cboModel.requery
end sub

[jamieciarams]

Is this supposed to work on a continuous form?

[Missinglinq]

A Cascading Comboboxes will work on any type of Form.

Linq ;0)>

[ArviLaanemets]

P.S. cascading combo boxes don't work because it is a continuous form.

Because you use in WHERE clause the value of control instead of value of field!

[jamieciarams]

A Cascading Comboboxes will work on any type of Form.

Linq ;0)>

Well yeah technically it works... but if I select a certain product, the fields in the format category will temporarily disapear if it isn't related to that product. The data remains in the database, but the display will vanish. Any idea on how to make that stop?

[Gicu]

You need to dynamically change the rowsource of the second combobox on the OnEnter and OnExit events:

Private Sub ID_format_Enter()
Me.ID_format.RowSource = "qryID_format_LimitedBy_ID_produit"
End Sub


Private Sub ID_format_Exit(Cancel As Integer)
Me.ID_format.RowSource = "qryID_Format_ALL"
End Sub

This way the combo's rowsource is only limited when it gets activated.

Cheers,
Vlad

[deepakg27]

Dear Jamie,

I was also facing a similar problem. Please refer to my post
https://www.accessforums.net/showthread.php?t=72593&page=2

Could you please be specific, if I am not mistaken you are facing problem, that when selection is done on main field only products relating to those field are displayed in next combo box in all the records, though the data is always there in the database but it shows blank (check images posted by me on my post). And YES Cascading combo box works in continuous form with its own set of problems.

If you try Gicu Solution please update on results, as it would help me out also.

Thanks and Regards
Deepak Gupta

[orange]

Cascading comboboxes -free, older videos from DataPig
http://www.datapigtechnologies.com/f...combobox1.html
http://www.datapigtechnologies.com/f...combobox2.html

Good luck.

[Gicu]

Ahh, to kill two birds with one stone...

Deepak and Jamie, would you please have a look at the attached file and see if that solves your problem?

Cheers,
Vlad

[jamieciarams]

Thanks all. Given my unfamiliarity with code, I decided to go with the text box over combo box hack.

[deepakg27]

Dear Gicu,

Thanks for the code, works perfectly. Hope you could have replied sooner to my post, took me couple of days to figure out the way around it. But your code is better and simpler. Thanks.

Regards
Deepak Gupta

[Gicu]

Glad to hear, sorry I missed your original post!

Cheers,
Vlad

[deepakg27]

No probs thanks for the code, it is such a simple process. Thanks.

Gicu, could you please look into following post and see something can be done:
https://www.accessforums.net/showthread.php?t=72286

Thanks and Regards
Deepak Gupta