Nested With Statement hierarchy

... curious if there was an easier way to do this... Does it have to be fully written out,...?

I too have had this problem; an example is transferring data from a TYPE structure to a database RST structure, where I would like to use the dot-notation in the innermost With/EndWith statement block.

I came up with the following:

Function AddTypdataToDB(dbs As Database, typData As typAddressInfo)
    Dim rst As Recordset
    Set rst = dBase.rstOpenRecordset(dbs, "SELECT * FROM Master")
        rst.AddNew
        With typData
            rst.Fields("Amenities") = .strAmenities
            rst.Fields("BusinessName") = .strBusinessName
            rst.Fields("CountOfBathrooms") = .strCountOfBathrooms
            rst.Fields("CountOfBeds") =
            rst.Fields("CountOfGuests") =

Here, I am partway through writing the VBA program code with my least-effort mechanism to date:

1. I typed in the first assignment (“amenities”) in full.
2. I copied that full statement to the line below, ...
3. ... stripped away the .strAmnenities part and then ...
4. ... copy/pasted that skeletal line many times.
5. From the Access MDB table definition, I copy/pasted the field names (Alt+Tab between the two windows)
6. Finally, I typed a period after each successive equals sign, and scrolled to the appropriate field name from my TYPE structure.

This sounds complex when I write it out in English, but it is the fastest error-free way I have found to generate code manually. I have eliminated the outer WITH statement and used explicit RST code, but the bulk of that is copy/pasted.

Not only do I reduce the elapsed time to generate a block of VBA code for my AddRecord procedure, but I save time by not having to correct the myriad errors that creep in when I code in “long-hand”.

The answer is a plain and simple No No.

The With clause facilitates the access to the members and methods of its subject, but it does not provide any facility to reference the subject itself. When that's needed, you have to write the name the object completely or refer to it by other means.

When accessing methods and members of an object that is the subject of an outer With clause, again, you need to name it completely. The inner With, and for the whole of its scope, completely hides the outer With.

Therefore, the way you wrote your code is the correct way to do it.

You could move it out of the inner with and into the outer with. Then you could drop the Activeworkbook qualification.

With Activeworkbook
  'Do Stuff
  With .Sheets(1)
    'More stuff Done
    '...
  End With
  SomeFunctionAnswer = SomeFunction .Sheets(1)
  'Yet more stuff Done
End With