Friday 29 July 2011

Using the DLookUp function

Imagine the following scenario: we have created a form to display information contained in an order details table.  Having selected tblOrderDetails as the form's Record Source, we realise this table (part of a Many to Many Relationship) does not include the name of the product item as one of its fields; it instead uses the item name Id as a foreign key from tblProducts.  Obviously, this is going to be a problem from the perspective of user friendliness.  That is to say, somebody using the form is not necessarily going to know which product ID relates to which product item name.  This is where the DLookUp function comes in handy.

The DLookUp function allows the Access Developer to look up the value of a field from a table other than the form's actual Record Source. It is often used as a function in a Calculated Text Box Control (see previous post for more information about Calculated Controls).  So applied to our scenario, we can use a Calculated Control containing the DLookUp function to obtain the item name from tblProducts (based on our knowledge of the product item's ID).

When we use the DLookUp function we need to provide it with three pieces of information (called parameters).  These are:

  1. The Field Name
  2. The Table or Query Name
  3. The Criteria to find the particular record.
The syntax for the DLookUp function used in a Calculated Control is as follows:

=DLookUp("FieldName", "TableName", "Criteria")

In our scenario we would enter the parameters which we need to pass into the text box's Control Source as follows:


=DLookUp("itemName", "tblProducts", "ID = " & forms![frmOrderDetails]![ProductId])

So here we are looking up the value of the itemName field, in the tblProducts table, where the ID for the product record matches the ProductId displayed on our active Order Details form.

It might be worth elaborating on the criteria parameter that we have used.  All parameters used in the DLookUp function (including the criteria parameter), are of the String Data Type.  The criteria parameter is a string containing information similar to an SQL WHERE Clause - except the "WHERE" part of the statement is omitted.  For example "ProductId = 1" instead of "WHERE ProductId =1".  In our example the ProductId used in this expression is going to be different for each current record displayed on the form.  Therefore our criteria needs to refer to the value of the ProductId displayed for the current record on our active form.  This is why our criteria is written:

 "ID = " & forms![frmOrderDetails]![productId]

Notice that only the "ID = " is contained within the quotation marks used to identify a string. The & symbol that appears immediately afterwards indicates that the information following it is intended to be part of that same string - a concatenation.  The criteria ends with the reference to the value contained in the productID of the active Order Details form that we have been working with - the syntax for the reference being forms![frmOrderDetails]![productId].



3 comments:

  1. Hello Justin,

    I am having a problem with a form I am creating, where in one vield I am selecting a value from a list linked to Table1, and am wanting to have other fields in the form populate with the corresponding value in that row using the DLookup. However, whenver I do this, it does not populate the field correctly, and in Form View it just shows "#Error". Below is the code, can you please review this & let me know what it is that I am doing wrong?

    =DLookUp("Value1","Table1","Value2 = " & [Forms]![Form1]![Field1-Value3])

    ReplyDelete
    Replies
    1. Hi

      I'm not sure I quite understand what you are trying to do here. However, in terms of the syntax you are using, are "Value1","Value2" and "Field1-Value3" the names of fields from a table. If so, is the data type of "Field1-Value3" numeric? If it is not, then let me know: if you are searching for a text value as opposed to a number, your syntax will need adjusting slightly.

      Delete
  2. Hi Justin, DLookUp looks weird. I have been looking at your examples and have come up with a

    best effort. This example using controls in a Report leads to #Error on the report. Where is

    the problem.
    Thanks for looking at this one...

    Briefly: I want to look up the table for the entry that
    contains "X-exp-tot" and having done so return to me the value in the "Aggregate" field. My

    desired result as "GBP".

    =DLookUp("Aggregate","ExpTypes","ExpCode = " & [x-TotalsCode])

    The table is called "ExpTypes"
    The value I am trying to extract is the "Aggregate" field
    that appears in the table opposite field "ExpCode"

    The text box "x-TotalsCode" has control ="X-exp-tot"

    Here is the entry in the table...

    ExpCode ExpDescr Aggregate ID
    X-exp-tot x-All - Total (Nominal)GBP 14

    Here is the SQL for a query that displays the results:
    SELECT ExpTypes.ExpCode, *
    FROM ExpTypes
    WHERE (((ExpTypes.ExpCode)="X-exp-tot"));

    thx Raphael
    http://ms-access-tips.blogspot.co.uk/2011/07/using-dlookup-function.html

    ReplyDelete