[AccessD] With / End With or not?

[Gustav Brock]

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.