Filling form controls with data

Controls that can contain data, like TextBoxes, ComboBoxes, CheckBoxes, and such, all have a .Value property that define 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:

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.725 (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.

OrderSubtotal

SalesTaxAmt

GrandTotal

SalesTaxRate

State

OrderSubtotal

SalesTaxAmt

GrandTotal

SalesTaxRate

State

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.25% 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, you need to make sure you 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 = "www.coolnerds.com"

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 field Date/Time field named DateEntered. The code places the date 12/31/05 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 for me to confuse things, but I should point out that for many controls, the .Value property is assumed if you don't include it in your code. It's important to 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 in code

[SalesTaxRate] = 0

that means exactly the same thing as

[SalesTaxRate].Value = 0

Both of the preceding VBA statements put the value zero into a control named SalesTaxRate.

0 0

Post a comment