Stuck With DLookup

[Craig]

Hi,
Im tryin to use Dlookup to return the values of a productcode,
I want to type the product code into its field and then lookup the product
description and the quality code. Im trying to remove operator error of
picking the wrong quality code as it pulls on all the specifications for that
product type.
Im trying to look the information up on a linked table
"Dbo_DProducts"

Any help will be much appreciated.

[Tom Wickerath]

Hi Craig,

Here is more help on using the DLookup function:

DLookup Usage Samples
http://www.mvps.org/access/general/gen0018.htm

However, are you sure that you need to use this domain aggregrate function?
Since you mention looking up product code, and reducing operator error in
data entry, it really sounds to me like a combo box might be more suitable.

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

[Craig]

It is currently a combo box, which shows all 3 bits of info, but seems you
can only be bound to 1 field, combo boxes wont update the other 2. Or is
there a way to do that? (This is why i thought Dlookup would be the best
option)

[Tom Wickerath]

Hi Craig,

Yes, there is a way to do it with a combo box. Take a look at the sample
Northwind database (Northwind.mdb). Open the Orders form and navigate to a
new record. When you select a customer from the Bill To dropdown, you will
see that the Ship to information is filled in into text boxes. This ship to
info. is also saved to the Orders table, to maintain a historical record.

While on this new order record, select a product. You'll see that the
UnitPrice is automatically filled in, along with default values for Discount
and Quantity. The Northwind sample uses the DLookup function in this example
for the Product combo box After_Update event procedure. You can see this if
you view the code module associated with the "Orders Subform" form:

Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID

' Look up product's unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter)

There's actually a different way to approach this, and I think it is a more
efficient method. First, modify the row source for this combo box from this:

SELECT DISTINCT [ProductID], [ProductName], [Discontinued] FROM Products
ORDER BY [ProductName];

to this:

SELECT ProductID, ProductName, Discontinued, UnitPrice FROM Products ORDER
BY ProductName;

Basically, I've just added a new field to the SELECT statement (UnitPrice).
I also removed the explicit references to the table name, but you don't need
to do that. When you run this query, UnitPrice will be the 4th column.
However, when we refer to columns in VBA code, we use a zero-based counting
system. Thus, in VBA code, ProductID will be column(0), ProductName will be
column(1), Discontinued will be column(2) and UnitPrice will be column(3). We
will set the column count on the Format tab to 4, and add a 0 as the column
width for our new column: 0";2.2";1";0"
This way, the UnitPrice will not be visible in the dropdown box, but it will
still be available in VBA code. To use this modification, make the following
change to the Private Sub ProductID_AfterUpdate() event procedure in the
Orders Subform. Note-I used my standard error handling as well:

Private Sub ProductID_AfterUpdate()
On Error GoTo ProcError

' Look up product's unit price and assign it to UnitPrice control.
Me!UnitPrice = Me.ProductID.Column(3)

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure ProductID_AfterUpdate..."
Resume ExitProc
End Sub

The reason that I feel that this is more efficient is that you are not using
the domain aggregate function, DLookup, each and every time that you select a
product. This can have important consequences, especially for a split Access
application, where a network is involved. For a single user application,
where the entire application is on the users local hard drive, one would not
notice a perfomance hit for the DLookup method. However, for a multiuser
application, with the method I just suggested, you would only be adding one
more field to the recordsource that is initially pulled "over the wire" in
order to populate the combo box. From that point on, the UnitPrice is
available without having to look it up each time. As long as these prices are
fairly stable (ie. not subject to being changed several times a day) there
should not be too much danger of an out-of-date price getting used for the
order. Once the form is closed and re-opened, the combo box would be properly
updated with a changed UnitPrice, if an update had been made.

[Craig]

Thanks Tom, that works brilliantly!

[Tom Wickerath]

Glad I could help. Good luck on your project.