Dependent Drop-down menus not working

[quicksilver1024]

Hi all,

This is my first time posting at Access World Forums and I hope you can help me out.

I think this is a fairly simple problem but due to my lack of experience (this is actually my first time using Access) I am unable to figure out what is wrong.

I have a entry form for a series of problems that needs to be documented on the plant floor.

The issue is in the 'Manual_Entry_Rewinder_Detail' form where I have two drop-downs -one for the category of which the problem lies and another drop-down for the actual problem itself. I have made (or tried to at least) make the problem drop-down dependent to the category drop-down. The purpose is to list only the relevant problems after the category is selected.
I have made a category ID of which I labelled for each problem. This of course corresponds correctly to the actual (an existing) category ID. Please take a look at the two tables: "Problem" and "Category" for details.

I followed this tutorial http://www.blueclaw-db.com/download/dependent_combo_box.htm .
I even downloaded and inspected their sample database with no idea on what is wrong with my own.

Please help!

 

[ajetrumpet]

Quicksilver,

I think the issue of cascading has been widely covered, but I also think that there are many variations to the issue. I see that you have set up intermediate relationships on one field between your tables. This might have something to do with your problem. I don't have a solution for you, but I don't think there is a "one size fits all" solution regarding this. I've tried a few different methods of cascading techniques on different database structures and they don't all the work the same.

I've been trying to figure out the variations on this for quite a while, but I've not gotten it all yet.

If you asked me, this is one of the most annoying things about the program.

In addition to that BlueClaw page, I've also archived the following....

http://www.mvps.org/access/forms/frm0028.htm
http://accessprogrammer.blogspot.co...on-another.html
http://www.techonthenet.com/access/comboboxes/index.php
http://support.microsoft.com/default.aspx?scid=kb;en-us;209576

 

[Villarreal68]

Hello Silver,

Try this simpler code on the Manual_Entry_Rewinder_Detail.CAT_NAME_COMBO combo box:

SELECT Category.CATEGORY_ID, Category.CAT_NAME FROM Category;

Once I change it to it it worked. Well partially worked. The records that you have multiple Categories entered are not showing up. You may want to enter multiple entries for the problems with a different Category and then it should work.

Also within the properties of the same combo box click on the Format tab and enter Column count=2 and Column width=0";2" (Make sure and hit the F1 on each of those fields to see what they are for.)

I hope this helps you.

René

 

[ajetrumpet]

The records that you have multiple Categories entered are not showing up.

There is a primary key involved with both boxes. By default, the wizard selects PK and specified categories. PK is usually not relevant, but has to be included. It is hidden by default.

 

[quicksilver1024]

Thank you all for your replies!

Hello Silver,

Try this simpler code on the Manual_Entry_Rewinder_Detail.CAT_NAME_COMBO combo box:

SELECT Category.CATEGORY_ID, Category.CAT_NAME FROM Category;

Once I change it to it it worked. Well partially worked. The records that you have multiple Categories entered are not showing up. You may want to enter multiple entries for the problems with a different Category and then it should work.

Also within the properties of the same combo box click on the Format tab and enter Column count=2 and Column width=0";2" (Make sure and hit the F1 on each of those fields to see what they are for.)

I hope this helps you.

René

This worked beautifully, however in the Problem combo box it lists multiple entries for problems. Any fix for this? What exactly is causing it?

 

[quicksilver1024]

Nevermind, I retract my earlier post about it being fixed. If I open up ''Manual_Entry_Packer' and try the Problem drop-down it asks me for some kind of input (maybe because it doesn't know where the detail form is?).

I guess I should put up the updated DOC to make it easier to see what the problem is.

Please see the attached file.

 

[Villarreal68]

Silver,

Try this on the Query4 Category_ID Criteria:

[Forms]![Manual_Entry_Packer]![SheetDetail subform]![CAT_NAME_COMBO]

Remember that now this is a subform.

And to Resolve the multiple listings you need to work with the "Problem" and "Category" Tables.

The "CategoryID" field on the "Problem" table. Needs to be a "Data Type" Number.

Then Link field "Problem.CategoryID" to "Category.CategoryID" via a relationship. This will resolve your multipe entries.

Sorry Engish is not my primary language so this is the best I can expain.

Hope it helps.

René

 

[quicksilver1024]

Don't worry. Your English is just fine! If you hadn't told me it wasn't your native language I wouldn't even notice.

Okay - everything works except for the multiple entry. It still appears despite adding the relationship and changing the datatype.

The file now looks like this (please see attached)

 

[quicksilver1024]

Nevermind! All I had to do is group them together. Everything is working now except for the fact that in the SheetDetail, instead of listing the Category name (CAT_NAME) it now lists the CATEGORY_ID.

 

[quicksilver1024]

Problems just keep on coming!

Now, when I check SheetMaster, the CAT_NAME gives the CAT_NAME_ID (a number) instead of the name itself. Is this because in my query, CAT_NAME_ID is placed as the first column?

How do I get it to show CAT_NAME instead?

Thanks for the help so far

 

[Villarreal68]

Hello Silver,

You are in the right track. This is very simple:

In the Properties of this field go to the "Format" tab and enter the following:

Column count: 2
Column Widths: 0";2";
List width: 2"


"Column count" Looks at that number of clumns in the table.
" Column Widths" shows the width of each column, but if you notice by putting 0"; you are actually hiding that column. Although it still is there you cant see it, but it can still be referenced. So the Data tab can still be bound to column 1, but the list will show column 2 of the table.

"List Width" tell you how wide the list will show when you click on the drop-down button.

I hope this makes sense to you. And you can use it in the future as well.

Have fun!

René

(PS. sorry for not answering sooner sort of busy day for me.)

 

[quicksilver1024]

Hello Silver,

You are in the right track. This is very simple:

In the Properties of this field go to the "Format" tab and enter the following:

Column count: 2
Column Widths: 0";2";
List width: 2"


"Column count" Looks at that number of clumns in the table.
" Column Widths" shows the width of each column, but if you notice by putting 0"; you are actually hiding that column. Although it still is there you cant see it, but it can still be referenced. So the Data tab can still be bound to column 1, but the list will show column 2 of the table.

"List Width" tell you how wide the list will show when you click on the drop-down button.

I hope this makes sense to you. And you can use it in the future as well.

Have fun!

René

(PS. sorry for not answering sooner sort of busy day for me.)

Oh. I see. Thank you for clearifying that up. I actually had the settings you speak of, so I looked around and found that the Bound Column is the column that will be entered into the database and changed that to 2, where the names were.

Thank you for your help.

 

[Villarreal68]

I'm glad you figured it out!

Later!