Overhead of lookup

[Joe Holzhauer]

I'm creating an entry form for users to create estimates. The user enters
an ItemID, and on exit from the ID field, I look up a description from a
table. I'm using DAO because I'm familiar with it, so I define a database
and recordset variable each time I call the ItemID.Exit event. Is there a
better way to do it? Am I wasting overhead by not having the db and
recordset statically (or globally) defined somewhere?

Speaking of which, I've been curious--can I use a global variable in VBA?

 

[Dirk Goldgar]

I'm creating an entry form for users to create estimates. The user
enters an ItemID, and on exit from the ID field, I look up a
description from a table. I'm using DAO because I'm familiar with
it, so I define a database and recordset variable each time I call
the ItemID.Exit event. Is there a better way to do it? Am I wasting
overhead by not having the db and recordset statically (or globally)
defined somewhere?

I'd use either the BeforeUpdate or the AfterUpdate event myself,
depending on whether there are circumstances where you want to cancel
the event and keep the user in the control. To answer your question,
it's very unlikely that the speed youi would gain from maintaining a
global database variable or recordset would be detectable by the user,
and against that you'd have to set additional code needed to manage the
global object variables. I woudn't bother.

In fact, you could use the DLookup instead of your own database and
recordset object, with no detectable slowdown. DLookup is slower than
opening your own recordset, but in this situation the difference is not
likely to be detectable.

Another possibility, which *would* be faster, would be to use a combo
box instead of a text box for entering the ItemID. The combo box could
include the description as an additional column, which would allow you
to pull it from that column into a calculated control using a
controlsource along these lines:

=[cboItemID].[Column](1)

That assumes "cboItemID" is the name of a combo box that has ItemID as
its first (and bound) column, and the description as its second column.
Note that the second column is referred to everywhere except on the
property sheet as .Column(1), not .Column(2), because the Column
property is zero-based.

Speaking of which, I've been curious--can I use a global variable in
VBA?

Sure. Did you have something unusual in mind? These days, the keyword
Public is normally used in the declaration instead of Global (though
Global is still accepted), and the variable must be declared in a
standard module, not a form, report, or class module.

 

[Joe Holzhauer]

Thanks for the advice--that's pretty crafty!

Will the BeforeUpdate & AfterUpdate events be called if the user tabs out of
a blank field?

I'm creating an entry form for users to create estimates. The user
enters an ItemID, and on exit from the ID field, I look up a
description from a table. I'm using DAO because I'm familiar with
it, so I define a database and recordset variable each time I call
the ItemID.Exit event. Is there a better way to do it? Am I wasting
overhead by not having the db and recordset statically (or globally)
defined somewhere?

I'd use either the BeforeUpdate or the AfterUpdate event myself,
depending on whether there are circumstances where you want to cancel
the event and keep the user in the control. To answer your question,
it's very unlikely that the speed youi would gain from maintaining a
global database variable or recordset would be detectable by the user,
and against that you'd have to set additional code needed to manage the
global object variables. I woudn't bother.

In fact, you could use the DLookup instead of your own database and
recordset object, with no detectable slowdown. DLookup is slower than
opening your own recordset, but in this situation the difference is not
likely to be detectable.

Another possibility, which *would* be faster, would be to use a combo
box instead of a text box for entering the ItemID. The combo box could
include the description as an additional column, which would allow you
to pull it from that column into a calculated control using a
controlsource along these lines:

=[cboItemID].[Column](1)

That assumes "cboItemID" is the name of a combo box that has ItemID as
its first (and bound) column, and the description as its second column.
Note that the second column is referred to everywhere except on the
property sheet as .Column(1), not .Column(2), because the Column
property is zero-based.

Speaking of which, I've been curious--can I use a global variable in
VBA?

Sure. Did you have something unusual in mind? These days, the keyword
Public is normally used in the declaration instead of Global (though
Global is still accepted), and the variable must be declared in a
standard module, not a form, report, or class module.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks for the advice--that's pretty crafty!

Will the BeforeUpdate & AfterUpdate events be called if the user tabs
out of a blank field?

Not if the user didn't modify the control in any way. So if you need to
trap a "tab through", you probably would do better to use the Exit
event. But that does present the problem of ensuring that the focus
enters the field in the first place. You can make that happen by
setting the focus to the control in the form's Current event.

I don't like doing things that way, though, as I prefer to let the user
navigate freely around the form's current record. Then I use the form's
BeforeUpdate event to ensure that all required fields have been filled
in.