Display Description instead of ID on combo box

[lorirobn]

Hi,

I have a form that consists of several combo boxes displaying information from a table. The table has ID fields, but I would like to display descriptions (from lookup tables) instead. The way I see it, there are 2 parts to this: the combo box 'text' part that will display the description of the ID actually on the table, AND the drop-down list that will display all the descriptions, from the look-up table.

From reading posts here, I have come up with a way to do this, but I'd like to make sure that this is the 'right' way and not kludgy. Here's how I do each combo box (by the way, form's record source is a query I have set up):

Combo box's control source is the corresponding ID on my table/query. Row source is the following select statement, referencing corresponding lookup table:

SELECT tlkpItemCategory.ItemCategoryDesc, tlkpItemCategory.ItemCategoryID, tlkpItemCategory.ItemCategoryDesc
FROM tlkpItemCategory
ORDER BY tlkpItemCategory.ItemCategoryDesc;

There are 3 columns, and their widths are: .007;0;2. The bound column is the ID, column 2.
Is it correct to use 3 columns like this? I tried it a handful of other ways, but had problems.

In addition, I have another question. One of my combo boxes will list descriptions based on the value selected in another combo box. For example, if combo box 1 selection is Furniture, combo box 2 will display "Dresser, Bed, Couch, etc". I haven't gotten this part to work so great yet - it works only for the first value, but when the values down the form change, cbo value is blank.
Its SQL is:

SELECT tlkpItemSubcategory.ItemSubcatDesc, tlkpItemSubcategory.ItemSubcatID, tlkpItemSubcategory.ItemSubcatDesc
FROM tlkpItemSubcategory
WHERE (((tlkpItemSubcategory.ItemCategoryID)=[Forms]![frmItems]![cboItemCategory].[Value]))
ORDER BY tlkpItemSubcategory.ItemSubcatDesc;

In my module, I requery subcategory on Category After_update (I tried Before_Update and Change, too). It only displays correctly for the first value of Category down the page. When category changes, the value in the 'text' portion of subcategory is blank, although its drop-down list is correct. I have narrowed the problem down to my WHERE clause; without it, it works fine. How do I have SQL display the the description for an item's ID, and then separately have the drop-down list correspond to another code?

I would appreciate any help on knowing if I am doing this correctly with the 3 columns, plus having one cbo dependent on another.

Thanks in advance.

 

[snyperx3]

Why in both of these do you have the Description twice?

Would it not be easier to use:

SELECT tlkpItemCategory.ItemCategoryID, tlkpItemCategory.ItemCategoryDesc
FROM tlkpItemCategory
ORDER BY tlkpItemCategory.ItemCategoryDesc;

and have 2 columns with the widths 0;2; and have the bound column set to 1?


-Pete

 

[lorirobn]

When I do that, I have a problem when I try to add a new record on the new line. When I try to Addnew, it doesn't allow me to select an ItemCategory from the drop-down list. It gives me the drop-down list, but when I click on an item, I get beeped.

I was so perplexed in figuring that one out, that I came up with this 3-column method (which I thought I saw in other posts).

Thx for the reply.

 

[snyperx3]

So you need to be able to add new items to the combo box at runtime, and then immediately afterward be able to select one of the items in that combo box and update the other combo box in relation to the selected data?



-Pete

 

[lorirobn]

Hi Pete,

Sorry if I wasn't clear: I do not need to add new items to the combo box, but to the table that is the recordsource of my form (let's say, tblItems). My form is a continuous form, with 5 combo boxes across. When I go to the "New Record" line to add (obviously!) a new record to tblItems, I click on the Category combo box to select a Category, and with the code you suggested, it doesn't allow me to select an item. I get a beep.

Hope this is more clear.
Thanks,
Lori

 

[snyperx3]

Thanks, that's definately more clear.

One more question. Does it beep when you click the arrow to see the values of the combo box, or does it beep after it drops down and you try to select the correct value?

=-=-=-=-=-=-=-=-=-=-=-=-=-=-
I'm going to throw a few guesses out there:

If you use the combo boxes the way I suggested, possibly the table that you are trying to put the value in wants a Number, and you're trying to give it Text. For example: tblItems.ItemCategoryID is a number, and tlkpItemCategory.ItemCategoryID is text.

Or possibly the table that has the categories in it has a category without an ID.

Or maybe you don't have the combo box bound to the correct field
=-=-=-=-=-=-=-=-=-=-=-=-=-=-

I'm not saying that you definately did any of these. These would just be the first things that I would check for on MY database if I got that reaction.



-Pete

 

[lorirobn]

Hi Pete - thanks for the suggestions. Before I get a chance to check them out on my db, I wanted to let you know about the beep: it's AFTER it drops down, when I try to select the correct value.
Hope that helps! Will check out the suggestions soon, hopefully...Lori