Dlookup seems to be slowing my form

[Bob Quintal]

I have a form that is opening noticeable slow when local and
intolerable slow when on the net work.

I believe I have narrowed the cause down to the dlookups in the
query that opens the form

FirstName: DLookUp("CustomerFirst","tblCustomers","[CustomerID] =
" & [CustomerID])
LastName: DLookUp("CustomerLast","tblCustomers","[CustomerID] = "
& [CustomerID])
DeliveryAddress:
DLookUp("DeliveryAddress","tblDeliveryAddress","[DeliveryID] = " &
[DeliveryID])

Are these written wrong or inefficiently?

The Dlookups are not very efficient. That's Microsoft's doing. not
yours.

And suggestions on how to improve performance?

You could replace the Dlookups with some VB code to open the table
as a recordset, issue a seek statement, and return the values.

 

[Dave]

I have a form that is opening noticeable slow when local and intolerable
slow when on the net work.

I believe I have narrowed the cause down to the dlookups in the query that
opens the form

FirstName: DLookUp("CustomerFirst","tblCustomers","[CustomerID] = " &
[CustomerID])
LastName: DLookUp("CustomerLast","tblCustomers","[CustomerID] = " &
[CustomerID])
DeliveryAddress:
DLookUp("DeliveryAddress","tblDeliveryAddress","[DeliveryID] = " &
[DeliveryID])

Are these written wrong or inefficiently?

And suggestions on how to improve performance?

Any help here will be appreciated.

Thanks in advance
dave

 

[Douglas J. Steele]

Using DLookup in a query is usually very inefficient, and it's seldom
required.

Reading between the lines, your query is based on TableX, and you want
details from tblCustomers as well. Create a table that joins the two tables,
and pick CustomerFirst, CustomerLast and DeliveryAddress that way, rather
than using DLookup.

 

[tina]

if the DLookup() functions return data, then they're written correctly. but
consider that for every single record returned by the query, each of the
three functions must run once. that's a lot of DLookup()s running!

have you tried writing the query to include tblCustomers and
tblDeliveryAddress, joined to the main data table in the query? if you need
to be able to add/edit records in the form, try using LEFT JOIN *from* the
data table *to* each of the other two tables. and then test it out in the
query's Datasheet view, to see if the query is still updateable.

hth

 

[Dave]

Thank you both for the quick replies.

you both offered different solutions to the problem
Make another table or do it with code. (I am not sure I know how to do
either.......)

Is there a best choice of the 2?

Thanks again for the replies

dave

 

[tina]

Create a table that joins the two tables,

and pick CustomerFirst, CustomerLast and DeliveryAddress that way

i'm thinking that Doug meant to say "create a *query* that joins the two
tables". if so, he and i posted essentially the same solution. i'm not sure,
but i'm also thinking that Bob understood you to mean you're using the
DLookups in query *criteria*, while Doug and i understood you to mean you're
using the DLookups as calculated fields in the query.

how are you actually using these DLookups in the query - as fields, or as
criteria? and how many tables are we actually dealing with - 2 or 3?

hth

Thank you both for the quick replies.

you both offered different solutions to the problem
Make another table or do it with code. (I am not sure I know how to do
either.......)

Is there a best choice of the 2?

Thanks again for the replies

dave

I have a form that is opening noticeable slow when local and intolerable
slow when on the net work.

I believe I have narrowed the cause down to the dlookups in the query that
opens the form

FirstName: DLookUp("CustomerFirst","tblCustomers","[CustomerID] = " &
[CustomerID])
LastName: DLookUp("CustomerLast","tblCustomers","[CustomerID] = " &
[CustomerID])
DeliveryAddress:
DLookUp("DeliveryAddress","tblDeliveryAddress","[DeliveryID] = " &
[DeliveryID])

Are these written wrong or inefficiently?

And suggestions on how to improve performance?

Any help here will be appreciated.

Thanks in advance
dave

 

[Dave]

The dlookups are being used as "Fields" in the query.
there are 3 tables involved.
tblOrders
tblCustomers
tblDeliveryAddress

dave

Create a table that joins the two tables,
and pick CustomerFirst, CustomerLast and DeliveryAddress that way

i'm thinking that Doug meant to say "create a *query* that joins the two
tables". if so, he and i posted essentially the same solution. i'm not
sure,
but i'm also thinking that Bob understood you to mean you're using the
DLookups in query *criteria*, while Doug and i understood you to mean
you're
using the DLookups as calculated fields in the query.

how are you actually using these DLookups in the query - as fields, or as
criteria? and how many tables are we actually dealing with - 2 or 3?

hth

Thank you both for the quick replies.

you both offered different solutions to the problem
Make another table or do it with code. (I am not sure I know how to do
either.......)

Is there a best choice of the 2?

Thanks again for the replies

dave

I have a form that is opening noticeable slow when local and intolerable
slow when on the net work.

I believe I have narrowed the cause down to the dlookups in the query that
opens the form

FirstName: DLookUp("CustomerFirst","tblCustomers","[CustomerID] = " &
[CustomerID])
LastName: DLookUp("CustomerLast","tblCustomers","[CustomerID] = " &
[CustomerID])
DeliveryAddress:
DLookUp("DeliveryAddress","tblDeliveryAddress","[DeliveryID] = " &
[DeliveryID])

Are these written wrong or inefficiently?

And suggestions on how to improve performance?

Any help here will be appreciated.

Thanks in advance
dave

 

[Douglas J. Steele]

Yes, I did mean create a query, Tina. Thanks for the assist.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Create a table that joins the two tables,
and pick CustomerFirst, CustomerLast and DeliveryAddress that way

i'm thinking that Doug meant to say "create a *query* that joins the two
tables". if so, he and i posted essentially the same solution. i'm not
sure,
but i'm also thinking that Bob understood you to mean you're using the
DLookups in query *criteria*, while Doug and i understood you to mean
you're
using the DLookups as calculated fields in the query.

how are you actually using these DLookups in the query - as fields, or as
criteria? and how many tables are we actually dealing with - 2 or 3?

hth

Thank you both for the quick replies.

you both offered different solutions to the problem
Make another table or do it with code. (I am not sure I know how to do
either.......)

Is there a best choice of the 2?

Thanks again for the replies

dave

I have a form that is opening noticeable slow when local and intolerable
slow when on the net work.

I believe I have narrowed the cause down to the dlookups in the query that
opens the form

FirstName: DLookUp("CustomerFirst","tblCustomers","[CustomerID] = " &
[CustomerID])
LastName: DLookUp("CustomerLast","tblCustomers","[CustomerID] = " &
[CustomerID])
DeliveryAddress:
DLookUp("DeliveryAddress","tblDeliveryAddress","[DeliveryID] = " &
[DeliveryID])

Are these written wrong or inefficiently?

And suggestions on how to improve performance?

Any help here will be appreciated.

Thanks in advance
dave

 

[Bob Quintal]

Thank you both for the quick replies.

you both offered different solutions to the problem
Make another table or do it with code. (I am not sure I know how
to do either.......)

Is there a best choice of the 2?

Thanks again for the replies

dave

The join is going to be faster, but Access has difficulties with
their editability.

Q

I have a form that is opening noticeable slow when local and
intolerable slow when on the net work.

I believe I have narrowed the cause down to the dlookups in the
query that opens the form

FirstName: DLookUp("CustomerFirst","tblCustomers","[CustomerID] =
" & [CustomerID])
LastName: DLookUp("CustomerLast","tblCustomers","[CustomerID] = "
& [CustomerID])
DeliveryAddress:
DLookUp("DeliveryAddress","tblDeliveryAddress","[DeliveryID] = "
& [DeliveryID])

Are these written wrong or inefficiently?

And suggestions on how to improve performance?

Any help here will be appreciated.

Thanks in advance
dave

 

[Bob Quintal]

When I try to bring all 3 tables into the query I get a "ambiguous
outer join" error

I think that is why I went for the dlookups in the first place.

dave

a quick fix would be join to tblcustomers, Dlookup() on
tblDeliveryAddress

It's a compromise to be sure.


Q

Yes, I did mean create a query, Tina. Thanks for the assist.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Create a table that joins the two tables,
and pick CustomerFirst, CustomerLast and DeliveryAddress that
way

i'm thinking that Doug meant to say "create a *query* that joins
the two tables". if so, he and i posted essentially the same
solution. i'm not sure,
but i'm also thinking that Bob understood you to mean you're
using the DLookups in query *criteria*, while Doug and i
understood you to mean you're
using the DLookups as calculated fields in the query.

how are you actually using these DLookups in the query - as
fields, or as criteria? and how many tables are we actually
dealing with - 2 or 3?

hth


Thank you both for the quick replies.

you both offered different solutions to the problem
Make another table or do it with code. (I am not sure I know
how to do either.......)

Is there a best choice of the 2?

Thanks again for the replies

dave

I have a form that is opening noticeable slow when local and
intolerable
slow when on the net work.

I believe I have narrowed the cause down to the dlookups in
the query
that
opens the form

FirstName:
DLookUp("CustomerFirst","tblCustomers","[CustomerID] = " &
[CustomerID])
LastName: DLookUp("CustomerLast","tblCustomers","[CustomerID]
= " & [CustomerID])
DeliveryAddress:
DLookUp("DeliveryAddress","tblDeliveryAddress","[DeliveryID]
= " & [DeliveryID])

Are these written wrong or inefficiently?

And suggestions on how to improve performance?

Any help here will be appreciated.

Thanks in advance
dave

 

[Dave]

When I try to bring all 3 tables into the query I get a "ambiguous outer
join" error

I think that is why I went for the dlookups in the first place.

dave

Yes, I did mean create a query, Tina. Thanks for the assist.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Create a table that joins the two tables,
and pick CustomerFirst, CustomerLast and DeliveryAddress that way

i'm thinking that Doug meant to say "create a *query* that joins the two
tables". if so, he and i posted essentially the same solution. i'm not
sure,
but i'm also thinking that Bob understood you to mean you're using the
DLookups in query *criteria*, while Doug and i understood you to mean
you're
using the DLookups as calculated fields in the query.

how are you actually using these DLookups in the query - as fields, or as
criteria? and how many tables are we actually dealing with - 2 or 3?

hth

Thank you both for the quick replies.

you both offered different solutions to the problem
Make another table or do it with code. (I am not sure I know how to do
either.......)

Is there a best choice of the 2?

Thanks again for the replies

dave

I have a form that is opening noticeable slow when local and
intolerable
slow when on the net work.

I believe I have narrowed the cause down to the dlookups in the query that
opens the form

FirstName: DLookUp("CustomerFirst","tblCustomers","[CustomerID] = " &
[CustomerID])
LastName: DLookUp("CustomerLast","tblCustomers","[CustomerID] = " &
[CustomerID])
DeliveryAddress:
DLookUp("DeliveryAddress","tblDeliveryAddress","[DeliveryID] = " &
[DeliveryID])

Are these written wrong or inefficiently?

And suggestions on how to improve performance?

Any help here will be appreciated.

Thanks in advance
dave

 

[tina]

post the SQL statement, Dave. perhaps we can help fix it.

hth

When I try to bring all 3 tables into the query I get a "ambiguous outer
join" error

I think that is why I went for the dlookups in the first place.

dave

Yes, I did mean create a query, Tina. Thanks for the assist.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Create a table that joins the two tables,
and pick CustomerFirst, CustomerLast and DeliveryAddress that way

i'm thinking that Doug meant to say "create a *query* that joins the two
tables". if so, he and i posted essentially the same solution. i'm not
sure,
but i'm also thinking that Bob understood you to mean you're using the
DLookups in query *criteria*, while Doug and i understood you to mean
you're
using the DLookups as calculated fields in the query.

how are you actually using these DLookups in the query - as fields, or as
criteria? and how many tables are we actually dealing with - 2 or 3?

hth


Thank you both for the quick replies.

you both offered different solutions to the problem
Make another table or do it with code. (I am not sure I know how to do
either.......)

Is there a best choice of the 2?

Thanks again for the replies

dave

I have a form that is opening noticeable slow when local and
intolerable
slow when on the net work.

I believe I have narrowed the cause down to the dlookups in the query
that
opens the form

FirstName: DLookUp("CustomerFirst","tblCustomers","[CustomerID] = " &
[CustomerID])
LastName: DLookUp("CustomerLast","tblCustomers","[CustomerID] = " &
[CustomerID])
DeliveryAddress:
DLookUp("DeliveryAddress","tblDeliveryAddress","[DeliveryID] = " &
[DeliveryID])

Are these written wrong or inefficiently?

And suggestions on how to improve performance?

Any help here will be appreciated.

Thanks in advance
dave

 

[Dave]

Tina,

I am not sure how to post the SQL Statment. Can you tell me how to do
that?

I have taken out the dlookups

I have made a new query that combines tblCustomers and tblDeliveryAddress.

Then I have put that query into the query that pulls the form.
I am not getting my join properties correct because I am getting duplicate
invoice numbers

dave

post the SQL statement, Dave. perhaps we can help fix it.

hth

When I try to bring all 3 tables into the query I get a "ambiguous outer
join" error

I think that is why I went for the dlookups in the first place.

dave

Yes, I did mean create a query, Tina. Thanks for the assist.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Create a table that joins the two tables,
and pick CustomerFirst, CustomerLast and DeliveryAddress that way

i'm thinking that Doug meant to say "create a *query* that joins the two
tables". if so, he and i posted essentially the same solution. i'm not
sure,
but i'm also thinking that Bob understood you to mean you're using the
DLookups in query *criteria*, while Doug and i understood you to mean
you're
using the DLookups as calculated fields in the query.

how are you actually using these DLookups in the query - as fields, or as
criteria? and how many tables are we actually dealing with - 2 or 3?

hth


Thank you both for the quick replies.

you both offered different solutions to the problem
Make another table or do it with code. (I am not sure I know how to
do
either.......)

Is there a best choice of the 2?

Thanks again for the replies

dave

I have a form that is opening noticeable slow when local and
intolerable
slow when on the net work.

I believe I have narrowed the cause down to the dlookups in the query
that
opens the form

FirstName: DLookUp("CustomerFirst","tblCustomers","[CustomerID] = " &
[CustomerID])
LastName: DLookUp("CustomerLast","tblCustomers","[CustomerID] = " &
[CustomerID])
DeliveryAddress:
DLookUp("DeliveryAddress","tblDeliveryAddress","[DeliveryID] = " &
[DeliveryID])

Are these written wrong or inefficiently?

And suggestions on how to improve performance?

Any help here will be appreciated.

Thanks in advance
dave

 

[tina]

to get to the SQL, open a query in Design view and, from the menu bar, click
View | SQL View. that opens the SQL pane; just highlight *all* the text in
the pane, and paste into a post.

since you're now working with two queries, suggest you post the SQL
statement of each.

hth

Tina,

I am not sure how to post the SQL Statment. Can you tell me how to do
that?

I have taken out the dlookups

I have made a new query that combines tblCustomers and tblDeliveryAddress.

Then I have put that query into the query that pulls the form.
I am not getting my join properties correct because I am getting duplicate
invoice numbers

dave

post the SQL statement, Dave. perhaps we can help fix it.

hth

When I try to bring all 3 tables into the query I get a "ambiguous outer
join" error

I think that is why I went for the dlookups in the first place.

dave

Yes, I did mean create a query, Tina. Thanks for the assist.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Create a table that joins the two tables,
and pick CustomerFirst, CustomerLast and DeliveryAddress that way

i'm thinking that Doug meant to say "create a *query* that joins the two
tables". if so, he and i posted essentially the same solution. i'm not
sure,
but i'm also thinking that Bob understood you to mean you're using the
DLookups in query *criteria*, while Doug and i understood you to mean
you're
using the DLookups as calculated fields in the query.

how are you actually using these DLookups in the query - as fields,

or
as

criteria? and how many tables are we actually dealing with - 2 or 3?

hth


Thank you both for the quick replies.

you both offered different solutions to the problem
Make another table or do it with code. (I am not sure I know how to
do
either.......)

Is there a best choice of the 2?

Thanks again for the replies

dave

I have a form that is opening noticeable slow when local and
intolerable
slow when on the net work.

I believe I have narrowed the cause down to the dlookups in the query
that
opens the form

FirstName: DLookUp("CustomerFirst","tblCustomers","[CustomerID] =

"
&

[CustomerID])
LastName: DLookUp("CustomerLast","tblCustomers","[CustomerID] = " &
[CustomerID])
DeliveryAddress:
DLookUp("DeliveryAddress","tblDeliveryAddress","[DeliveryID] = " &
[DeliveryID])

Are these written wrong or inefficiently?

And suggestions on how to improve performance?

Any help here will be appreciated.

Thanks in advance
dave

 

[Dave]

Sorry Tina, I did not see your reply for some reason.
seems I have got it working now and the issue was that I needed 2
connections in one of the queries instead of just 1.
Thanks again
This group is great - it is not the first time you and a few of the other
repliers here have pitched in to help me.

Dave

to get to the SQL, open a query in Design view and, from the menu bar,
click
View | SQL View. that opens the SQL pane; just highlight *all* the text in
the pane, and paste into a post.

since you're now working with two queries, suggest you post the SQL
statement of each.

hth

Tina,

I am not sure how to post the SQL Statment. Can you tell me how to do
that?

I have taken out the dlookups

I have made a new query that combines tblCustomers and
tblDeliveryAddress.

Then I have put that query into the query that pulls the form.
I am not getting my join properties correct because I am getting
duplicate
invoice numbers

dave

post the SQL statement, Dave. perhaps we can help fix it.

hth


When I try to bring all 3 tables into the query I get a "ambiguous outer
join" error

I think that is why I went for the dlookups in the first place.

dave

Yes, I did mean create a query, Tina. Thanks for the assist.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Create a table that joins the two tables,
and pick CustomerFirst, CustomerLast and DeliveryAddress that way

i'm thinking that Doug meant to say "create a *query* that joins
the
two
tables". if so, he and i posted essentially the same solution. i'm not
sure,
but i'm also thinking that Bob understood you to mean you're using the
DLookups in query *criteria*, while Doug and i understood you to mean
you're
using the DLookups as calculated fields in the query.

how are you actually using these DLookups in the query - as fields, or
as
criteria? and how many tables are we actually dealing with - 2 or
3?

hth


Thank you both for the quick replies.

you both offered different solutions to the problem
Make another table or do it with code. (I am not sure I know how
to
do
either.......)

Is there a best choice of the 2?

Thanks again for the replies

dave

I have a form that is opening noticeable slow when local and
intolerable
slow when on the net work.

I believe I have narrowed the cause down to the dlookups in the
query
that
opens the form

FirstName: DLookUp("CustomerFirst","tblCustomers","[CustomerID]
= "
&
[CustomerID])
LastName: DLookUp("CustomerLast","tblCustomers","[CustomerID] =
" &
[CustomerID])
DeliveryAddress:
DLookUp("DeliveryAddress","tblDeliveryAddress","[DeliveryID] = " &
[DeliveryID])

Are these written wrong or inefficiently?

And suggestions on how to improve performance?

Any help here will be appreciated.

Thanks in advance
dave

 

[tina]

you're welcome, and good job!

Sorry Tina, I did not see your reply for some reason.
seems I have got it working now and the issue was that I needed 2
connections in one of the queries instead of just 1.
Thanks again
This group is great - it is not the first time you and a few of the other
repliers here have pitched in to help me.

Dave

to get to the SQL, open a query in Design view and, from the menu bar,
click
View | SQL View. that opens the SQL pane; just highlight *all* the text in
the pane, and paste into a post.

since you're now working with two queries, suggest you post the SQL
statement of each.

hth

Tina,

I am not sure how to post the SQL Statment. Can you tell me how to do
that?

I have taken out the dlookups

I have made a new query that combines tblCustomers and
tblDeliveryAddress.

Then I have put that query into the query that pulls the form.
I am not getting my join properties correct because I am getting
duplicate
invoice numbers

dave



post the SQL statement, Dave. perhaps we can help fix it.

hth


When I try to bring all 3 tables into the query I get a "ambiguous outer
join" error

I think that is why I went for the dlookups in the first place.

dave

Yes, I did mean create a query, Tina. Thanks for the assist.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Create a table that joins the two tables,
and pick CustomerFirst, CustomerLast and DeliveryAddress that way

i'm thinking that Doug meant to say "create a *query* that joins
the
two
tables". if so, he and i posted essentially the same solution.

i'm
not

sure,
but i'm also thinking that Bob understood you to mean you're

using
the

DLookups in query *criteria*, while Doug and i understood you to mean
you're
using the DLookups as calculated fields in the query.

how are you actually using these DLookups in the query - as

fields,
or

as
criteria? and how many tables are we actually dealing with - 2 or
3?

hth


Thank you both for the quick replies.

you both offered different solutions to the problem
Make another table or do it with code. (I am not sure I know how
to
do
either.......)

Is there a best choice of the 2?

Thanks again for the replies

dave

I have a form that is opening noticeable slow when local and
intolerable
slow when on the net work.

I believe I have narrowed the cause down to the dlookups in the
query
that
opens the form

FirstName: DLookUp("CustomerFirst","tblCustomers","[CustomerID]
= "
&
[CustomerID])
LastName: DLookUp("CustomerLast","tblCustomers","[CustomerID] =
" &
[CustomerID])
DeliveryAddress:
DLookUp("DeliveryAddress","tblDeliveryAddress","[DeliveryID] =

"
&

[DeliveryID])

Are these written wrong or inefficiently?

And suggestions on how to improve performance?

Any help here will be appreciated.

Thanks in advance
dave