Access VBA Macro Programming

This will return the sum of all MyField values in the table MyTable. MyField must be a

numeric data type. You can use an optional criterion as follows:

MsgBox DSum("MyField", "MyTable", "MyName='Richard'")

Notice that single quotes are used within the string parameter to denote that the MyName

criterion is a string.

DVar/DVarP

The methodsDVarandDVarPlet you estimate the variance of field values for a given field

within a table. An example is:

MsgBox DVar("MyField", "MyTable")

This will return the variance of all MyField values in the table MyTable. MyField must be

a numeric data type. You can use an optional criterion as follows:

MsgBox DVar("MyField", "MyTable", "MyName='Richard'")

Notice that single quotes are used within the string parameter to denote that the MyName

criterion is a string.

TheDVarmethod is used to estimate the variance across a population sample, and the

DVarPmethod is used to estimate the variance across a population.

Forms

TheFormscollection represents all the forms in the application that are open and loaded.

It allows considerable scope for manipulation using properties and methods. You can also

change the properties, and by using the controls collection within a form object you have

access to the properties and methods of an individual control, which provides a very powerful

means of on-the-fly manipulation.

You can iterate through theFormscollection using the following code:

For Each frm In Forms

MsgBox frm.Name

Next frm

This will display the name of each form loaded, but notice that it does not include forms

that are not loaded.

You can address controls on a form by using the Controls collection:

MsgBox Forms("MyForm").Controls("label 0 ").Caption

This will display the caption of the control called label0. You can also use this to change

the caption of the label control.

198 Microsoft Access 2010 VBA Macro Programming