To WITH or not to WITH?

[lauren quantrell]

I'm wondering if there is any speed advantage or disadvantage between
making use of WITH statements or not.

For example, will:

WITH Forms!myFormName
..ctlA = x
..ctlB = y
..ctlC = z
END WITH

be any better/worse than:

Forms!myFormName.ctlA = x
Forms!myFormName.ctlB = y
Forms!myFormName.ctlC = z

Thanks,
lq

[Rick Brandt]

lauren quantrell wrote:

I'm wondering if there is any speed advantage or disadvantage between
making use of WITH statements or not.

For example, will:

WITH Forms!myFormName
.ctlA = x
.ctlB = y
.ctlC = z
END WITH

be any better/worse than:

Forms!myFormName.ctlA = x
Forms!myFormName.ctlB = y
Forms!myFormName.ctlC = z

Thanks,
lq

My understanding is that With helps performance if it is eliminating Bangs (!)
or Dots (.). In your case it is eliminating one bang (per line) so it should be
better than the second example you have, but perhaps only marginally since only
one bang level is removed. If the With reference was a great long one with many
bang/dot levels then the difference would be bigger.

I often use With just for increased readability of the code even if I am not
eliminating any bangs or dots so I think it is still worthwhile even if
performance is not improved.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[David W. Fenton]

"Rick Brandt" <ri*********@hotmail.com> wrote in
news:FR***************@newssvr25.news.prodigy.net:

lauren quantrell wrote:

I'm wondering if there is any speed advantage or disadvantage
between making use of WITH statements or not.

For example, will:

WITH Forms!myFormName
.ctlA = x
.ctlB = y
.ctlC = z
END WITH

be any better/worse than:

Forms!myFormName.ctlA = x
Forms!myFormName.ctlB = y
Forms!myFormName.ctlC = z

My understanding is that With helps performance if it is
eliminating Bangs (!) or Dots (.). In your case it is eliminating
one bang (per line) so it should be better than the second example
you have, but perhaps only marginally since only one bang level is
removed. If the With reference was a great long one with many
bang/dot levels then the difference would be bigger.

I often use With just for increased readability of the code even
if I am not eliminating any bangs or dots so I think it is still
worthwhile even if performance is not improved.

I don't know about the ! vs. . distinction (I never use . for
controls and think it's bad coding style), but I do know that if you
fully refer to the form every single time, you're forcing a lookup
in the forms collection for every single line. The following lines
of code take just as long look up the form reference as referring to
the same form on each line:

Forms!Form1!ctlA = x
Forms!Form2!ctlB = y
Forms!Form3!ctlC = z

That is, the above is no slower than:

Forms!Form1!ctlA = x
Forms!Form1!ctlB = y
Forms!Form1!ctlC = z

Each line has to look up to see what the form is.

One method to avoid that is to set a form variable so you look it up
once:

Dim frm As Form

Set frm = Forms!Form1
frm!ctlA = x
frm!ctlB = y
frm!ctlC = z
Set frm = Nothing

I don't do that because it means having to clean up the reference
when you're done. WITH is much cleaner because of that:

With Forms!Form1
!ctlA = x
!ctlB = y
!ctlC = z
End With

With proper indentation, it also helps make code easier to read,
seems to me.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[lylefair]

There might be a minor speed advantage to "WITH". Usually speed is
faster when we have fewer dots, and fewer object variables to look up.

The fastest way I know to assign value to form controls (and in general
this pattern works for everything for all objects) is to create an
object pointer to the !!!!specific!!!! kind of control, to initialize
it, and to assign its value as follows:

Dim c As TextBox
Set c = Form_Form1.Controls(0)
c = "Jennie"

Of course, if one is setting the value only once then the speed
advantage of this method may be offset by having to create the object
reference; then again it may not, as setting an object reference is
extremely fast. But unless we are setting these values thousands of
times it's very unlikely that we will notice any time difference at
all.

In general, if one is doing intensive coding, data manipulation,
whatever, it may be worthwhile to declare EVERY object used, and to
initialize the object as soon as possible. In complex calculations,
such a strategy may result in very great speed advantages, eg something
that took 10 minutes, may take 5 seconds after this is applied.

[lauren quantrell]

David,
Thanks for the thoughtful reply (as always!)

What I have been doing is:

Dim myFrm as String
myFrm = "myFormName"
Forms(myFrm).ctlA = x

