I've pasted the code for the help topic below.
A couple of notes related to your issue -
1. I'm not sure if I understand what you are looking to do, but it sounds
like you are trying to get values that are in a subform. If this is so, you
wouldn't use a Dlookup function. Dlookup returns values from a table or
query, not a form. You would read the subform controls - something like
this:
Me!MyInvoiceNo = Me!Subform1.Form!MyInvoiceNo
2. You seem to be looking for three values. Dlookup can only return one
value. You would have to issue three Dlookups to get your data. It would
probably be easier to open a recordset and read all three values at once.
Dim db as Database
Dim rs as Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("MyTable", dbOpenSnapshot)
rs.Findfirst "InvoiceNo = " & Me!InvoiceNo
If Not rs.NoMatch Then
Me!Field1 = rs!Field1
Me!Field2 = rs!Field2
etc.
End If
Set rs = Nothing
Set db = Nothing
Hope this helps.
=================================
You can use the DLookup function to get the value of a particular field from
a specified set of records (a domain (domain: A set of records that is
defined by a table, a query, or an SQL expression. Domain aggregate
functions return statistical information about a specific domain or set of
records.)). Use the DLookup function in a Visual Basic for Applications
(VBA) module, a macro (macro: An action or a set of actions that you can use
to automate tasks. Macros are recorded in the Visual Basic for Applications
programming language.), a query expression, or a calculated control
(calculated control: A control that is used on a form, report, or data
access page to display the result of an expression. The result is
recalculated each time there is a change in any of the values on which the
expression is based.) on a form or report.
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.
Syntax
DLookup(expr, domain [, criteria] )
The DLookup function syntax has these arguments:
Argument Description
expr Required. An expression that identifies the field whose value you want
to return. It can be a string expression (string expression: An expression
that evaluates to a sequence of contiguous characters. Elements of the
expression can be: functions that return a string or a string Variant
(VarType 8); a string literal, constant, variable, or Variant.) 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. 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. A 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 (Null: A value you
can enter in a field or use in expressions or queries to indicate missing or
unknown data. In Visual Basic, the Null keyword indicates a Null value. Some
fields, such as primary key fields, can't contain Null.).
Remarks
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 (primary key: One or more fields (columns) whose values
uniquely identify each record in a table. A primary key cannot allow Null
values and must always have a unique index. A primary key is used to relate
a table to foreign keys in other tables.) value for your criteria, such as
[EmployeeID] in the following example, to ensure that the DLookup function
returns a unique value:
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 (update query: An action query (SQL statement)
that changes a set of records according to criteria (search conditions) that
you specify.).
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:
=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 (foreign table: A table (such as Customer Orders) that
contains a foreign key field (such as CustomerID) that's the primary key
field in another table (such as Customers) in the database and that is
usually on the "many" side of a one-to-many relationship), 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.
Note Unsaved changes to records in domain aren't included when you use
this function. If you want the DLookup function to be based on the changed
values, you must first save the changes by clicking Save Record under
Records on the Data tab, moving the focus to another record, or by using the
Update method.
Example
Note Examples that follow demonstrate the use of this function in a
Visual Basic for Applications (VBA) module. For more information about
working with VBA, select Developer Reference in the drop-down list next to
Search and enter one or more terms in the search box.
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.
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 Office
Access 2007 will obtain the current value from the control.
Dim varX As Variant
varX = DLookup("[CompanyName]", "Shippers", _
"[ShipperID] = " & Forms!Shippers!ShipperID)
The next example uses a variable, intSearch, to get the value.
Dim intSearch As Integer
Dim varX As Variant
intSearch = 1
varX = DLookup("[CompanyName]", "Shippers", _
"[ShipperID] = " & intSearch)
================================
