Fitting form controls with data

Controls that can contain data, like TextBoxes, ComboBoxes, CheckBoxes, and such, all have a .Value property that defines the contents of the control. To put data into a control, use the following syntax, where controlName is the name of the control, and value is the data you want to put in the control:

controlName.Value = value

If controlName refers to a control that's bound to an underlying table, the field in the current record of that table receives the same value as the control.

Suppose that your form includes controls named State, SalesTaxRate, OrderSubtotal, SalesTaxAmt, and GrandTotal, as in Figure 6-7. You want to write some code that does the following:

1. If State is CA, put 0.0725 (7.25%) in the SalesTaxRate control.

2. If State is not CA, put 0 (zero) in the SalesTaxRate control.

3. Calculate and display the SalesTaxAmt.

4. Calculate and display the GrandTotal amount.

Figure 6-7:

A sample form with calculated sales tax.

Figure 6-7:

A sample form with calculated sales tax.






You need an If...Then...Else block of code to make the decision in your VBA code. For the calculations, just use the * (multiplication) and + (addition) operators, as shown here:

If [State] = "CA" Then 'If State is CA then... '...Set SalesTaxRate to 7.2 5% for CA [SalesTaxRate].Value = 0.0725 Else

'Otherwise, set SalesTaxRate to zero. [SalesTaxRate].Value = 0 End If

'Calculate and show SalesTaxAmt and GrandTotal SalesTaxAmt.Value = [SalesTaxRate] * [OrderSubtotal] GrandTotal.Value = [OrderSubtotal] + [SalesTaxAmt]

When assigning values to controls, make sure to use the correct data type. For example, if you want to put text in a Text, Memo, or Hyperlink control, enclose the text in quotation marks, as in the following examples (all of which use completely hypothetical control names):

anyTextbox.Value = "Smith"

anyHyperlink.Value = "[email protected]" anyHyperlink.Value = ""

To put a check mark into a check box, set the check box's value to True, as in anyCheckbox .Value = True. To clear a check box, set its value to False,as in anyCheckbox.Value = False.

If you want to put a specific date into a Date/Time field on a form (or in a table), enclose the date in pound signs (#). For example, the following line assumes that DateEntered is the control for a Date/Time field named DateEntered. The code places the date 12/31/07 into that control:

To put today's date into a Date/Time field, use the word Date, alone, to the right of the equal sign, as in DateEntered.Value = Date.

Far be it from us to confuse things, but we should point out that for many controls, the .Value property is assumed if you don't include it in your code. This is because .Value is the default property of the text box and combo box controls. you must understand this point when modifying existing code because some programmers might prefer to omit the .Value property name. For example, when you see something like this line in your code:

[SalesTaxRate] = 0

it means exactly the same thing as

[SalesTaxRate].Value = 0

Both these VBA statements put the value 0 into a control named


Was this article helpful?

0 0

Post a comment