I have a form that has an unbound Listbox that is populated by a Query. When I run the query, I see the descriptions listed and not the codes that are actually stored in the table (which is what I want). When I display the form, the column shows the code number instead of the description. Here is what I am working with:
Table: TaskAssignments
Field: DifficultyLevel
The values for this field would be one of the following (99, 70, 50 or 10).
Under the General Tab, it shows:
Field Size: Long Int
Decimal Places: 0
Required: No
Index: Yes (Dups OK)
Under the Lookup Tab, it shows:
Display Control: ComboBox
Row Source Type: Table/Query
Row Source: SELECT TypesDifficultyLevel.DifficultyLevelID, TypesDifficultyLevel.DifficultyLevelDesc FROM TypesDifficultyLevel;
Bound Col: 1
Col Count: 2
Col Heads: No
Col Widths: 0"; 1"
List Rows: 8
List Width: 1"
Limit to List: Yes

My Query is called OpenWorkOrdersQuery which used the TaskAssignments (table) and the DifficultyLevel field. Like I said before the query shows the descriptions instead of the difficulty level code number.

I have created a Form with an Unbound ListBox with the following:
Row Source Type: Table/Query
Row Source: OpenWorkOrdersQuery
Col Count: 18
Col Heads: Yes
Bound Col: 1

When I display the form, under Difficulty Level I see the code numbers 10, 50, 70 and 99 and not their descriptions.

What am I doing wrong?

Oh, by the way I am using Access 2003 if that makes any difference.

do yourself a service and *FORGET* the sickeningly awful lookup "feature" in Access.
there is no redemption for the lookup "feature" - it addles your brain.
stay far FAR away (or further if possible)!

it is precisely the Access lookup "feature" that is screwing you around: yes you can work around it, but you end up coding 100* more lines than if you hadn't used the wretched lookup "convenience" in the first place.

start again:
kill all the lookups in all your table definitions.
fix whatever the kills broke.
...your above question will auto-resolve itself and sanity will resume.

lookup is evil!

izy

So if I am storing a code in my table, i.e. 10, 50, 70 and 99, how do I get it to display the description? Do I store the description in the main table instead of the number? If I do I won't be able to do the sorting that I want to do. In other words I want 10 to be at the top of my list and 99 to be at the bottom of my list and so forth.

you do exactly what the lookup thingie is doing, but you do it explicitly, in plain sight, and YOU will know exactly what is going on.

mycodestable:
PKcodes integer
Descr

mymaintable:
blah1,
blah2,
blah3,
FKcodes integer <<< foreign key pointing at PKcodes in mycodestable
blah4,
...
blah17

maybe you are using a smaller numeric type than integer - makes no difference, but use the same datatype for FK and PK. you will want referential integrity enforced (most probably without cascades).

your query then looks like:all the lookup marvel is doing is making that same JOIN for you behind the scenes (...and sowing confusion about the datatype of FKcodes).

do your own JOIN and you will know for 100% certain that your FK is an integer (and not the string that lookup magic wants to show you).

izy

Don't just take my word for it - look at this.

izy

Just to be clear, this is not just a bee in Izy's bonnet! Lookup Fields at the Table level are Evil! They're considered by most experienced Access developers to be the worst feature the Boys of Redmond ever came up with, and should be avoided at all costs!

Since Data Input should be done through a Form, simply use the Combobox Wizard on the Form and create your own 'lookup!'

Linq ;0)>

I think I have it worked out now. I went back into my main table and set the four fields that I was using the Lookup feature and changed them from Combo/Box to Text. I am using Radio buttons that are set to the "number values" that I want to store in the table. I then went into the Query and added the four description tables (one for each of the four fields in my main table) and that automatically created the relationships between the main table and the four description tables. When I opened the form that had the unbound list containing the query, the descriptions were listed instead of the code numbers; which is what I was wanting.

Yeah!!!

I did all of my changes on a test copy of the database that I had copied from my work computer to my personal laptop and now I just need to make all of these changes to the production database tomorrow morning.

Thanks for all of the help and I really appreciate it.