instead of what you suggest:

Dim frm As Form
Set frm = Forms!Form1
frm!ctlA = x

Am I screwed doing it the way I have been?
lq

[Steve Jorgensen]

On 19 Jul 2005 21:24:06 -0700, "lauren quantrell"
<la*************@hotmail.com> wrote:

David,
Thanks for the thoughtful reply (as always!)

What I have been doing is:

Dim myFrm as String
myFrm = "myFormName"
Forms(myFrm).ctlA = x

instead of what you suggest:

Dim frm As Form
Set frm = Forms!Form1
frm!ctlA = x

Am I screwed doing it the way I have been?
lq

There are bugs in some cases (particularly with ADO) if you assign a reference
to an object, and let it go out of scope without setting it to Nothing. As a
result, some of us have made it a best practice to always set such references
to Nothing or use a With block for the equivalent effect.

Personally, I don't do that, and I don't have problems. Specifically, I've
never seen any negative side effects from writing code like your example
above, and I consider that to be totally safe.

The cases where you do have to be careful to release references seem to be
when you're holding references to multiple objects in a COM library, one of
those depends on the other (such as a Database object and a Recordset object),
and they both go out of scope at the same point in your code. There is no way
to make sure VB will release them in proper reverse-dependency order except to
force the issue, and if they don't get released in the right order, you can
get resource leaks.

I have no inside-information to tell me that what I said in that last
paragraph is correct, but my experience indicates that it's at least an
effective model to work by, whether it's an accurate picture of what's
happening under the hood or not.

[Rick Brandt]

lauren quantrell wrote:

David,
Thanks for the thoughtful reply (as always!)

What I have been doing is:

Dim myFrm as String
myFrm = "myFormName"
Forms(myFrm).ctlA = x

instead of what you suggest:

Dim frm As Form
Set frm = Forms!Form1
frm!ctlA = x

Am I screwed doing it the way I have been?
lq

You are getting no benefit from it except perhaps if the real form name is
longer then you are saving a few key strokes.

David's method only searches the Forms collection once no matter how many times
he references something on the form. You method is still looking in the Forms
collection every time.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Terry Kreft]

A quick description of what is going on with each bit of code may illustrate
it best.

Firstly
-----------
WITH Forms!myFormName
.ctlA = x
.ctlB = y
.ctlC = z
END WITH

This translates as:-
Get a pointer to the Forms collection
Iterate the Forms collection to get a pointer to myFormName
Iterate the controls collection of myFormName to get a pointer
to ctlA
Set the default property of ctlA to x

Iterate the controls collection of myFormName to get a pointer
to ctlB
Set the default property of ctlB to y

Iterate the controls collection of myFormName to get a pointer
to ctlA
Set the default property of ctlB to z

Secondly
-----------
Forms!myFormName.ctlA = x
Forms!myFormName.ctlB = y
Forms!myFormName.ctlC = z

This translates as:-

Get a pointer to the Forms collection
Iterate the Forms collection to get a pointer to myFormName
Iterate the controls collection of myFormName to get a pointer
to ctlA
Set the default property of ctlA to x

Get a pointer to the Forms collection
Iterate the Forms collection to get a pointer to myFormName
Iterate the controls collection of myFormName to get a pointer
to ctlB
Set the default property of ctlB to y
Get a pointer to the Forms collection
Iterate the Forms collection to get a pointer to myFormName
Iterate the controls collection of myFormName to get a pointer
to ctlA
Set the default property of ctlB to z
An analogy could be visiting a library to get out three books on Access
programming, you can either

Enter the library
Find the Access programming section
Find book A
Find book B
Find book C

or
Enter the library
Find the Access programming section
Find book A
Enter the library
Find the Access programming section
Find book B
Enter the library
Find the Access programming section
Find book C

The first way is more efficient and quicker.

--
Terry Kreft
MVP Microsoft Access
"lauren quantrell" <la*************@hotmail.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...

I'm wondering if there is any speed advantage or disadvantage between
making use of WITH statements or not.

For example, will:

WITH Forms!myFormName
.ctlA = x
.ctlB = y
.ctlC = z
END WITH

be any better/worse than:

Forms!myFormName.ctlA = x
Forms!myFormName.ctlB = y
Forms!myFormName.ctlC = z

Thanks,
lq

