Cascading combo boxes working yet not working

[MangoFruit]

Hi all,

I'll try to explain this as clearly as possible:
I have a database consisting of 4 tables, each containing items of a certain type. I also have a 5th table which is a join table, because different combinations of items have specific properties (e.g. 1 item is 50 cm long, the other is 60 cm long, yet together they are nog 110 cm long but only 90 cm long). We use the join table to store the combined dimensions and weight. Then I made a qry that simply displays all fields from all 5 tables.
Now we have a form based on that query with 4 combo boxes to choose 1 item from each table and then we want it to give both the dimensions and weight of each individual item, and the combined dimensions and weight of all 4 together. I made the combo boxes cascading by putting the following in the row source of the 2nd combo box:
SELECT [qry Database].[ItemCategory2 Code] FROM [qry Database] WHERE ((([qry Database].[ItemCategory1 Code])=[forms]![frm MainForm]![cbo ItemCategory1 Code])) GROUP BY [qry Database].[ItemCategory2 Code];

And for each following combo box I added an extra 'where' clause. This actually seems to work, whenever I select an item in combo box 1, the choice for the second combo box is correctly narrowed down, then the same goes for combo box 3 and 4.
Now comes the problem: I want to display the dimensions and weight for each of the 4 chosen items (so not the combined dimensions and weight). However this is not working. When I choose the first item I get the correct info for that item. When I choose the second it displays the correct dimensions for that one but not always for the first one. I think it chooses the first record that contains the 2nd item rather than choosing the first record of all the records that contain a combination of item 1 and 2. I don't understand why the cascading combo boxes seem to work just fine, but I can't get my form to show the correct details belonging to my selection?
Any help would be much appreciated.

 

[pr2-eugin]

Your Flow of logic is a bit twisted IMVHO, I will take a real world example. Consider a Farm with Cats, Dogs, Sheep, Cows, Hens, Ducks. The first filter would be Animal/Bird, if we choose Animal the second filter should work only for Animals. It is pointless to include two filters (a) Type of creature - Animal, (b) Animal that is Cattle/Pet. Make sense?

The first filter should be able to give only a specific set, the next filter will automatically be filtered based on the First and Second, so on and so forth.

 

[MangoFruit]

Do you mean that I shouldn't add more criteria as I go to the next combo box and then the next? So in stead of saying combo box 3 needs to take box 1 and 2 into account, it should only take box 2 into account?
If that is what you mean, that does make sense and is good to know, but I don't see how it would really matter in terms of functionality. I just changed this in my database but it doesn't fix the problem or change anything as far as I can tell.

 

[pr2-eugin]

Can you please upload a Stripped DB?

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[MangoFruit]

I think I finally just solved it myself. I was making two mistakes. First of all I should have put the fields with the details in a sub form rather than on the form itself. I already tried that before but with no results. The second thing I needed to do was give the sub form a 'where' clause that pointed to the choice in the combo box. I think I'll probably have to make a small sub form for each 4 of the different item choices.

 

[MangoFruit]

Thanks for your help though.

 

[pr2-eugin]

Glad you have it sorted. There would not be a need to write more code, Cascading comb's are the simplest version of filters. If you are happy that's okay.

Just if you are interested (in order of my preference) -
http://www.fmsinc.com/MicrosoftAccess/Forms/combo-boxes/cascading.html
http://www.fontstuff.com/access/acctut10.htm
http://www.baldyweb.com/CascadingCombo.htm

Good Luck !