Results 1 to 10 of 10

Autofill / lookup issue

  1. #1
    bertollini is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    4

    Exclamation Autofill / lookup issue

    Greetings,

    Given the below scenario, I want to make Field _4 , in Table_B auto populate, based on Table_A, much like an HLOOKUP or VLOOKUP in Excel.

    For example:
    If I the next entry in Field_3 is “d”, I want the table to look in Field_1 for “d” and autofill with the corresponding color “red” in Field_2.

    Again I DON'T want to have to choose anything for Field_4. I want it automatically filled in. ANY HELP IS GREATLY APPRECIATED!



    Table_A
    ________________________________
    Field_1 | Field_2
    ________________________________
    |
    a | green
    b | brown
    c | gray
    d | red
    e | blue
    f | black
    g | yellow
    h | pink
    i | orange

    Table_B
    ________________________________
    Field_3 | Field_4
    ________________________________
    i | orange
    g | yellow
    c | gray
    c | gray
    i | orange
    a | green
    g | yellow
    c | gray
    a | green

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Lake County, IL
    Posts
    1,978
    I think this is what you are trying to do.

    http://www.baldyweb.com/Autofill.htm

    Alan
    If an answer has "resolved" your issue, then make sure to mark your thread as "solved." Did my answer help? Click on the '*' below.
    Relational Database Principles
    DataPig Access Tutorials
    Debug your Code​

  3. #3
    bertollini is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    4
    I am assuming you are referring to the statement on that site that reads:
    "SELECT CustID, CustName, CustPhone FROM Customers"
    If so, shouldn't there be a "WHERE" statement in there somewhere, because the results depend on another cell in another table (referencing my original post)?

  4. #4
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Lake County, IL
    Posts
    1,978
    Look at my example attached.
    Attached Files Attached Files
    If an answer has "resolved" your issue, then make sure to mark your thread as "solved." Did my answer help? Click on the '*' below.
    Relational Database Principles
    DataPig Access Tutorials
    Debug your Code​

  5. #5
    bertollini is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    4
    OK, it looks like you're VERY close to what I need.
    So you have that form that does the lookup. Now I need a "submit button" on that form, that will add the selection as another row in the table. Make sense?
    FYI, Added as a permanent record to "Field3" and "Field4", as a new record. I tried to simply add a button to "add record", but it only submits the letter choice, and not the color that the form showed.

  6. #6
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Lake County, IL
    Posts
    1,978
    There is no need to save it to a table. If you need to retrieve the data from field4 you do it in a query by joining the two tables. It will automatically populate in the query which you can then export to excel, pdf, document or make a report out of. It becomes redundant putting it in a table.
    If an answer has "resolved" your issue, then make sure to mark your thread as "solved." Did my answer help? Click on the '*' below.
    Relational Database Principles
    DataPig Access Tutorials
    Debug your Code​

  7. #7
    bertollini is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    4
    Can I ask you for a favor, to put that into a query?

  8. #8
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Lake County, IL
    Posts
    1,978
    Not on a machine with Access right now. But will describe.

    In the Query design. Add both tables.

    connect the two fields that have mutual ID

    Drag the fields from both tables you want to include. Run the query. Uncheck the fields you don't want to display after looking at results.
    If an answer has "resolved" your issue, then make sure to mark your thread as "solved." Did my answer help? Click on the '*' below.
    Relational Database Principles
    DataPig Access Tutorials
    Debug your Code​

  9. #9
    V760838 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2012
    Posts
    10
    Thanks Alan,

    I searched for the example day -in and day-out on the access forum. All I was getting was VB jargon. With the help of your example, my most of the problems are sorted out.

    v760838

  10. #10
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Lake County, IL
    Posts
    1,978
    See attached for query.
    Attached Files Attached Files
    If an answer has "resolved" your issue, then make sure to mark your thread as "solved." Did my answer help? Click on the '*' below.
    Relational Database Principles
    DataPig Access Tutorials
    Debug your Code​

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

Similar Threads

  1. Replies: 1
    Last Post: 01-19-2012, 01:31 PM
  2. Autofill ?
    By Daryl2106 in forum Access
    Replies: 14
    Last Post: 10-28-2011, 09:09 AM
  3. Zipcode Lookup Autofill
    By jgalloway in forum Access
    Replies: 25
    Last Post: 09-20-2011, 05:54 PM
  4. Autofill
    By evosheas in forum Access
    Replies: 4
    Last Post: 09-20-2011, 01:29 PM
  5. Autofill
    By kdcooper88 in forum Access
    Replies: 1
    Last Post: 09-18-2010, 04:52 AM

Posting Permissions

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