[David W. Fenton]

"lauren quantrell" <la*************@hotmail.com> wrote in
news:11**********************@g43g2000cwa.googlegr oups.com:

What I have been doing is:

Dim myFrm as String
myFrm = "myFormName"
Forms(myFrm).ctlA = x

instead of what you suggest:

Dim frm As Form
Set frm = Forms!Form1
frm!ctlA = x

Am I screwed doing it the way I have been?

You're gaining absolutely nothing, since

Forms("myForm")

and

Forms!myForm

are doing exactly the same thing.

As I said, I would *never* do what you say I suggest, which is SET a
form variable -- instead, I'd use a WITH. The only time I'd use a
form variable would be when a WITH is impossible (e.g., when I'm
using WITH for working wtih some other object).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Steve Jorgensen <no****@nospam.nospam> wrote in
news:30********************************@4ax.com:

On 19 Jul 2005 21:24:06 -0700, "lauren quantrell"
<la*************@hotmail.com> wrote:

David,
Thanks for the thoughtful reply (as always!)

What I have been doing is:

Dim myFrm as String
myFrm = "myFormName"
Forms(myFrm).ctlA = x

instead of what you suggest:

Dim frm As Form
Set frm = Forms!Form1
frm!ctlA = x

Am I screwed doing it the way I have been?
lq

There are bugs in some cases (particularly with ADO) if you assign
a reference to an object, and let it go out of scope without
setting it to Nothing. As a result, some of us have made it a
best practice to always set such references to Nothing or use a
With block for the equivalent effect.

Personally, I don't do that, and I don't have problems.
Specifically, I've never seen any negative side effects from
writing code like your example above, and I consider that to be
totally safe.

MichKa advised once that you should set to Nothing even in this
situation:

Dim ctl As Control

For Each ctl In Me.Controls
[do whatever]
Next ctl

Set ctl = Nothing

That's because he said the last loop could maintain an implicit open
reference to the last control.

That would seem to me to not happen under the rules you are
adducing, but MichKa knows more about this than I do.

The time it would take to chase down an unreleased reference is
going to be much greater than the time it takes me, by habit, to set
all such variables to Nothing at the end of each subroutine where
I'm using them.

Setting to Nothing has no downside, and the potential upside could
be huge, so I do it.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"ly******@yahoo.ca" <ly******@yahoo.ca> wrote in
news:11*********************@z14g2000cwz.googlegro ups.com:

There might be a minor speed advantage to "WITH". Usually speed is
faster when we have fewer dots, and fewer object variables to look
up.

The fastest way I know to assign value to form controls (and in
general this pattern works for everything for all objects) is to
create an object pointer to the !!!!specific!!!! kind of control,
to initialize it, and to assign its value as follows:

Dim c As TextBox
Set c = Form_Form1.Controls(0)
c = "Jennie"

Of course, if one is setting the value only once then the speed
advantage of this method may be offset by having to create the
object reference; then again it may not, as setting an object
reference is extremely fast. But unless we are setting these
values thousands of times it's very unlikely that we will notice
any time difference at all.

In general, if one is doing intensive coding, data manipulation,
whatever, it may be worthwhile to declare EVERY object used, and
to initialize the object as soon as possible. In complex
calculations, such a strategy may result in very great speed
advantages, eg something that took 10 minutes, may take 5 seconds
after this is applied.

A real-world case of this is when you need to do things to groups of
controls on a form. I have found that it is substantially faster (I
mean, so much faster that you can tell it in the UI) to create
custom collections of subgroups of controls that you want to act
upon than it is to walk the controls collection each time, testing
for conditions.

Say you are creating a query-by-form interface, and you have text
boxes, combo boxes, list boxes, check boxes and option groups. In
the form's OnLoad event, you would populate collections according to
how you wanted to use them. You could, potentially, create a
collection for each type of control, or you could define your
collections according to what you're going to use the collections
for.

The traditional way to do this is:

Dim ctl As Control

For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acCombo, acListBox, acTextBox
ctl = Null
Case acCheckBox, acOptionGroup
ctl = ctl.DefaultValue
Next ctl

Set ctl = Nothing

Now, keep in mind that the controls collection is substantially
larger than the editable controls, so you'll always be walking
through a lot more controls than you actually want to act upon.

