Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2012
    Posts
    18

    Question Unanswered: Description not displaying in an unbound listbox on a form

    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.
    Last edited by Polkster58; 02-03-12 at 19:33.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    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
    currently using SS 2008R2

  3. #3
    Join Date
    Jan 2012
    Posts
    18
    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.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    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:
    Code:
    SELECT a.blah1, a.blah2, a.blah3, a.FKcodes, b.Descr
    FROM mymaintable a
    LEFT JOIN mycodestable b
    ON a.FKcodes = b.PKcodes
    ORDER BY a.FKcodes;
    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
    Last edited by izyrider; 02-06-12 at 09:59. Reason: typo 'FK' --> 'FKcodes'; 'PK' --> 'PKcodes' (in query)
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    currently using SS 2008R2

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    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)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Jan 2012
    Posts
    18

    Thumbs up

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •