Dlookup seems to be slowing my form

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

"Douglas J. Steele" wrote in message
...
Yes, I did mean create a query, Tina. Thanks for the assist.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"tina" wrote in message
...
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


"Dave" dave@accessdatapros wrote in message
...
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

"Dave" dave@accessdatapros wrote in message
news 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

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

hth


"Dave" dave@accessdatapros wrote in message
...
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

"Douglas J. Steele" wrote in message
...
Yes, I did mean create a query, Tina. Thanks for the assist.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"tina" wrote in message
...
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


"Dave" dave@accessdatapros wrote in message
...
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

"Dave" dave@accessdatapros wrote in message
news 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,

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



"tina" wrote in message
...
post the SQL statement, Dave. perhaps we can help fix it.

hth


"Dave" dave@accessdatapros wrote in message
...
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

"Douglas J. Steele" wrote in message
...
Yes, I did mean create a query, Tina. Thanks for the assist.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"tina" wrote in message
...
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


"Dave" dave@accessdatapros wrote in message
...
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

"Dave" dave@accessdatapros wrote in message
news 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

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


"Dave" dave@accessdatapros wrote in message
...
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



"tina" wrote in message
...
post the SQL statement, Dave. perhaps we can help fix it.

hth


"Dave" dave@accessdatapros wrote in message
...
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

"Douglas J. Steele" wrote in
message
...
Yes, I did mean create a query, Tina. Thanks for the assist.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"tina" wrote in message
...
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


"Dave" dave@accessdatapros wrote in message
...
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

"Dave" dave@accessdatapros wrote in message
news 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

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


"tina" wrote in message
...
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


"Dave" dave@accessdatapros wrote in message
...
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



"tina" wrote in message
...
post the SQL statement, Dave. perhaps we can help fix it.

hth


"Dave" dave@accessdatapros wrote in message
...
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

"Douglas J. Steele" wrote in
message
...
Yes, I did mean create a query, Tina. Thanks for the assist.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"tina" wrote in message
...
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


"Dave" dave@accessdatapros wrote in message
...
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

"Dave" dave@accessdatapros wrote in message
news 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

you're welcome, and good job!


"Dave" dave@accessdatapros wrote in message
...
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


"tina" wrote in message
...
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


"Dave" dave@accessdatapros wrote in message
...
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



"tina" wrote in message
...
post the SQL statement, Dave. perhaps we can help fix it.

hth


"Dave" dave@accessdatapros wrote in message
...
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

"Douglas J. Steele" wrote in
message
...
Yes, I did mean create a query, Tina. Thanks for the assist.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"tina" wrote in message
...
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


"Dave" dave@accessdatapros wrote in message
...
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

"Dave" dave@accessdatapros wrote in message
news 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