If, on the other hand, you create two custom collections, populate
them in the form's OnLoad, you can then use those specifically, and
act upon only the controls you want, thus speeding up the process
significantly, simply by reducing the size of the collections you're
working with. You'd do this in OnLoad:

Dim ctl As Control

Set colNullValue = New Collection ' module-level variable
Set colDefaultValue = New Collection ' module-level variable

For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acCombo, acListBox, acTextBox
colNullValue.Add ctl, ctl.Name
Case acCheckBox, acOptionGroup
colDefaultValue.Add ctl, ctl.Name
Next ctl

Set ctl = Nothing

(I'd put this in subroutine so I could call it to initialize the
collections any time I wanted, especially during coding when there
are code resets)

Then, to reset the controls to the initialized values, you'd do
this:

Dim varCtl As Variant
Dim ctl As Control

For each varCtl in colNullValue
Set ctl = varCtl
ctl = Null
Next varCtl
For each varCtl in colDefaultValue
Set ctl = varCtl
ctl = ctl.DefaultValue
Next varCtl
Set ctl = Nothing

I also use tags in controls to allow me to create ad hoc collections
that aren't based on ControlType.

In a sense, this gets you what Lyle was asking for, a predefined
variable referring to the object, because each collection item
stores that.

This is one case of a real-world preformance improvement, one that
end users could experiences for themeselves.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

On Wed, 20 Jul 2005 14:50:41 -0500, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:30********************************@4ax.com :

On 19 Jul 2005 21:24:06 -0700, "lauren quantrell"
<la*************@hotmail.com> wrote:

David,
Thanks for the thoughtful reply (as always!)

What I have been doing is:

Dim myFrm as String
myFrm = "myFormName"
Forms(myFrm).ctlA = x

instead of what you suggest:

Dim frm As Form
Set frm = Forms!Form1
frm!ctlA = x

Am I screwed doing it the way I have been?
lq

There are bugs in some cases (particularly with ADO) if you assign
a reference to an object, and let it go out of scope without
setting it to Nothing. As a result, some of us have made it a
best practice to always set such references to Nothing or use a
With block for the equivalent effect.

Personally, I don't do that, and I don't have problems.
Specifically, I've never seen any negative side effects from
writing code like your example above, and I consider that to be
totally safe.

MichKa advised once that you should set to Nothing even in this
situation:

Dim ctl As Control

For Each ctl In Me.Controls
[do whatever]
Next ctl

Set ctl = Nothing

That's because he said the last loop could maintain an implicit open
reference to the last control.

That would seem to me to not happen under the rules you are
adducing, but MichKa knows more about this than I do.

The time it would take to chase down an unreleased reference is
going to be much greater than the time it takes me, by habit, to set
all such variables to Nothing at the end of each subroutine where
I'm using them.

Setting to Nothing has no downside, and the potential upside could
be huge, so I do it.

Actually, in experiments, what I've found is that apparently the last item
gets released, but the controls collection does not.

I saw the problem in Access 2000 using the VBIDE library. If I closed the
database I was accessing through VBIDE after executing a For Each loop through
one of its collections, and did so within in the same procedure, I would get a
nasty invalid memory access error. When I tested it, the iteration variable
was already Nothing, and setting it to Nothing again didn't help. What did
help was making a variable to hold the collection before iterating through it,
and explicitly setting that variable to Nothing after the loop.

[David W. Fenton]

Steve Jorgensen <no****@nospam.nospam> wrote in
news:tu********************************@4ax.com:

On Wed, 20 Jul 2005 14:50:41 -0500, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:30********************************@4ax.co m:

On 19 Jul 2005 21:24:06 -0700, "lauren quantrell"
<la*************@hotmail.com> wrote:

David,
Thanks for the thoughtful reply (as always!)

What I have been doing is:

Dim myFrm as String
myFrm = "myFormName"
Forms(myFrm).ctlA = x

instead of what you suggest:

Dim frm As Form
Set frm = Forms!Form1
frm!ctlA = x

Am I screwed doing it the way I have been?
lq

There are bugs in some cases (particularly with ADO) if you
assign a reference to an object, and let it go out of scope
without setting it to Nothing. As a result, some of us have
made it a best practice to always set such references to Nothing
or use a With block for the equivalent effect.

Personally, I don't do that, and I don't have problems.
Specifically, I've never seen any negative side effects from
writing code like your example above, and I consider that to be
totally safe.

