Results 1 to 8 of 8
  1. #1
    cshipley83 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    28

    Auto Populate Description Field by Data Entered into Code Field

    Hi. In my form I need to automatically populate the description field by the data that is entered in the code field.


    For example: Code = 5710 Description = GOJ

    Both the Code and Description fields are in the same table.
    Thanks for any help given.

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Make combo box with Code, Description fields. After they select a code, in the AfterUpdate event, use me.Description = me.Code.column(1)

  3. #3
    cshipley83 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    28
    I have too many codes for combo box. They need to be manually entered.

  4. #4
    cshipley83 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    28
    Also, this is in a sub form. I didn't know if that mattered or not.

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    So you have over 65535 codes? If so then you can do a DLookup in AfterUpdate Event on Code field:
    Me.Description = DLookup("[Description]", "tblCodes", "Code = " & Me.Code)

  6. #6
    cshipley83 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    28
    No, I have over 400 codes, but I don't want that field to be a combo box. The are manually entered, we know the codes so its faster. I tried the DLookup you gave me above, but I got an error saying it could not find the tblCodes, so I removed the tbl part and it works, but it will only populate the description for the first record only.

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Use a lookup query as the source for your form. I don't normally recommend these but if you are determined not to use a combo box that seems the likeliest way.

    The lookup query would require you to have a code table where the code you're entering is the PRIMARY KEY of the code table (it must be defined as the primary key in the table design).

    so for instance if you had the tables

    Code:
    tblErrCodes
    E_ID  E_Desc
    5710  GOJ
    
    tblErrors
    Err_ID  E_ID Err_Qty ----> other error related fields
    The lookup query would be

    Code:
    SELECT tblErrors.Err_ID, tblErrors.E_ID, tblErrors.Err_Qty, tblErrCodes.E_Desc
    FROM tblErrCodes RIGHT JOIN tblErrors ON tblErrCodes.E_ID = tblErrors.E_ID;
    If you type 5710 in the E_ID field the description will be filled in automatically.

    Then just adapt this for your data entry screen.

  8. #8
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    put a breakpoint on line with "Me.Description = DLookup("[Description]", "Codes", "Code = " & Me.Code)", when code is stopped, mouse over Me.Code(or whatever you text box name is) to see what value it has. It should be the code you put in the text box. Hit F8 to step though that 1 code line. Look at me.Description and see what it gives you.

    I assume each Code value in that table is unique right?

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

Similar Threads

  1. auto populate description when code is entered...
    By willmafingerdo in forum Programming
    Replies: 5
    Last Post: 06-07-2016, 12:25 PM
  2. Replies: 9
    Last Post: 08-15-2014, 12:03 PM
  3. Replies: 1
    Last Post: 08-10-2014, 04:06 PM
  4. Replies: 3
    Last Post: 07-26-2012, 03:55 PM
  5. Replies: 6
    Last Post: 04-27-2012, 01:31 PM

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