Results 1 to 6 of 6
  1. #1
    jones is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    17

    Autofill the other field in same table using dlookup

    Hi to all,



    Please anyone could help me.

    I have 2 table: 1. tbl Material List
    2. tbl Material Supply Plan

    I want to select the Item code then autofill the Desc field with description in tbl Material Supply Plan.

    What i did is i make datasheet form from tbl Material Supply Plan and i choose the Desc field then make a proceedure in properties then in event tab i use afterupdate and in vba screen here is my proceedure,


    Option Compare Database

    Private Sub Item__Code_AfterUpdate()
    Desc DLookup("[Description]", "tbl Material List", "Item Code=" & Item Code)
    End Sub


    if i run it say: Compile Error:
    Expected: list seperator or )

    Please help me to figure out this problem



    Here is the sample that i want to autofill.
    If i select item code it autofill in Desc field.

    Project Code Work Order No Item Code Desc Reqd Qty Unit Date Needed Remarks


    01B-01C-13-0006 35MPa, OPC+MS+Waterproofing/Admix SP432MS/WP20 (as per SABIC specs)





    01B-01C-13-0001





    Thank you in advance.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    Names with inadvisable spaces have to be bracketed, but this avoids the extra trip to the data:

    http://www.baldyweb.com/Autofill.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jones is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    17
    Quote Originally Posted by pbaldy View Post
    Names with inadvisable spaces have to be bracketed, but this avoids the extra trip to the data:

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

    Hi Sir,

    Thank you Sir, it help a lot. i done it.
    Thank you very much.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    rnodern is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    6
    Quote Originally Posted by jones View Post
    Hi to all,


    I have 2 table: 1. tbl Material List
    2. tbl Material Supply Plan

    ...

    if i run it say: Compile Error:
    Expected: list seperator or )

    Thank you in advance.
    In addition, to the other excellent response, I find it handy to use underscore "_" characters in table naming convention to avoid this issue.

    It is also possible to do what you want via a query.

    You could just use the dlookup as an expression in the query. You can test these in the VBA immediate window to see if the dlookup works.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    I don't personally like the underscore, but it's certainly an acceptable character. I would NOT use DLookup() in a query.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 3
    Last Post: 01-09-2013, 03:39 PM
  2. Autofill using DLookup not working
    By BBonexx in forum Access
    Replies: 2
    Last Post: 01-08-2013, 09:18 PM
  3. Populating a field from another table using DLookup
    By Jamescdawson in forum Access
    Replies: 22
    Last Post: 03-16-2012, 09:59 AM
  4. Replies: 1
    Last Post: 02-20-2012, 01:02 PM
  5. adding an autofill field from another table
    By loopyl00 in forum Access
    Replies: 3
    Last Post: 01-12-2011, 08:59 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
  •  
Other Forums: Microsoft Office Forums