Using Pseudo-Variables

Introduction

A variable is an area in memory that is used to hold a value for a program. Since the value can change regularly while the program is running, it is called a variable. In order to use this area in memory, you must ask the computer to reserve it for later use. This request is referred to as declaring a variable.

Since Microsoft Access doesn't inherently present a programming environment, in order to declare your own variable, you can use VBA, which is available with Microsoft Access. VBA requires you to do some programming. Even though it may not be the most 
complicated language in the world, it is still a programming language that you would have to learn. If you don't want to get into that area, in various cases you don't have to. You can cheat with Microsoft Access and use variables without declaring them

Practical Learning: Starting the Exercise
  1. If you want to follow this exercise, start Microsoft Access and create a 
    database named Compound Interest
  2. Create a table in Design View as follows:
     
    Field Name Data Type Additional Properties
    FrequencyID AutoNumber Primary Key
    Frequency    
  3. Save the table as Frequencies and switch it to Datasheet View
  4. Enter the first frequency as Monthly, the second as Quarterly, the third as Semiannually, and the fourth as Annually
     
  5. Close the Frequencies table
  6. Create a new table in Design View as follows:
     
    Field Name Data Type Additional Properties
    CompoundID AutoNumber Primary Key
    Caption: Compound ID
    Principal Currency  
    Interest Number Field Size: Double
    Format: Percent
    Default Value: .0725
    Periods Number Field Size: Integer
    Default Value: 1
    FrequencyID Number Start the Lookup Wizard and select the Frequency column of the Frequencies table
    Caption: Frequency
    Default Value: 1
  7. Save the table as CompoundInterest
  8. Switch it to Datasheet View and close it

Creating the Application

One of the features of a variable is that the user neither sees nor uses it. In fact, this aspect, like all others in programming, are of little to no interest to the user. Based on this, you can cheat with your application by including one or many Windows controls in your form or report. To treat such controls as variables, you can simply hide them. Once they are hidden, you can use them as you see fit.

To hide a control in your form, you can include it in any section. All you have to do is set its Visible property to No.

Practical Learning: Designing the Form
  1. Using the CompoundInterest table, design a form as follows:
     
  2. Set the Name of the frame that contains the radio (option) buttons to fraFrequency
  3. Save it as CompoundInterest
  4. To create text boxes that would be used as variables, in the Toolbox, click the Text Box control and click in the left area of the Form Footer section
  5. Change the caption of the label to Compound Type
  6. Using the Properties window, change the following characteristics of the text box
    Name: txtCompoundType
    Record Source: =Switch([fraFrequency]=1,12,[fraFrequency]=2,4,[fraFrequency]=3,2,[fraFrequency]=4,1)
    Visible: No
  7. Add another text box to the right of the previous one. Change its label's caption to i
  8. In the Properties window, change the following characteristics:
    Name: txti
    Record Source: =CDbl(Nz([Interest]))/Nz([txtCompoundType])
    Visible: No
  9. Add another text box to the right of the previous one. Change its label's caption to n 
  10. Using the Properties window, change the following characteristics
    Name: txtn
    Record Source: =Nz([txtCompoundType])*CInt(Nz([Periods]))
    Visible: No
  11. Expand the Detail section if necessary. Add a Rectangle box in the bottom area of the Detail section
  12. Add a label to the top border of the rectangle to appear like a group box or frame. Change the label's caption to Results
  13. Inside the rectangle, add a text box. Change its label's caption to Interest Earned
  14. In the Properties window, change the following characteristics of the text box:
    Name: txtInterestEarned
    Record Source: =Nz([txtAmountEarned])-Nz([Principal])
    Format: Currency
    Decimal Places: 2
  15. Add another text box inside the rectangle and to the right of the previous one. Change its label's caption to Amount Earned
  16. In the Properties window, change the following characteristics of the text box:
    Name: txtAmountEarned
    Record Source: =Nz([Principal])*((1+CDbl([txti]))^CInt([txtn]))
    Format: Currency
    Decimal Places: 2
  17. Reposition the controls so your form appears designed as follows:
     
  18. Save and switch the form to Form View
  19. Process a few loans to see the result
     
  20. Close the form
 
Home Copyright © 2004 FunctionX, Inc.