MichKa advised once that you should set to Nothing even in this
situation:

Dim ctl As Control

For Each ctl In Me.Controls
[do whatever]
Next ctl

Set ctl = Nothing

That's because he said the last loop could maintain an implicit
open reference to the last control.

That would seem to me to not happen under the rules you are
adducing, but MichKa knows more about this than I do.

The time it would take to chase down an unreleased reference is
going to be much greater than the time it takes me, by habit, to
set all such variables to Nothing at the end of each subroutine
where I'm using them.

Setting to Nothing has no downside, and the potential upside could
be huge, so I do it.

Actually, in experiments, what I've found is that apparently the
last item gets released, but the controls collection does not.

I saw the problem in Access 2000 using the VBIDE library. If I
closed the database I was accessing through VBIDE after executing
a For Each loop through one of its collections, and did so within
in the same procedure, I would get a nasty invalid memory access
error. When I tested it, the iteration variable was already
Nothing, and setting it to Nothing again didn't help. What did
help was making a variable to hold the collection before iterating
through it, and explicitly setting that variable to Nothing after
the loop.

But that would make using WITH dangerous in all cases.

My guess would be that the problem was with the external library, as
I've never seen anything of the kind with "native" Access
collections.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

On Thu, 21 Jul 2005 01:47:12 -0500, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

....

But that would make using WITH dangerous in all cases.

My guess would be that the problem was with the external library, as
I've never seen anything of the kind with "native" Access
collections.

I'd have to do more experiments to verify, but as I recall, I was not seeing
the problem in any implementation of With blocks that I found when using "For
Each <n> in <release-me-or-else>".

[lauren quantrell]

So if I am using Dim ctl as Control and release it with Set ctl =
Nothing, does it matter what order I release it in - in context with
other Dims?

Currently using a structure that looks like this:
On Error GoTo myErr

Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim RS As ADODB.Recordset
Dim ctl As Control
Dim myID as Long
Dim myFrm as String

myFrm = "myFormName"

With Forms(myFrm)
myID = .PKID
etc...
End With

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandTimeout = 0
cmd.CommandText = "dbo.sprocName"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParameter("parameterMyID", adInteger,
adParamInput, , myID)
cmd.Parameters.Append prm
Set RS = cmd.Execute()

If Not RS.BOF And Not RS.EOF Then
For Each ctl In Forms(myFrm).Controls

If (ctl.ControlType = acTextBox Or ctl.ControlType =
acComboBox) Then ctl.value = RS(ctl.Name)
Next ctl
Else
MsgBox "Shucks, no record exists for your ID
Number.",vbExclamation,"Yo dude!"
End If

myExit:
On Error Resume Next
RS.Close
Set RS = Nothing
Set prm = Nothing
Set cmd = Nothing
Set ctl = Nothing
Exit Function
myErr:
MsgBox Err.Number & " " & Err.Description,.",vbExclamation,"Dude
You Are So Screwed!"
Resume myExit

[Bri]

lauren quantrell wrote:

So if I am using Dim ctl as Control and release it with Set ctl =
Nothing, does it matter what order I release it in - in context with
other Dims?

Currently using a structure that looks like this:<snip>

The order is the most important part of this. Your code example looks
like it is in the correct order, but since you are asking the question
it may be luck rather than intent? The key is to release the Child
before the Parent. If you release the parent first, then the Child is
left in limbo. Or you can think of these as a nested set. You have to
close the inner nest before the outer one. So release in the opposite
order that you declare.

--
Bri

[David W. Fenton]

"lauren quantrell" <la*************@hotmail.com> wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:

So if I am using Dim ctl as Control and release it with Set ctl =
Nothing, does it matter what order I release it in - in context
with other Dims?

No. I always to the simple cleanups of Set x - Nothing right after
they are used.

That is, any cleanup that I do for something that was *not* assigned
with a SET statement, I do in context. That means I do it right
after the For/Each loop.

But there's merit in doing it your way, since it's in the exit
routine of your code and will get called if an error happens. I put
recordset cleanup code there, but never the code for cleaning up
For/Each loops.

You should also check if your recordset is Nothing before you try to
close it, because if the error happens before the recordset is open,
your error handler will put you in an endless loop.

Also, I'm pretty sure ADO is safe that you don't have to do this
cleanup at all. At least, ADO's defenders claim as much.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc