Page 3 of 3 FirstFirst 123
Results 21 to 30 of 30

Thread: Look up data from another table

  1. #21
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,293
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Look up data from another table

    OK, first off, you should avoid using spaces in Object names (names of Table, Queries, Forms, etc) and Fields. It is good programming practice not to use spaces. I use underscores instead.
    So I changed the name the "Material Part Number" fields to "Material_Part_Number".

    Then, in using the method Allen Browne shows at the end of Example 2 here: http://allenbrowne.com/casu-07.html, I came up with this formula as the Control Source of my Text field, and it worked for me:
    Code:
    =DLookUp("[Price]","Cost","Material_Part_Number='" & [Material_Part_Number] & "'")
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  2. #22
    Board Regular
    Join Date
    Jun 2009
    Posts
    135
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Look up data from another table

    I am having to start again as just done something and hole thing crashed and lost everything (this may be a good thing only time will tell)

  3. #23
    Board Regular
    Join Date
    Jun 2009
    Posts
    135
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Look up data from another table

    Hi All,
    I managed to get it working so it displays the Part Name and Part Price within the form !!!!
    But these two details are not then copied across to the database

    Is there anyway round that?

  4. #24
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,293
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Look up data from another table

    But these two details are not then copied across to the database
    Do you mean back to the underlying table? No, that doesn't do that. It just looks up the value, which may be all that is really required.

    Typically, in most cases, you would NOT stored calculated values at the table rule. The general rule of thumb is to not store any value which can easily be calculated. Doing so may undermine data integrity and make your database less dynamic. If a value can easily be calculated, usually there is no need to store it (it is like in Excel if you have the value of C1 equal A1 + B1, there is no need to "hard-code" the value when you can just leave the formula). If you wanted to do a query on the table, you would just join the two tables together in a query on the common field, and return the fields you want from each table.

    The exception to storing values at the table level is if it is needed for historical reasons, i.e. the price can change, so you need to capture it at that particular point in time. So for something like that, instead of using the DLOOKUP formula in the Control Source of a Text field, you would use VBA that after a value is entered, it looks up the value and writes it to the table.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #25
    Board Regular
    Join Date
    Jul 2010
    Posts
    305
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Look up data from another table

    Quote Originally Posted by posfog View Post
    How would you get the part name description and part price then to appear in your data table?
    You wouldn't save the Description(s) - if you need to view these then you would use a query to link back to the data table - that's normalisation - if there is a link it is unusual to require duplication.

    However with the price you are right, this is often required as a price can update and you wouldn't want to update historical orders. The best way to do this is using VBA code on the after update event of your form. If you are planning lots of thinks like this then it would be worthwhile having a look/play around with the Northwind sample database.

    I have added to my sample here in a much more basic format than the Northwind database.

  6. #26
    Board Regular
    Join Date
    Jun 2009
    Posts
    135
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Look up data from another table

    Hi Stumac

    Just done the same in my database but geting this error

    Cannot join on Memo,OLE, or Hyperling Object (Data.[Material Part Number]=cost.[Material Part Number]).

    Any ideas?

  7. #27
    Board Regular
    Join Date
    Jul 2010
    Posts
    305
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Look up data from another table

    Looks like one or both of these fields: Data.[Material Part Number], cost.[Material Part Number] are one of these data types: Memo,OLE, or Hyperling

    Change them both to short text and it should be ok.
    Last edited by stumac; Nov 16th, 2018 at 04:19 AM.

  8. #28
    Board Regular
    Join Date
    Jun 2009
    Posts
    135
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Look up data from another table

    Thanks Stumac!!

  9. #29
    Board Regular
    Join Date
    Jun 2009
    Posts
    135
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Look up data from another table

    Quote Originally Posted by stumac View Post
    Looks like one or both of these fields: Data.[Material Part Number], cost.[Material Part Number] are one of these data types: Memo,OLE, or Hyperling

    Change them both to short text and it should be ok.
    Morning Stumac,

    I have added another coloumn to my Cost table with some more details so was thinking all i would need to do is change the details in my form

    Eg
    Copy
    =[Material Part Number].[column](2)

    But change the (2) to (4) as the new details i want to include is in coulmn 4 but it dosnt seam to want to work?

    Any Ideas?

    Regards

  10. #30
    Board Regular
    Join Date
    Jul 2010
    Posts
    305
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Look up data from another table

    You need to change the row source of your combo box to include the new field. Also bear in mind that it starts at 0, therefore if there is 4 fields in the row source then the last one would be 3.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •