Windows Controls and Expressions

Introduction

A person’s interaction with the computer is mostly done using Windows controls as these objects hold or present various pieces of information that the person can use. Therefore, Windows controls are highly involved in expressions. The expressions we have used so far were fairly straightforward and did not need any complex algorithm to accomplish their purpose. Unfortunately, as a database developer, you will face various scenarios, some of them might range from difficult to impossible. There are two main tools you need to build these sorts of expressions. First, you should know what is available so you can wisely play with Microsoft Access. Second, you will make use of your ability to logically think and process information.

A string is an empty space, a character, or a group of characters. Because the character or group of characters that constitute a string is considered "as is", a string must be included in double-quotes whenever it is involved in any expression. Examples of strings are "g", "Gabriel", "Congo". In reality, these examples of strings are referred to as values. The value of a string is usually stored in the name of a field. For example, the last names of employees, such as "Pierre", "Harvey", "Charles", "Hermine", can be commonly stored in a field named FirstName. The ability to store a category of string in a named field allows such a field to be used in an expression, which then would produce a standard result for all records involved in that field. Based on this, you can use operators on names of fields instead of on values of those fields.

The most classic operation you will perform on strings consists of adding strings such as a first name and a last name in order to produce a new string that could represent a full name. Adding two strings is also referred to as concatenating them.

To concatenate two or more strings, you can use the addition operator “+”. An example would be FirstName + LastName. This would produce a string as FirstNameLastName. Instead of having the first and last names tied, you may need to include an empty space between them. For this reason, you can add three strings such as FirstName + " " + LastName to get a new string.

Although you can use the addition operator on strings, you might start thinking that any of the other algebraic operations can be used on strings, not at all. The addition operator was especially written (in computer programming, we say that it was overloaded) to be applied to strings. None of the other arithmetic operations (subtraction, multiplication, division, and remainder) can be applied to strings and it would not make sense. For this reason, Microsoft Access (and the (Visual) Basic language) provide an alternate and appropriate operator to add strings.

Besides, or instead of, the addition operator, you can also add strings using the & (called ampersand) operator. To add two strings or fields named FirstName and LastName, you can use the concatenation operator as FirstName & LastName. Like the addition operator, this operation appends the second string to the end or right of the first one. To produce a more readable string, you can add an empty string in the middle. The operation would become FirstName & " " & LastName

1. Open the Danilo Pizza1 database and click Tables
2. Double-click the CustomersOrder table to open it in Datasheet View
3. After viewing the table, switch it to Design View
4. Right-click OrderDate and click Insert Rows. In the new empty field, type Cashier and press Tab. Type L and press F6. In the first page of the wizard, make sure the first radio button is selected and click Next. In the second page, click Employees and click Next. In the Available Fields list, double-click LastName. Click Next and click Finish
5. In the lower section of the table, delete the Default Value and click Lookup
6. Click Row Source and click its ellipsis button
7. Change LastName in the second column to LastName & “, “ & FirstName and press Enter
8. To preview, on the Query Design toolbar, click the Run button
9. After viewing the query, switch it back to Design View. In the new field, replace Expr1 with Cashier and, in the Sort field of the same column, select Ascending:
    
   
10. Close the SQL Statement window. When asked whether you want to save, click Yes
11. Save the table and switch it back to Datasheet View. Click the arrow of the Processed By combo boxes and notice that each employee is represented with a last and a first name
12. Close the table
13. Open the College Park Auto Shop2 database and click Forms
14. Double-click the WorkOrders form to open it. Click the arrow of the Processed By combo box. Notice that each employee name is set only using the last name
     
    
15. Switch the form to Design View and double-click the EmployeeID combo box
16. In the Properties window, click the All tab and click Row Source. Then click the ellipsis button of the field to open its SQL Statement
17. In the lower section of the query window, change LastName to display:
    

    IIf(IsNull([MiddleName]),[LastName] & ", " & [FirstName],[LastName] & ", " & [FirstName] & " " & [MiddleName])

18. Preview the query and switch it back to Design View. In the second column, change Expr1 to Employee and set the Sort field to Ascending for the same column
     
    
19. Close the SQL Statement window. When asked whether you want to save it, click Yes
20. Still in the All tab of the Properties window, change Column Width value to 0";1.4";1.25"
21. Change the List Width value to 2.65
22. Switch the form to Form View and click the arrow of the Processed By combo box
     
    
23. Save and close the form

Command buttons, or simply called buttons, are the most widely used objects to initiate actions on an application. Among other things, they are used to close windows, to open others, to validate a user’s choice, to present alternatives, etc. When introducing Windows controls, we that the easiest way to create a button consisted of using the Command Button Wizard. Indeed, when using this technique, there is a great deal of code that Microsoft Access writes to apply the desired functionality. The lines of code that Microsoft Access writes have already been prepared and were installed with it in some libraries called DLL. In some cases, either Microsoft Access is not prepared to write the exact functionality you need and it can write only part of the code. For this reason, you will either simply modify the code that Microsoft Access started or, if you know what to do, you can completely write your own code.

Imagine that, on the Bethesda Car Rental database, while processing an order and after the clerk has selected a car, the customer may ask various questions about the car. When creating subforms, we learned that an efficient subform includes only a reduced list of the fields that would be valuable on its parent form. You can then allow you the user to open the main form that holds more information about the subform but you should make sure that only the current record is made available to the user.

1. Open the Bethesda Car Rental2 database and click Forms
2. Double-click the OrderProcessing form to open it
3. After viewing the form, switch it to Design View
4. On the Toolbox, make sure the Control Wizard button is clicked
   In the Toolbox, click the Command Button and click to the right side of the CarID combo box
5. When the first page of the Command Button Wizard comes up, in the Categories list box, select Form Operations
6. In the Actions list, select Open Form and click Next
7. In the second page of the wizard, click Cars and click Next
8. In the third page of the wizard, click the first radio button: Open The Form And Find Specific Data To Display and click Next
9. In the fourth page, in the OrderProcessing list box, click CarID. In the Cars list box, click CarID. Click the button between both lists
    
   
10. Click Next
11. In the text box, replace the string with Car Details and click Next
12. Change the name of the button to cmdCarDetails and click Finish
13. Resize and position the button for a better layout then close the form
14. When asked whether you want to save it, click Yes

Options buttons are used in a group, which makes it particular to refer to them. If you design your option buttons in a "normal" way, which is classically done by including them in an Option Group, to find out which option button is selected in a group, you can call the Value property of the Option Group control. The first option button has a value of 1, the second has a value of 2, etc.

Imagine you have an Option Group control called fraGender that has three radio buttons and a text box called txtGender. If you want to find out what radio button is selected from the option group and display its value in the text box, in the Control Source of the text box, you can type

=fraGender.Value

In the same way, if you have radio buttons on an Option Group box, you can use the IIf() function to find out what button the user would have clicked. If you have only two option buttons, or if you have more than one radio button but want to consider only two possibilities, the use of the IIf() function would be easy. You can just decide which option button in the group would hold the necessary value.

The database engine recognizes that when you access an option button that is part of a group, you are actually asking the group control (the Option Group box is in fact the "parent" of the radio buttons because it "carries" them) to find out what radio button was selected. In reality, the default property of an Option Group box is the Value. Therefore, in your expressions, you can omit the calling to the Value property. This means that when you write Frame12 for the name of a group box, you are in fact accessing the Value property of the control. Based on this, the minimum validation you can perform on an Option Group is to do something if the desired option button is selected, or to do something else otherwise. Such a validation can be written with the IIf() function as follows

=IIf([fraGender]=1,"You are the man!","You go girl!!!")

If you want to navigate through a group of radio buttons and validate each, you would have to include IIf() functions inside of others. This is referred to as nesting. Imagine that when processing the orders of a pizza application, you want to specify the price of a pizza based on the size selected and there are three sizes. A simple IIf() function would not suffice. If the user selects the small size, you would apply the price for a small pizza. In a simple IIf() function, if the user selects another size, you would have only one price left. In a text box that holds the price according to the selected size, you could write an expression as:

=IIf(fraPizzaSize=1, PriceSmall, PriceMedium)

What if the user selects a large size? The only solution is to nest another IIf() function and to include it as the False part of the Condition. The pseudo code you could use is

If Small Size is selected Then
    Apply Small Price
Otherwise
    If Medium Size is selected Then
        Apply Medium Price
    Otherwise
  	Apply Large Price
    End If
End If

1. Open the Danilo Pizza1 database and click Forms in the Database window
2. Double-click the OrderProcessing form to open it
3. After viewing the form, switch it to Design View
4. On the form, click the text box to the right of Total Price to select it
5. In the Properties window, click the All tab and click the Control Source field
6. Click the ellipsis button to call the Expression Builder dialog box
7. On the left list, double-click Functions and click Built-in Functions
8. In the middle list, scroll down and click Program Flow. In the right list, double-click IIf 
9. In the top text box, click <<expr>> to select it
10. In the left list, click OrderProcessing 
11. In the middle list, double-click fraPizzaSize 
12. Type =1 
13. In the top text box, click <<truepart>> 
14. In the middle list, double-click PriceSmall 
15. In the top text box, click <<falsepart>> 
16. In the left list, click Built-in Functions. In the middle list, click Program Flow. In the right list, look for and double-click IIf 
17. In the top text box, click <<expr>> 
18. In the left list, click OrderProcessing 
19. In the middle list, double-click fraPizzaSize 
20. Type =2 
21. In the top text box, click <<truepart>> 
22. In the middle list, double-click PriceMedium 
23. In the top text box, click <<falsepart>>
24. In the middle list, double-click PriceLarge
     
    
25. Click OK 
26. On the Properties window, set the Format to Currency and the Decimal Places to 2
27. Switch the form to Form View
28. On the form, click different option buttons and notice that the price of the pizza changes depending on the selected radio button
29. Switch the form back to Design View.
30. We will make invisible the text box we had added because the user will not need access to it.
    On the form, click the txtPizzaPrice text box that was just added
31. In the Properties window, click the Format tab and set the Visible property to No
32. Save and close the form

We defined a toggle button as a variant of a radio button or a check box. When toggle buttons come as a group and are treated as a group, they behave like radio buttons. When a toggle button is created as its own button, it behaves like a check box. To create toggle buttons that would behave like radio buttons, you should use the Option Group control. Otherwise, to create a toggle button that behaves like a check box, you can use the Toggle Button control on the Toolbox.

After creating a toggle button, you can implement its functionality. As stated already, a toggle button is first of all a button. Therefore, when it is clicked, it sends a message stating that it has been clicked. You can then take the necessary action. When a toggle button is part of a group created in an Option Group control, the host (the Option Group control) can be used to take care of the action. To do this, each toggle button is represented by the Option Value property. Therefore, when implementing the Option Group, to know what button was clicked in the group, you can use a logical function like IIf (or a conditional statement like If…Else) to check the value of the clicked button. Once you know what button was clicked, you can do what you want.

1. Open the Bethesda Car Rental2 database and click Forms in the Database window
2. Double-click the Customers2 form to open it
3. After viewing the form, switch it to Design View
4. Scroll down to display the Form Footer section.
   In the Toolbox, make sure Control Wizard button is clicked.
   In the Toolbox, click the Option Group button and click somewhere under the Form Footer bar
5. On the first page of the Option Group Wizard, under Label Names, type Contact and press the down arrow key
6. Type Driving Information and press the down arrow key
7. Type Notes/Comments and press Enter
    
   
8. Click Next
9. In the second page of the wizard, accept to have Contact as the Default Choice and click Next
10. In the third page, accept the suggested values and click Next
11. In the fourth page, make sure the Save The Value For Later Use radio button is selected and click Next
12. In the fifth page, click Toggle Buttons and accept the Etched Style
     
    
13. Click Next
14. In the sixth page of the wizard, accept the suggested Caption and click Finish
15. While still in Design View, click the label that was added and press Delete to remove it. Move and resize the controls to redesign them as follows:
     
    
16. To add a button to close the form, on the Toolbox, click the Command Button and click to the right of the Notes/Comments button in the Form Footer section and inside the group control
17. When the first page of the Command Button Wizard comes up, in the Categories list, click Form Operations. In the Actions list, click Close Form and click Next. In the second page, in the text box, delete Form to have only Close and click Next. In the next page, change the name to cmdClose and click Finish
18. Resize the button to occupy the area on the right side of the group control
     
    
19. Double-click the border of the group control. In the Properties window, click the All tab and change the following two properties:
    Name: fraSections
    Border Style: Transparent
20. Save the form

A check box can have only two states: selected or not selected. (In most programming environments, a check can actually have three states, depending on how it was created. The third state would appear as "half" checked, also referred to as gray or indeterminate. For our databases here, we will consider that a check box can only be clicked or not clicked).

When a check box is selected, it has a value of True, Yes, On, or 1. When it is not clicked, it holds a value of False, No, or 0. This value can be used as you see fit. For, example, while only one option button can be selected in a group box, as many as necessary check boxes can be selected in a group. In fact, you do not have to include your check boxes in a group.

You can use the IIf() function to do something if a check box is selected or do something else if the same check box is not selected. You can use the IIf() function as follows:

=IIf([chkMaritalStatus],"Welcome to the club","We can't allow you")

Practical Learning: Using Check Boxes in Expressions

Combo Boxes

Practical Learning: Designing a Time Sheet

Page Breaks

Practical Learning: Configuring Page Breaks

Subforms and Subreports

Lesson Summary

MOUS Topics

Exercises 

Yugo National Bank

Watts A Loan