dlookup vs. sql query

[asianguy35]

hello-

this is what I'm trying to do, I'm creating a search box in a main form to
search for a particular id, and when that is id is found, display all the
data in the main form and also the subforms related to that id.

I'm already got this working using dlookup, but when using this in a really
big database, it's really slow searching for that id. I tried using an sql
query to do this, but haven't had any luck getting it to work. so i'm just
wanted to find out if it makes any difference using an sql query for my
search and
whether it would be faster when used in a big database, so i don't waste my
time trying to get it to work.

any feedback would be appreciated.

Thank you,

asianguy35

 

[Mike Painter]

hello-

this is what I'm trying to do, I'm creating a search box in a main
form to search for a particular id, and when that is id is found,
display all the data in the main form and also the subforms related
to that id.

I'm already got this working using dlookup, but when using this in a
really big database, it's really slow searching for that id. I tried
using an sql query to do this, but haven't had any luck getting it to
work. so i'm just wanted to find out if it makes any difference
using an sql query for my search and
whether it would be faster when used in a big database, so i don't
waste my time trying to get it to work.

any feedback would be appreciated.

For a single lookup there should be no noticeable difference but I suspect
you are using that lookup in the search and that will slow things down.

A combobox should do what you want.

If not build the query you want in the query builder, then cut and paste it
into the event you use. Put XXX in the criteria.
You will need to convert it into a string and add me.WhatEver in place of
the XXX.

strSQL = "Select .... where SomeField = " & Me.Whatever (which may have to
be quoted)


When I do this I always use use msgbox strSQL and make sure it is correct
before running it with DoCmd.

 

[asianguy35]

Mike-

so are you saying that it's faster with a sql query then?

if so, how do i get the data to populate in the main form and subforms if
those subforms are tables
that are linked to the main form table?

thanks!

asianguy35

hello-

[quoted text clipped - 12 lines]

any feedback would be appreciated.

For a single lookup there should be no noticeable difference but I suspect
you are using that lookup in the search and that will slow things down.

A combobox should do what you want.

If not build the query you want in the query builder, then cut and paste it
into the event you use. Put XXX in the criteria.
You will need to convert it into a string and add me.WhatEver in place of
the XXX.

strSQL = "Select .... where SomeField = " & Me.Whatever (which may have to
be quoted)

When I do this I always use use msgbox strSQL and make sure it is correct
before running it with DoCmd.

 

[Mike Painter]

Yes a SQL query will be faster than running dLookup over and over again *if*
that is what you are doing.
You set the forms datasource to the results of your query.
You set the relation between form and subform in the same way you normally
would.
Either by setting the master child relationship or by changing the source of
the subform.

What is the reason you want to use a field and a lot of extra work over a
combo box and letting Access do the work?

Mike-

so are you saying that it's faster with a sql query then?

if so, how do i get the data to populate in the main form and
subforms if those subforms are tables
that are linked to the main form table?

thanks!

asianguy35

hello-

[quoted text clipped - 12 lines]

any feedback would be appreciated.

For a single lookup there should be no noticeable difference but I
suspect you are using that lookup in the search and that will slow
things down.

A combobox should do what you want.

If not build the query you want in the query builder, then cut and
paste it into the event you use. Put XXX in the criteria.
You will need to convert it into a string and add me.WhatEver in
place of the XXX.

strSQL = "Select .... where SomeField = " & Me.Whatever (which may
have to be quoted)

When I do this I always use use msgbox strSQL and make sure it is
correct before running it with DoCmd.