Results 1 to 6 of 6
  1. #1
    ToadPhrogg is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2020

    Form picks up wrong value from drop down list in table (Aghhhh!!!!)

    I have been struggling with this for days. I guess that "Do the same thing and expect different results" thing comes into play here.
    So here goes. Hope someone can help.
    So, I create a blank DB and add a few fields.
    I change the filed name to whatever, like States, Location and Climate for instance, save it as Destinations and close.
    Create three more tables named States, Location and Climate.
    In each of these, enter in a vertical column, 3 or 4 states, NW, SW, NE and SE for the Locations and HOT and COLD for the last one.
    Save and close all.
    Then open Destinations in Design view, select the field States, Use the Lookup Wizard to set up communications with Enable Integrity checked with the table States. Save and close.
    Repeat for the other two Fields, Location and Climate.
    Then, with Destinations selected but not opened, I create a form, save and close. I have even closed Access down and come back in just to make sure.

    I open the Destinations table and enter enough data for a couple records.
    Then I open the form. The cell which should have a state in it, has a number, like 1,2 or 3, depending on which record you are looking at.

    So, obviously, I'm not quite doing it correctly. I've watched video on the subject until I need a drink! So, if anyone out there can help with doing it correctly, well, it would be greatly appreciated.

    Thanks to the community in advance!

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Your description of the setup is quite confusing to me. You don't mention keys at all.
    You also don't explain what the DB is to accomplish.
    It would be best if you post your DB here so that we can see the current state of affairs and offer advice.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Nevada, USA
    Sounds like you are doing it correctly, just not what you expected. I suspect you set the lookup tables up with a numeric ID field plus the text value, which is normal. The ID field is what's typically saved in related tables, not the text value. If that doesn't sound like what's going on, can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    To add to Paul's comments, if you do have ID and 'name' fields, your combo should have two columns and set the first column width to 0.

    Also, it is not a good idea to use lookups in tables, only use them in forms. Reason is you will be typically storing a numeric value (the ID), but displaying text. This can lead to the sort of confusion you are experiencing.

  5. #5
    ToadPhrogg is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2020
    Submitting an update. First, thank you for your replies. I was in the process of making a DB to submit here for further help and in the process, have an idea as to what I was doing incorrectly. I think that by creating all of the tables for all of the drop-downs in the main table, and THEN creating the form did the trick. So, evidently, the sequence of events has a bearing on things. Everything is as it should be and working fine. Now I can move on and finish up my project.
    Maybe this will help someone else out there.

  6. #6
    ssanfu is online now Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Anchorage, Alaska, USA

    Quote Originally Posted by ToadPhrogg View Post
    ...... I think that by creating all of the tables for all of the drop-downs in the main table, and THEN creating the form did the trick.........
    You really should heed the advice of Ajax (Post#4).

    See The Evils of Lookup Fields in Tables
    (most, if not all, experienced Access developers avoid using Look up FIELDS in tables)

    Good luck with your project........
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 07-17-2018, 05:40 PM
  2. Qry criteria has trouble with Form picks
    By gpierce9 in forum Queries
    Replies: 3
    Last Post: 02-27-2018, 12:57 PM
  3. Replies: 7
    Last Post: 11-30-2013, 08:45 PM
  4. Replies: 1
    Last Post: 03-04-2013, 02:18 PM
  5. Table drop down list filter
    By jbataille86 in forum Access
    Replies: 2
    Last Post: 08-05-2011, 08:37 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums