Most efficient way to see if a value exists

[Andrew]

Hi all

I need to determine whether a value typed into a text box in a form
exists in a related table, and I'd be interested in the thoughts of
those who know more than me on what is the most efficient way.

2 ways spring to my mind...

-----
Dim rs as ADODB.Recordset
set rs=new ADODB.Recordset
with rs
.ActiveConnection=CurrentProject.Connection
.Source="SELECT * from tblRelatedTable where strCode='" & txtCode &
"'"
.Open
If .EOF then _
'We know that there are no related records, handle it.
.Close
end with
Set rs=nothing
----

dim varMatch as variant
varMatch=dlookup("strCode","tblRelatedTable","strCode='" & txtCode &
"'")
if isnull(varMatch) then _
'We know that there are no related records, hadle it

---


Logic would suggest that the second of these ways would be quicker,
but somehow I instinctively want to go the first way.... probably just
my predilection for using 20 lines of code where 1 would do. But can
anyone give me a definitive answer on this? Or is there another way
which would be better yet?

Many thanks

Andrew

 

[Jeff Boyce]

Andrew

Consider a third option...

Forcing your users to "type [a value] into a text box", then using code to
look up whether they typed a valid number and having the computer tell them
"Nope, you dummy, try again!" is not particularly user-friendly.

As an alternate approach, use a combo box that lists all available values.
That way, the user has a higher likelihood of selecting a correct value.
And you can use that combobox's LimitToList property and NotInList event to
manage the user adding a new value, if necessary.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Andrew]

Andrew

Consider a third option...

Forcing your users to "type [a value] into a text box", then using code to
look up whether they typed a valid number and having the computer tell them
"Nope, you dummy, try again!" is not particularly user-friendly.

As an alternate approach, use a combo box that lists all available values.
That way, the user has a higher likelihood of selecting a correct value.
And you can use that combobox's LimitToList property and NotInList event to
manage the user adding a new value, if necessary.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all

I need to determine whether a value typed into a text box in a form
exists in a related table, and I'd be interested in the thoughts of
those who know more than me on what is the most efficient way.

2 ways spring to my mind...

-----
Dim rs as ADODB.Recordset
set rs=new ADODB.Recordset
with rs
.ActiveConnection=CurrentProject.Connection
.Source="SELECT * from tblRelatedTable where strCode='" & txtCode &
"'"
.Open
If .EOF then _
'We know that there are no related records, handle it.
.Close
end with
Set rs=nothing
----

dim varMatch as variant
varMatch=dlookup("strCode","tblRelatedTable","strCode='" & txtCode &
"'")
if isnull(varMatch) then _
'We know that there are no related records, hadle it

Logic would suggest that the second of these ways would be quicker,
but somehow I instinctively want to go the first way.... probably just
my predilection for using 20 lines of code where 1 would do. But can
anyone give me a definitive answer on this? Or is there another way
which would be better yet?

Many thanks

Andrew- Hide quoted text -

- Show quoted text -

Yes, thanks Jeff. This was another solution I considered, but I didn't
want the overhead of a combo box which always has to run its query to
be populated, and which would have several thousand rows in it. As
things stand, I have the text box, but I've also given a button
labelled "Lookup" which opens a little pop-up form with a combo box
exactly as you described. In the company for whom I am putting
together this application, most the staff use the codes all the time
(they are catalogue numbers for CDs and DVDs, and the staff usually
refer to a release by its Cat no. rather than the name) and so
generally wouldn't need that automatic search capability of the combo
box. But I still wanted to check what they typed instead of generating
the standard error, which isn't particularly intuitive.

Maybe I'm just barking up the wrong tree. Maybe the overhead of this
"check" is just as great as the overhead of populating that combo box
to begin with,... But I'm interested in which is the more efficient
option anyway....

Thanks for your input, though.

 

[Pieter Wijnen]

Dlookup is ultra slow compared to the ADO approach
which will be (slightly) quicker still if you use a stored procedure
(parameter query) to avoid the overhead involved in parsing the statement
for each execution...

HTH

Pieter

Andrew

Consider a third option...

Forcing your users to "type [a value] into a text box", then using code
to
look up whether they typed a valid number and having the computer tell
them
"Nope, you dummy, try again!" is not particularly user-friendly.

As an alternate approach, use a combo box that lists all available
values.
That way, the user has a higher likelihood of selecting a correct value.
And you can use that combobox's LimitToList property and NotInList event
to
manage the user adding a new value, if necessary.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all

I need to determine whether a value typed into a text box in a form
exists in a related table, and I'd be interested in the thoughts of
those who know more than me on what is the most efficient way.

2 ways spring to my mind...

