Question about 'With.......End With' efficiency

[Guest]

If I am reading a recordset into form controls, is it more efficient to use

With Me...
.xxx = rs("..."
End Wit

O

With r
Me.xxx = .fields("..."
End With

 

[Allen Browne]

No difference in runtime performance for your example.

Holding a deeper reference open (one with more dots in it) is more efficient
that repeating the reference because it only has to be resolved once, e.g.:
With Me.MySubform.Form.RecordsetClone
If .RecordCount > 0 Then
.FindFirst "SomeField = SomeValue"
'etc
End If
End With

 

[Mark Burns]

No difference in runtime performance for your example.

Holding a deeper reference open (one with more dots in it) is more efficient
that repeating the reference because it only has to be resolved once, e.g.:
With Me.MySubform.Form.RecordsetClone
If .RecordCount > 0 Then
.FindFirst "SomeField = SomeValue"
'etc
End If
End With

Uh...isn't that snippet of code a bad example?
It looks like a bit of code likely to lead to "phantom variables" issues and
Access not
closing because of resulting COM reference count problems.

 

[Allen Browne]

That did happen in earlier versions, Mark, because you were not able to
destroy the reference to the implicit Recordset variable. Have not seen the
problem in recent versions though.

The example was chosen because the reference contains 3 dots. Each one
requires Access to resolve it, so they act like little speed-bumps.

Of course, there are reasons other than performance that you may want to use
a With block. Here is an example that I use frequently in forms where we
offer lots of options for filtering, show/hide them as appropriate, and then
have to build up the filter string:

With Me.txtFilterSurname
If .Visible And .Enabled And Not IsNull(.Value) Then
'do something
End If
End With