Weird problem and very frustrating - Known Issue?

[Archie999]

Hi All,

I'm having this problem... hope you can help. Or perhaps this is a known issue with Access (2002).

The problems is with 2 comboboxes on a form that I have. The first combobox gets it's values from a table and presents it to me in the dropdown list. Simple enough and this works.

The second combobox also get's it's values from yet another table BUT it is supposed to filter the values based on the value of the first combobox.

I've had this working a few times but the weird thing is that it keeps 'breaking' even though I have not touched it! When it does not work it has no values in the second combobox - the dropdown list is empty.

Here is my query for the 2nd combobox.

SELECT tblCategory.ProductID, tblCategory.MyCategoryID, tblCategory.CategoryName
FROM tblCategory
WHERE (((tblCategory.ProductID)=[Forms].[frmIssues].[ProductID]));


This is how I seem to get it to work again. Right now there are 3 columns in my query and I only want the 3rd to show so I set the column length to 0" for the first 2 columns. After it breaks, I set the MyCategoryID to show (i.e. set it for 1"). Then my box works again (But with the ID column showing which of course is undesirable). I can then remove this setting and once again hide my ID column and it works... for a while. But then later... it's broken again and around I go again.

Has anyone seen this before or can see anything I am doing wrong. Let me know if you need anymore info.

Would appreciate your input. Thanks in advance!

Arch

 

[Pat Hartman]

If you are trying to use cascading combos in a continuous form, you will have a problem. I wrote at length about this in a previous post.

I guess your tables are set up with ProductID being a parent of CategoryID but most people would think that relationship to be backwards. Category would be something like Tea and Product would be something like Earl Grey or Green.

 

[Archie999]

I'll check out threads on cascading combos

Thanks Pat,

My form is not continuous - but I will check out previous posts about cascading combos for some hints.

BTW - My DB logic does in fact seem backwards but it's really not. It makes perfect sense when you know the full story. Thanks for noticing though