-----
Dim rs as ADODB.Recordset
set rs=new ADODB.Recordset
with rs
.ActiveConnection=CurrentProject.Connection
.Source="SELECT * from tblRelatedTable where strCode='" & txtCode &
"'"
.Open
If .EOF then _
'We know that there are no related records, handle it.
.Close
end with
Set rs=nothing
----

dim varMatch as variant
varMatch=dlookup("strCode","tblRelatedTable","strCode='" & txtCode &
"'")
if isnull(varMatch) then _
'We know that there are no related records, hadle it

Logic would suggest that the second of these ways would be quicker,
but somehow I instinctively want to go the first way.... probably just
my predilection for using 20 lines of code where 1 would do. But can
anyone give me a definitive answer on this? Or is there another way
which would be better yet?

Many thanks

Andrew- Hide quoted text -

- Show quoted text -

Yes, thanks Jeff. This was another solution I considered, but I didn't
want the overhead of a combo box which always has to run its query to
be populated, and which would have several thousand rows in it. As
things stand, I have the text box, but I've also given a button
labelled "Lookup" which opens a little pop-up form with a combo box
exactly as you described. In the company for whom I am putting
together this application, most the staff use the codes all the time
(they are catalogue numbers for CDs and DVDs, and the staff usually
refer to a release by its Cat no. rather than the name) and so
generally wouldn't need that automatic search capability of the combo
box. But I still wanted to check what they typed instead of generating
the standard error, which isn't particularly intuitive.

Maybe I'm just barking up the wrong tree. Maybe the overhead of this
"check" is just as great as the overhead of populating that combo box
to begin with,... But I'm interested in which is the more efficient
option anyway....

Thanks for your input, though.

 

[Andrew]

Dlookup is ultra slow compared to the ADO approach
which will be (slightly) quicker still if you use a stored procedure
(parameter query) to avoid the overhead involved in parsing the statement
for each execution...

HTH

Pieter

Andrew
Consider a third option...
Forcing your users to "type [a value] into a text box", then using code
to
look up whether they typed a valid number and having the computer tell
them
"Nope, you dummy, try again!" is not particularly user-friendly.
As an alternate approach, use a combo box that lists all available
values.
That way, the user has a higher likelihood of selecting a correct value.
And you can use that combobox's LimitToList property and NotInList event
to
manage the user adding a new value, if necessary.
Regards
Jeff Boyce
Microsoft Office/Access MVP

Hi all
I need to determine whether a value typed into a text box in a form
exists in a related table, and I'd be interested in the thoughts of
those who know more than me on what is the most efficient way.
2 ways spring to my mind...
-----
Dim rs as ADODB.Recordset
set rs=new ADODB.Recordset
with rs
.ActiveConnection=CurrentProject.Connection
.Source="SELECT * from tblRelatedTable where strCode='" & txtCode &
"'"
.Open
If .EOF then _
'We know that there are no related records, handle it.
.Close
end with
Set rs=nothing
----
dim varMatch as variant
varMatch=dlookup("strCode","tblRelatedTable","strCode='" & txtCode &
"'")
if isnull(varMatch) then _
'We know that there are no related records, hadle it
---
Logic would suggest that the second of these ways would be quicker,
but somehow I instinctively want to go the first way.... probably just
my predilection for using 20 lines of code where 1 would do. But can
anyone give me a definitive answer on this? Or is there another way
which would be better yet?
Many thanks
Andrew- Hide quoted text -
- Show quoted text -

Yes, thanks Jeff. This was another solution I considered, but I didn't
want the overhead of a combo box which always has to run its query to
be populated, and which would have several thousand rows in it. As
things stand, I have the text box, but I've also given a button
labelled "Lookup" which opens a little pop-up form with a combo box
exactly as you described. In the company for whom I am putting
together this application, most the staff use the codes all the time
(they are catalogue numbers for CDs and DVDs, and the staff usually
refer to a release by its Cat no. rather than the name) and so
generally wouldn't need that automatic search capability of the combo
box. But I still wanted to check what they typed instead of generating
the standard error, which isn't particularly intuitive.

Maybe I'm just barking up the wrong tree. Maybe the overhead of this
"check" is just as great as the overhead of populating that combo box
to begin with,... But I'm interested in which is the more efficient
option anyway....

Thanks for your input, though.- Hide quoted text -

- Show quoted text -

OKay. That's what I thought, but I didn't know why I thought it, and
wanted to get someone else's opinion.

Thanks a lot

Andrew

 

[Jeff Boyce]

Andrew

