none
Dlookup from subform

    Question

  • Hi Experts,

    I created a new textbox named it txtbal in Main Form and click textbox properties and place below Dlookup in ControlSource, but it is error. How to Dlookup textbox value in Sub form from Main Form (MainForm Name is OnHoldMain)?

    =DLookUp("[TxtTotalBal]"," [Forms]![frmOnHoldDetailsSub]")

    *TxtTotalBal is in SubForm.

    *SubForm name is frmOnHoldDetailsSub.

    Kindly advise. Your reply is highly appreciated.

    Thanks.

    Regards,

    DAnny

      

    Monday, August 22, 2011 10:14 AM

Answers

  • Danny Gan wrote:
    > I created a new textbox named it txtbal in Main Form and click
    > textbox properties and place below Dlookup in ControlSource, but it
    > is error. How to Dlookup textbox value in Sub form from Main Form
    > (MainForm Name is OnHoldMain)?
    >
    > =DLookUp("[TxtTotalBal]"," [Forms]![frmOnHoldDetailsSub]")
    >
    > *TxtTotalBal is in SubForm.
    >
    > *SubForm name is frmOnHoldDetailsSub.
    > ...
     
    DLookUp fetches data from tables and queries not from forms.
    If you just want to show a value of the subform in your main form
    then try this expression:
     
    =frmOnHoldDetailsSub.Form!TxtTotalBal
     
    --
    cu
    Karl
    *********
    Access-FAQ (German/Italian): http://www.donkarl.com
     
     
     
    • Marked as answer by Danny Gan Tuesday, August 23, 2011 9:11 AM
    Monday, August 22, 2011 12:19 PM

All replies

  • Here is the information for the VBA Help file on the DLookup Function.  From that you will note that the second argument of the function is to be the domain that from which the data is to be extracted.  You will also note that the domain can be a table name or a query name for a query that does not require a parameter.

    I doubt that  [Forms]![frmOnHoldDetailsSub] satisfies that requirement.

    Application.DLookup Method 

     

    You can use the DLookup function to get the value of a particular field from a specified set of records (a domain ).

    Syntax

    expression.DLookup(Expr, Domain, Criteria)

    expression   A variable that represents an Application object.

    Parameters

    Name Required/Optional Data Type Description
    Expr Required String An expression that identifies the field whose value you want to return. It can be a string expression  identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field. In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.
    Domain Required String A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter.
    Criteria Optional Variant An optional string expression used to restrict the range of data on which the DLookup function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DLookup function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise, the DLookup function returns a Null.

    Return Value
    Variant

    Remarks

    You can use the DLookup function to display the value of a field that isn't in the record source for your form or report. For example, suppose you have a form based on an Order Details table. The form displays the OrderID, ProductID, UnitPrice, Quantity, and Discount fields. However, the ProductName field is in another table, the Products table. You could use the DLookup function in a calculated control to display the ProductName on the same form.

    The DLookup function returns a single field value based on the information specified in criteria. Although criteria is an optional argument, if you don't supply a value for criteria, the DLookup function returns a random value in the domain.

    If no record satisfies criteria or if domain contains no records, the DLookup function returns a Null.

    If more than one field meets criteria, the DLookup function returns the first occurrence. You should specify criteria that will ensure that the field value returned by the DLookup function is unique. You may want to use a primary key  value for your criteria, such as [EmployeeID] in the following example, to ensure that the DLookup function returns a unique value:

    Visual Basic for Applications
    Dim varX As Variant
    varX = DLookup("[LastName]", "Employees", "[EmployeeID] = 1")

    Whether you use the DLookup function in a macro or module, a query expression, or a calculated control, you must construct the criteria argument carefully to ensure that it will be evaluated correctly.

    You can use the DLookup function to specify criteria in the Criteria row of a query, within a calculated field expression in a query, or in the Update To row in an update query .

    You can also use the DLookup function in an expression in a calculated control on a form or report if the field that you need to display isn't in the record source on which your form or report is based. For example, suppose you have an Order Details form based on an Order Details table with a text box called ProductID that displays the ProductID field. To look up ProductName from a Products table based on the value in the text box, you could create another text box and set its ControlSource property to the following expression:

    Visual Basic for Applications
    =DLookup("[ProductName]", "Products", "[ProductID] =" _
       & Forms![Order Details]!ProductID)

    Tips

    • Although you can use the DLookup function to display a value from a field in a foreign table , it may be more efficient to create a query that contains the fields that you need from both tables and then to base your form or report on that query.
    • You can also use the Lookup Wizard to find values in a foreign table.

     

    Example

    The following example returns name information from the CompanyName field of the record satisfying criteria. The domain is a Shippers table. The criteria argument restricts the resulting set of records to those for which ShipperID equals 1.

    Visual Basic for Applications
    Dim varX As Variant
    varX = DLookup("[CompanyName]", "Shippers", "[ShipperID] = 1")

    The next example from the Shippers table uses the form control ShipperID to provide criteria for the DLookup function. Note that the reference to the control isn't included in the quotation marks that denote the strings. This ensures that each time the DLookup function is called, Microsoft Access will obtain the current value from the control.

    Visual Basic for Applications
    Dim varX As Variant
    varX = DLookup("[CompanyName]", "Shippers", "[ShipperID] = " _
      & Forms!Shippers!ShipperID)

    The next example uses a variable, intSearch, to get the value.

    Visual Basic for Applications
    Dim intSearch As Integer
    Dim varX As Variant
    
    intSearch = 1
    varX = DLookup("[CompanyName]", "Shippers", _
      "[ShipperID] = " & intSearch)

    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Monday, August 22, 2011 12:08 PM
  • Danny Gan wrote:
    > I created a new textbox named it txtbal in Main Form and click
    > textbox properties and place below Dlookup in ControlSource, but it
    > is error. How to Dlookup textbox value in Sub form from Main Form
    > (MainForm Name is OnHoldMain)?
    >
    > =DLookUp("[TxtTotalBal]"," [Forms]![frmOnHoldDetailsSub]")
    >
    > *TxtTotalBal is in SubForm.
    >
    > *SubForm name is frmOnHoldDetailsSub.
    > ...
     
    DLookUp fetches data from tables and queries not from forms.
    If you just want to show a value of the subform in your main form
    then try this expression:
     
    =frmOnHoldDetailsSub.Form!TxtTotalBal
     
    --
    cu
    Karl
    *********
    Access-FAQ (German/Italian): http://www.donkarl.com
     
     
     
    • Marked as answer by Danny Gan Tuesday, August 23, 2011 9:11 AM
    Monday, August 22, 2011 12:19 PM
  • Danny Gan wrote:
    > I created a new textbox named it txtbal in Main Form and click
    > textbox properties and place below Dlookup in ControlSource, but it
    > is error. How to Dlookup textbox value in Sub form from Main Form
    > (MainForm Name is OnHoldMain)?
    >
    > =DLookUp("[TxtTotalBal]"," [Forms]![frmOnHoldDetailsSub]")
    >
    > *TxtTotalBal is in SubForm.
    >
    > *SubForm name is frmOnHoldDetailsSub.
    > ...
     
    DLookUp fetches data from tables and queries not from forms.
    If you just want to show a value of the subform in your main form
    then try this expression:
     
    =frmOnHoldDetailsSub.Form!TxtTotalBal
     
    --
    cu
    Karl
    *********
    Access-FAQ (German/Italian): http://www.donkarl.com
     
     
     


    Dear Karl,

    Thankyou very much....

    Danny

    Tuesday, August 23, 2011 9:12 AM