Page 1 of 2 12 LastLast
Results 1 to 15 of 23

Populating a field from another table using DLookup

  1. #1
    Jamescdawson is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    South Wales, UK
    Posts
    64

    Unhappy Populating a field from another table using DLookup

    I'm trying to populate a field (we'll call it field#2) on a standard form (Customer Record Card) automatically after I've selected the contents of another field on the same form (we'll call it field#1)

    The content of field#1 is a pick-list lookup from a separate table. The other table also holds the look-up list I want to use to populate field#2. The relationship between fileds 1 & 2 is direct. Sounds simple ? It's driving me nuts.

    I've tried using DLookup and I'm stuggling.

    Can it be done using DLookup or another Built-in function or do I need more complex code.

    Can anyone tell me what the syntax for the Dlookup function in the above scenario would be ... and where should I put it ? "on click" "after update".... ?

    I'm using Access 2010

    Help much appreciated

    =Jimbo=

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,207
    If your structure fits, this is is simpler and more efficient:

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

  3. #3
    Jamescdawson is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    South Wales, UK
    Posts
    64
    Good Morning Pbaldy,

    Many thanks for your suggestion. It makes sense. I configured the data in the Combo box and played with it to make sure I understand what's going on. However, I'm still not getting the 'autofill' in the text box. I've tried a number of syntax options but I'm doing something wrong.

    In your example

    =ComboName.column(2)

    what does "column(2)" represent ?

    I am trying to autofill the content of the text box with the contents of the 3rd column in my table. The 3rd column is labled "Code"

    so is this wrong ?

    =[County/UA].[Code]

    where "Country/UA" is the table name and "Code" is the third column.

    (I've also tried using integers 0 - 3.

    All options return #Name?


    Quote Originally Posted by pbaldy View Post
    If your structure fits, this is is simpler and more efficient:

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

  4. #4
    Jamescdawson is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    South Wales, UK
    Posts
    64
    That was a typo I do mean "County/UA"

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,207
    It would look exactly as I typed it, replacing the combo name and the appropriate integer. For the third column:

    =[County/UA].Column(2)
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  6. #6
    Jamescdawson is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    South Wales, UK
    Posts
    64
    Hi Pbaldy,

    Thanks for the additional assistance.

    I think I'm proceeding in the wrong direction. I'll try another way.

    Thanks for your help

    Best Regards

    =Jimbo=

    Quote Originally Posted by pbaldy View Post
    It would look exactly as I typed it, replacing the combo name and the appropriate integer. For the third column:

    =[County/UA].Column(2)

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,207
    No problem, and welcome to the site by the way. I'd like to make sure you beat this, so feel free to post back if you're stuck. Post the db if you want, and we'll sort it out.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  8. #8
    Jamescdawson is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    South Wales, UK
    Posts
    64
    P,

    Very kind. I'm reluctant to take advantage of experienced developers on free forums , so I wait until I am just about pulling my hair out before I ask. I'm more of an Operatrions Guy than a coder... I know stuff CAN be done, but I fall down on the "how". I may take you up on the offer.

    Best Regards

    Jimbo

    Quote Originally Posted by pbaldy View Post
    No problem, and welcome to the site by the way. I'd like to make sure you beat this, so feel free to post back if you're stuck. Post the db if you want, and we'll sort it out.

  9. #9
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,207
    We are here because we choose to be; nobody is forcing us to answer questions against our will. Ask away; nobody wants this:
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  10. #10
    Jamescdawson is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    South Wales, UK
    Posts
    64
    Pbaldy... You're a gentleman.....

    OK... I'll assume that I can return the good deed at some point in the future... this will be wordy.... apologies...

    I started to rethink my problem after your initial post because I'm actually trying to 'autofill' a field on a FORM using the data entered manually (or more accurately from a limited pick list) in another field on the same form... Ideally, I'd like this autofilled field to simple act as a default in the new field because there is a likelihood that the user may want to manually change the data which was initially autofilled to an alternative item from the original underlying pick list. After getting your first advice and suggestion, I tried to use the function you suggested on a slightly simpler problem with only partial success. I could only managed to fill the 2nd field with the ID number.

    In the scenario described above, I cannot simply have field #2 use the same source as field #1 because when the user makes a change to the second field, field#1 also changes!

    This problem relates to a data entry form which records & controls the scheduling of a small team of 6 drivers (Ken, Keven, Mike, Paul, Garry & Kai) who bring customer items back to our depot and which will be returned to the original customer 7 days later in a weekly cycle - after having been processed. It's necessary for us to populate that "Return Driver" field on the day that the item arrives. (There's good reason why this has to happen so far in advance.) However, the supervisor may decide (in fact it often is necessary) that one of the other drivers will return the item earlier and not wait for the original driver's scheduled visit. At the end of the pickup day, the supervisor generates job cards/drop-off schedules based on driver's names and whilst doing so, is able to re-schule a delivery by an alternative driver. The majority of the returns are made by the same driver on his weekly scheduled round - hence my preference for the 2nd field to be defaulted to the "Pickup Driver" - BUT, it is necessary that the supervisor be able to make that change to an alternative driver. I have two fields in the underlying source table for the form ("pickupdriver" and "ReturnDriver"). They can both populated by drop down pick lists from another table called "drivers". V.simple table (ID, Driver first name, driver Surname)

    So here's the process... On the day of pick-up, the data entry clerk selects the pick up driver's name from a drop down pick list on the data entry form together with a range of other data which I don't have problem managing. The item collected by the driver is processed on Day 1 & 2 and availble for return within 3 days but usually returned on the 7th day. Later on the pickupday, the transport manager gets a report based on that data entry form. He will then decide which - if any - of the returns need to be rescheduled. He then changes that schedule and prints the driver lists for all the items collected that day.

    He COULD simply bring up each item record and select a driver from the pick list for ever one, but this would be tedious in the extreme. better if he can just change the execeptions.

    We process 350-400 items per week - each with its own record in our db and each with a named pick up driver (and exceptionally) an alterntive return driver.

    There you have it. I look forward to your comments.

    Best Wishes

    =Jimbo=


    Quote Originally Posted by pbaldy View Post
    We are here because we choose to be; nobody is forcing us to answer questions against our will. Ask away; nobody wants this:

  11. #11
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,207
    I may be having late-afternoon brain cramps, but wouldn't the second method from that link work? That would set the value into the second field, but still allow it to be changed by the user later. If I'm not understanding, can you post the db here?
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  12. #12
    Jamescdawson is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    South Wales, UK
    Posts
    64
    Well if you are having brain cramps, I'm approaching "Brain dead". It's now after midnight here. I'll retry in the morning.

    Thanks again.

    Jimbo

  13. #13
    chauhan8787 is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    2
    How to Display combobox releted in other text field

  14. #14
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,207
    Jimbo, you must be over there in the same time zone my daughter lives in (England).

    Chauhan, have you reviewed the previous posts?
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  15. #15
    Jamescdawson is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    South Wales, UK
    Posts
    64
    Pbaldy,

    Just a quick note to say "Thanks" and that I solved the intial problem I asked about in my first post - based on your original suggestion. I didn't get it working until I set the "Bound Column" property in the Combo box to "1" As soon as that parameter was set, the rest worked as you said !! To be honest, I'm not sure why that setting was set to incorrectly in the first place and why that should affect the display in the scondary text box... but it does.

    With that in the bag, I can resolve the more detailed problem I posted at the week-end.

    Thanks again for pointing me in the right direction.

    Best Regards

    Jimbo

    PS: I'm not quite in England... I am a Scotsman living in Wales... about 5 miles from the border with England.



    Quote Originally Posted by pbaldy View Post
    Jimbo, you must be over there in the same time zone my daughter lives in (England).

    Chauhan, have you reviewed the previous posts?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Populating identity field of an over-ODBC linked MSSQL table
    By Passiday in forum Import/Export Data
    Replies: 0
    Last Post: 11-16-2011, 09:49 AM
  2. Replies: 2
    Last Post: 09-28-2011, 01:41 AM
  3. Replies: 1
    Last Post: 08-12-2011, 11:03 PM
  4. populating value of one field to another
    By jzacharias in forum Database Design
    Replies: 2
    Last Post: 09-08-2010, 01:39 PM
  5. Auto Populating a field in a table
    By softspoken in forum Access
    Replies: 11
    Last Post: 04-05-2010, 01:52 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
  •  
Tech Forums: Microsoft Office Forums