[AccessD] With / End With or not?

[Bill Benson]

You're joking right? It takes FAR less of my OWN microseconds to make use
of With and use the dots only, than to type or copy paste Target all those
times!

On Fri, Sep 17, 2021 at 6:03 AM Gustav Brock via AccessD <
accessd at databaseadvisors.com> wrote:

> Hi Bill
>
> Perhaps, and we can all believe, but who knows?
> My believe is, that we are discussing microseconds, thus not worth any
> efforts for any normal scenario.
>
> /gustav
>
> Fra: Bill Benson <bensonforums at gmail.com>
> Sendt: 17. september 2021 11:47
> Til: Access Developers discussion and problem solving <
> accessd at databaseadvisors.com>
> Cc: Gustav Brock <gustav at cactus.dk>
> Emne: Re: [AccessD] With / End With or not?
>
> Gustav, I believe you could AT LEAST have left With Target in there and
> got a performance boost. Small, but real.
>
> On Tue, Sep 14, 2021 at 5:04 AM Gustav Brock via AccessD <
> accessd at databaseadvisors.com<mailto:accessd at databaseadvisors.com>> wrote:
> Hi Arthur
>
> If any gain at all, it will more likely be microseconds.
>
> The use or not of With-End With is rather a matter of preferred coding
> style. Indeed, if you type in an environment like Visual Studio or Visual
> Studio Code (yes, you can code VBA with this) where IntelliSense predicts
> quite well what you intend to type.
>
> The problem with With-End With is, that you easily loose track of the
> object, indeed when nested.
> For fun, I took an old function of mine with what I considered "smart" use
> of With-End With tending the unreadable and rewrote it in "clean" style.
>
> Copy the code blocks to your editor using a monospaced font.
> Note how you probably will read back and forth to grasp this simple code:
>
> <code old>
> Public Sub CopyRecords()
>
>   Dim rstSource   As DAO.Recordset
>   Dim rstInsert   As DAO.Recordset
>   Dim fld         As DAO.Field
>   Dim strSQL      As String
>   Dim lngLoop     As Long
>   Dim lngCount    As Long
>
>   strSQL = "SELECT * FROM tblStatus WHERE Location = '" & _
>                 "DEFx" & "' Order by Total"
>
>   Set rstInsert = CurrentDb.OpenRecordset(strSQL)
>   Set rstSource = rstInsert.Clone
>   With rstSource
>     lngCount = .RecordCount
>     For lngLoop = 1 To lngCount
>       With rstInsert
>         .AddNew
>           For Each fld In rstSource.Fields
>             With fld
>               If .Attributes And dbAutoIncrField Then
>                 ' Skip Autonumber or GUID field.
>               ElseIf .Name = "Total" Then
>                 ' Insert default job total.
>                 rstInsert.Fields(.Name).Value = 0
>               ElseIf .Name = "PROCESSED_IND" Then
>                 rstInsert.Fields(.Name).Value = vbNullString
>               Else
>                 ' Copy field content.
>                 rstInsert.Fields(.Name).Value = .Value
>               End If
>             End With
>           Next
>         .Update
>       End With
>       .MoveNext
>     Next
>     rstInsert.Close
>     .Close
>   End With
>
>   Set rstInsert = Nothing
>   Set rstSource = Nothing
>
> End Sub
> </code old>
>
> The revised "clean" style, I very much think you can read in one go, as
> any line expresses by itself what is doing:
>
> <code new>
> Public Sub CopyRecords()
>
>     Dim Source      As DAO.Recordset
>     Dim Target      As DAO.Recordset
>     Dim Field       As DAO.Field
>     Dim Sql         As String
>     Dim LoopCount   As Long
>     Dim RecordCount As Long
>     Dim FieldName   As String
>
>     Sql = "Select * From tblStatus " & _
>         "Where Location = 'DEFx' " & _
>         "Order By Total"
>
>     Set Target = CurrentDb.OpenRecordset(Sql)
>     Set Source = Target.Clone
>
>     RecordCount = Source.RecordCount
>     For LoopCount = 1 To RecordCount
>         Target.AddNew
>         For Each Field In Source.Fields
>             FieldName = Field.Name
>             If Field.Attributes And dbAutoIncrField Then
>                 ' Skip Autonumber or GUID field.
>             ElseIf FieldName = "Total" Then
>                 ' Insert default job total.
>                 Target.Fields(FieldName).Value = 0
>             ElseIf FieldName = "PROCESSED_IND" Then
>                 Target.Fields(FieldName).Value = vbNullString
>             Else
>                 ' Copy field content.
>                 Target.Fields(FieldName).Value = Field.Value
>             End If
>         Next
>         Target.Update
>         Source.MoveNext
>     Next
>     Target.Close
>     Source.Close
>
>     Set Target = Nothing
>     Set Source = Nothing
>
> End Sub
> </code new>
>
> /gustav
>
> -----Oprindelig meddelelse-----
> Fra: AccessD <accessd-bounces+gustav=cactus.dk at databaseadvisors.com
> <mailto:cactus.dk at databaseadvisors.com>> På vegne af Arthur Fuller
> Sendt: 14. september 2021 03:57
> Til: Access Developers discussion and problem solving <
> accessd at databaseadvisors.com<mailto:accessd at databaseadvisors.com>>
> Emne: Re: [AccessD] With / End With or not?
>
> Susn,
> Milliseconds count. Perhaps the average user cannot see the difference,
> but I am going to compose a toy to determine the relative speed , to
> determine the savings if any. I should have done this long ago, but hey,
> stuff happens.
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>