Re: comboBox & northwinds sample orders form

From: Albert D. Kallal (PleaseNOOOsPAMMkallal_at_msn.com)
Date: 09/05/04 

Date: Sun, 5 Sep 2004 16:27:19 -0600

"Mike Painter" <mddotpainter@sbcglobal.net> wrote in message
news:Mnz_c.16053$OQ1.229@newssvr29.news.prodigy.com...
>
>
> I'm not sure wht the intent of this was but for any readers that may be
> new
> to Access or relational databases this is the way they are designed to
> work.
>
> There are no tricks involved and it is no more cool than having a light go
> on when you flip a switch.

True, but OFTEN to display a customer name when you have a customer id, we
are told to use a dlookup, or use the

me.MyComboBox.column(1)

And, a very large portion of the time in this newsgroup, when people need to
"display" a related value in a form, the above two solutions (column(1), and
dlookup) are suggest.

>
> dLookup is slow but that will only be important if you are running one in
> a
> large query.

> Why it is slow is mystery because you can run a query in VB that does
> exactly the same thing and it many times faster.

Well, the problem is that you don't need dlookup in a query. And, the reason
why it is slow is because for each record, a whole function is called that
has to open up a table. You can always just shove in the sql in place of the
dlookup anyway.

select customerID, dllookup("CustomerName",tblCustomers","CustomerID = " &
customerID as CustName,
                           OrderDate, OrderTerms from tblOrders.

In the above, we are lookup in the customer name. However, the above should
be replaced with a sub-query. A sub-query gets compiled by the query
process..and runs VERY fast. So, we can/should use:

select customerID, (select CustomerName from tblCustomers where CustomerID =
tblOrders.CustomerID) as CustName,
                          OrderDate, OrderTerms from tblOrders

The above runs fast, since the query processor can compile the whole
statement..and "KEEP" the tblCustomers table opened. We have no such luxury
with dlookup. And, to be frank, the only time dlookup is of any use in on a
form for a quick and dirty lookup..and as mentioned, even for a quick
lookup...dlookup is rarely needed. And, as mentioned, you can use the query
builder, and use left joins. So, even if you have 3 or 4 lookups in a
table..the query builder can make this form you..and no sql needs to be
typed. (the above sub-select tends to be cleaner solution then left joins
via the query builder, but it don't matter..as both perform very well..and
certainly MANY times faster then dlookup).

Further, dlookup is a access VB function, and one would do well to use of
the "many" "sql" based solutions in place of a VB function (least someday
one moves the back end data part to sql server. In fact, one should avoid
ALL use of VB functions directly in quires for performance reasons anyway).

We also as a general rule don't call basic functions in pick queries either!
(but, the callX does perform very well!)

Of course, the same quick and dirty lookup trick in Pick basic code is often
used. It is:

CustomerName = oconv(customerID,"TCUSTOMERS;X;5;5")

(assumes that customerName is amc field 5)

Using a translate directly in pick code was also consider "lazy", and
opening a file for reading in place of oconv was always MUCH faster.
However, I will admit that using the oconv with a file translate in pick
basic don't seem to suffer much of a performance hit like ms-access does.

As mentioned, with several sql based solutions....dlookup should be avoid,
and is really on there for convenience sake. 

-- 
Albert D. Kallal   (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.attcanada.net/~kallal.msn