MS-Access / Getting Started

---

With...End Statement

Use a With statement to simplify references to complex objects in code. You can establish a base object using a With statement and then use a shorthand notation to refer to objects, collections, properties, or methods on that object until you terminate the With statement. When you plan to reference an object many times within a block of code, using With also improves execution speed.

Syntax

With <object reference>
    [<procedure statements>]
End With

Example

To use shorthand notation on a recordset object to add a new row to a table, enter the following:

Dim rcd As DAO.Recordset, db As DAO.Database
Set db = CurrentDb
Set rcd = db.OpenRecordset("MyTable", _ dbOpenDynaset, dbAppendOnly)
With rcd
    Start a new record
    .Addnew
    ' Set the field values
    ![FieldOne] = "1"
    ![FieldTwo] = "John"
    ![FieldThree] = "Viescas"
    .Update
    .Close
End With

To write the same code without the With, you would have to say:

Dim rcd As DAO.Recordset, db As DAO.Database
Set db = CurrentDb
Set rcd = db.OpenRecordset("MyTable", _
  dbOpenDynaset, dbAppendOnly)
    ' Start a new record
    rcd.Addnew
    ' Set the field values
    rcd![FieldOne] = "1"
    rcd![FieldTwo] = "John"
    rcd![FieldThree] = "Viescas"
    rcd.Update
    rcd.Close