[AccessD] With / End With or not?

[Arthur Fuller]

Gustav
In all my years of codi ng, I can safely say that this was the most
hilarious! You are a genius and/or fool, I'm at present undecided. Buut
either way, you are the cat's meow. I don't know if there is an equivalent
Danish expression, but it means I love your sense of humor.

On Tue, Sep 14, 2021 at 5:04 AM Gustav Brock via AccessD <
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> På
> vegne af Arthur Fuller
> Sendt: 14. september 2021 03:57
> Til: Access Developers discussion and problem solving <
> 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
>


-- 
Arthur