Navigation:  Articles > Oct-1996 >

Tip: Use With Construct to Streamline Your Code

Previous pageReturn to chapter overviewNext page

ACCESS 95 introduced the VBA With…End With construct, which lets you simplify repeated references to an object. For example, in the following Access 2 code, you have to repeat the object name on every row:

Set dbs = DBEngine.Workspaces(0).Databases(0) 

Set rstInfo = db.OpenRecordset("tblInfo", DB_OPEN_TABLE)

rstInfo.MoveFirst 

rstInfo.Edit   

rstInfo![Attached] = True 

rstInfo.Update 

rstInfo.Close

In Access 95, you can streamline the code like this:

Set dbs = CurrentDb 

Set rstInfo = dbs![tblInfo].OpenRecordset (dbOpenTable, dbDenyRead)

With rstInfo    

  .MoveFirst   

   .Edit      

  ![Attached] = True    

  .Update    

 .Close 

End With

This code is more compact and, more significantly, it’s more easily reusable since you need to make only one change to use a different recordset variable.

 

This tip still applies in Access 2007