Cascading Combo Box and Drop Down Pick List empty when tables are linked

[stevebp]

Hello

To set the scene I am a complete Access novice and I have no understanding of writing code, only using the MS tools and wizards available.

I have an issue where I am attempting to create a cascading combo box based on a 3 tables; Orders, Products and Product Category. When I choose a Product Category I want it to just show the products available to the category chosen. My problem is that when I enter the criteria in the Query Tools, it fails to show the Product Categories. I have spent hours trying to get this to work, without success. I am sure it is a simple thing I am getting wrong..

The tables that have look up links to tblOrders and field ProductName and ProductCategory


The form shows categories in both lists


When the criteria is entered to enforce the combo box restricted what categories are shown, the pick list is then blank



Empty pick list




Thanks if anyone can point me ion the right direction.

Regards

Steve

[pbaldy]

Maybe this will help:

http://www.baldyweb.com/CascadingCombo.htm

i would expect the category combo to reference the name combo or vice versa. You appear to have it referencing itself.

[stevebp]

Thanks Paul

I have also tried this before by changing to the ProductsName, but I get the same issue.

I have seen on another thread on this forum that talks about not having picklists/lookups in tables, so I may try changing this to see if that has an effect.

Regards

Steve

[pbaldy]

I'd expect that criteria to be on the name field. That appears to be comparing apples to oranges. Can you attach the db here?

[stevebp]

Hi Paul

Thanks for this, unfortunately it is too big 1.7mb, there is a limit of 500kb.

I have a copy on Onedrive that I could share with you if I had your email?

Kind regards

Steve
PS
Now inactive until tomorrow.

[pbaldy]

The limit should be 2MB if zipped.

[stevebp]

Contacts.zip

Thanks again Paul, filke zipped and attached, although it does not clear if it has worked?

Regards

Steve

[pbaldy]

I think you had it backwards, see if this does what you want.

[stevebp]

Thank you so much Paul, that has sorted it and you also kindly populated the requery.

Lots of respect for people like yourself who go to the trouble of sorting out problems like this in your own time.

Thanks again.

Kind regards

Steve

[pbaldy]

Happy to help Steve!

[stevebp]

Hi Paul

Apologies for contacting you again, but whilst the individual form works when I embed in another one (Contact Details) as per the screen shot, it errors with a parameter value against the ProductsCategory. Spent a few hours trying to resolve, without any success! Are you able to take a quick look when you have time.


Contacts with forms.zip

Thanks and regards

Steve

[pbaldy]

The syntax changes when it's a subform:

http://www.theaccessweb.com/forms/frm0031.htm

but you'll find cascading combos don't work as well in a continuous form.

[stevebp]

Ok thanks Paul.

Regards

Steve

[mike60smart]

Hi Steve

I modified how ProductCategory is related to Products. - See the relationship diagriam.

Your Contact Details form now works as it should

[pbaldy]

Just make sure not to requery the form during data entry.