Since there are so many other factors (network speed, ?"split" design, any
advice you get probably still needs to be tested/confirmed in your
environment.

A colleague, Allen Browne, offers a nifty little variation on using a combo
box when there are "tens of thousands" of rows. Instead of firing when the
form loads, the combo box "waits" until the user has typed in "n" (you pick
the "n") characters, then does the query with the already-entered characters
as a WHERE clause.

http://allenbrowne.com/ser-32.html

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Andrew

Consider a third option...

Forcing your users to "type [a value] into a text box", then using code
to
look up whether they typed a valid number and having the computer tell
them
"Nope, you dummy, try again!" is not particularly user-friendly.

As an alternate approach, use a combo box that lists all available
values.
That way, the user has a higher likelihood of selecting a correct value.
And you can use that combobox's LimitToList property and NotInList event
to
manage the user adding a new value, if necessary.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all

I need to determine whether a value typed into a text box in a form
exists in a related table, and I'd be interested in the thoughts of
those who know more than me on what is the most efficient way.

2 ways spring to my mind...

-----
Dim rs as ADODB.Recordset
set rs=new ADODB.Recordset
with rs
.ActiveConnection=CurrentProject.Connection
.Source="SELECT * from tblRelatedTable where strCode='" & txtCode &
"'"
.Open
If .EOF then _
'We know that there are no related records, handle it.
.Close
end with
Set rs=nothing
----

dim varMatch as variant
varMatch=dlookup("strCode","tblRelatedTable","strCode='" & txtCode &
"'")
if isnull(varMatch) then _
'We know that there are no related records, hadle it

Logic would suggest that the second of these ways would be quicker,
but somehow I instinctively want to go the first way.... probably just
my predilection for using 20 lines of code where 1 would do. But can
anyone give me a definitive answer on this? Or is there another way
which would be better yet?

Many thanks

Andrew- Hide quoted text -

- Show quoted text -

Yes, thanks Jeff. This was another solution I considered, but I didn't
want the overhead of a combo box which always has to run its query to
be populated, and which would have several thousand rows in it. As
things stand, I have the text box, but I've also given a button
labelled "Lookup" which opens a little pop-up form with a combo box
exactly as you described. In the company for whom I am putting
together this application, most the staff use the codes all the time
(they are catalogue numbers for CDs and DVDs, and the staff usually
refer to a release by its Cat no. rather than the name) and so
generally wouldn't need that automatic search capability of the combo
box. But I still wanted to check what they typed instead of generating
the standard error, which isn't particularly intuitive.

Maybe I'm just barking up the wrong tree. Maybe the overhead of this
"check" is just as great as the overhead of populating that combo box
to begin with,... But I'm interested in which is the more efficient
option anyway....

Thanks for your input, though.

 

[Andrew]

Andrew

Since there are so many other factors (network speed, ?"split" design, any
advice you get probably still needs to be tested/confirmed in your
environment.

A colleague, Allen Browne, offers a nifty little variation on using a combo
box when there are "tens of thousands" of rows. Instead of firing when the
form loads, the combo box "waits" until the user has typed in "n" (you pick
the "n") characters, then does the query with the already-entered characters
as a WHERE clause.

http://allenbrowne.com/ser-32.html

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Andrew
Consider a third option...
Forcing your users to "type [a value] into a text box", then using code
to
look up whether they typed a valid number and having the computer tell
them
"Nope, you dummy, try again!" is not particularly user-friendly.
As an alternate approach, use a combo box that lists all available
values.
That way, the user has a higher likelihood of selecting a correct value.
And you can use that combobox's LimitToList property and NotInList event
to
manage the user adding a new value, if necessary.
Regards
Jeff Boyce
Microsoft Office/Access MVP

Hi all
I need to determine whether a value typed into a text box in a form
exists in a related table, and I'd be interested in the thoughts of
those who know more than me on what is the most efficient way.
2 ways spring to my mind...
-----
Dim rs as ADODB.Recordset
set rs=new ADODB.Recordset
with rs
.ActiveConnection=CurrentProject.Connection
.Source="SELECT * from tblRelatedTable where strCode='" & txtCode &
"'"
.Open
If .EOF then _
'We know that there are no related records, handle it.
.Close
end with
Set rs=nothing
----
dim varMatch as variant
varMatch=dlookup("strCode","tblRelatedTable","strCode='" & txtCode &
"'")
if isnull(varMatch) then _
'We know that there are no related records, hadle it
---
Logic would suggest that the second of these ways would be quicker,
but somehow I instinctively want to go the first way.... probably just
my predilection for using 20 lines of code where 1 would do. But can
anyone give me a definitive answer on this? Or is there another way
which would be better yet?
Many thanks
Andrew- Hide quoted text -
- Show quoted text -

Yes, thanks Jeff. This was another solution I considered, but I didn't
want the overhead of a combo box which always has to run its query to
be populated, and which would have several thousand rows in it. As
things stand, I have the text box, but I've also given a button
labelled "Lookup" which opens a little pop-up form with a combo box
exactly as you described. In the company for whom I am putting
together this application, most the staff use the codes all the time
(they are catalogue numbers for CDs and DVDs, and the staff usually
refer to a release by its Cat no. rather than the name) and so
generally wouldn't need that automatic search capability of the combo
box. But I still wanted to check what they typed instead of generating
the standard error, which isn't particularly intuitive.

Maybe I'm just barking up the wrong tree. Maybe the overhead of this
"check" is just as great as the overhead of populating that combo box
to begin with,... But I'm interested in which is the more efficient
option anyway....

Thanks for your input, though.- Hide quoted text -

- Show quoted text -

Thanks for that Jeff. I've seen something similar done in John
Viescas' book "Building Access Applications".

Incidentally, can you recommend a reliable way of testing speed of
execution of code like this? Is a call to the Win API timeGetTime
function the best bet? Or do you know of utilities that are perhaps
slightly more user-friendly?

Andrew

 

[Jeff Boyce]

Andrew

While I'm sure you can find some highly-accurate timers (I'd Google for
'em), I generally rely on user-impression. I haven't run across a user yet
who can tell the difference between a response time of .447 seconds and .503
seconds (and still, given all the other potential factors, the actual
'fetch' portion of the total response time might be a couple milliseconds!).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Andrew

Since there are so many other factors (network speed, ?"split" design,
any
advice you get probably still needs to be tested/confirmed in your
environment.

A colleague, Allen Browne, offers a nifty little variation on using a
combo
box when there are "tens of thousands" of rows. Instead of firing when
the
form loads, the combo box "waits" until the user has typed in "n" (you
pick
the "n") characters, then does the query with the already-entered
characters
as a WHERE clause.

http://allenbrowne.com/ser-32.html

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Andrew

Consider a third option...

Forcing your users to "type [a value] into a text box", then using
code
to
look up whether they typed a valid number and having the computer tell
them
"Nope, you dummy, try again!" is not particularly user-friendly.

As an alternate approach, use a combo box that lists all available
values.
That way, the user has a higher likelihood of selecting a correct
value.
And you can use that combobox's LimitToList property and NotInList
event
to
manage the user adding a new value, if necessary.

Jeff Boyce
Microsoft Office/Access MVP

Hi all

I need to determine whether a value typed into a text box in a form
exists in a related table, and I'd be interested in the thoughts of
those who know more than me on what is the most efficient way.

2 ways spring to my mind...

-----
Dim rs as ADODB.Recordset
set rs=new ADODB.Recordset
with rs
.ActiveConnection=CurrentProject.Connection
.Source="SELECT * from tblRelatedTable where strCode='" & txtCode
&
"'"
.Open
If .EOF then _
'We know that there are no related records, handle it.
.Close
end with
Set rs=nothing
----

dim varMatch as variant
varMatch=dlookup("strCode","tblRelatedTable","strCode='" & txtCode &
"'")
if isnull(varMatch) then _
'We know that there are no related records, hadle it

Logic would suggest that the second of these ways would be quicker,
but somehow I instinctively want to go the first way.... probably
just
my predilection for using 20 lines of code where 1 would do. But can
anyone give me a definitive answer on this? Or is there another way
which would be better yet?

Many thanks

Andrew- Hide quoted text -

- Show quoted text -

Yes, thanks Jeff. This was another solution I considered, but I didn't
want the overhead of a combo box which always has to run its query to
be populated, and which would have several thousand rows in it. As
things stand, I have the text box, but I've also given a button
labelled "Lookup" which opens a little pop-up form with a combo box
exactly as you described. In the company for whom I am putting
together this application, most the staff use the codes all the time
(they are catalogue numbers for CDs and DVDs, and the staff usually
refer to a release by its Cat no. rather than the name) and so
generally wouldn't need that automatic search capability of the combo
box. But I still wanted to check what they typed instead of generating
the standard error, which isn't particularly intuitive.

Maybe I'm just barking up the wrong tree. Maybe the overhead of this
"check" is just as great as the overhead of populating that combo box
to begin with,... But I'm interested in which is the more efficient
option anyway....

Thanks for your input, though.- Hide quoted text -

- Show quoted text -

Thanks for that Jeff. I've seen something similar done in John
Viescas' book "Building Access Applications".

Incidentally, can you recommend a reliable way of testing speed of
execution of code like this? Is a call to the Win API timeGetTime
function the best bet? Or do you know of utilities that are perhaps
slightly more user-friendly?

Andrew