Controlling The User's Input

Introduction:

Redundancy in database is the default of having the same data provided or entered in
more that one component; having two different forms from where the user can enter
the same customer's name is nasty is should always be avoided. Furthermore, when
you design your database and decide that some boxes will receive an employee's date
of birth, you have to make sure that only a recognized date can be entered.

Different components of your database are meant to communicate and collaborate,
allowing some data in one component to supply necessary data to another; this
assures that the same data cannot be entered from more than one place, once more
eliminating redundancy.

Input Masks:

Microsoft Access comes with input masks allowing you to controlling user's input such as dates, times, Social Security Number, Currency values, etc.

1. Start Microsoft Access and choose to create a blank database called Ashburn Agency.
2. In Design View, create a table with the following fields:
   
3. Switch to Datasheet View and save the table as tblEmployees.
4. In the Employee # field, type dot and in the Salary field, type Suzy and press Enter. Microsoft Access has accepted your entries and is ready for another record, the only problem is that if in your company you have employee's numbers formatted and called dot and whatever and a table or form accepting a name in a field reserved for money, you might end up getting confused about employees and on pay day, somebody will not receive her wages. These are the things we will avoid from data entry.
5. Switch to Design View.
6. Click the Salary field. Set its Data Type to Currency and delete the 0 set as its Default Value. The data you left in the Datasheet View will be deleted when you save the table.
7. In our records, we only want to know whether an employee is married or not. To get that result, the user will choose between a Yes or No. Set the Data Type of the MaritalStatus to Yes/No.
8. One of the Data Types provided by Microsoft Access allows you to specify a field for a date or time values. Click the DateHired field. Change its data type to Date/Time. In the lower part of the Design View, change its Format to Short Date. Save the table. You can built an input the way you want data in that field to be formatted. Or, you can use the Input Mask wizard to assist you in building your mask. Click the Input Mask field. There appears an ellipsis/build button on its right, click that button. When the Input Mask Wizard comes up, choose Short Date. Click Next twice and click Finish.
9. Most companies and institutions give telephone extensions in the form of a two, three or four-number format. Let's imagine that our company here uses an extension like 4266. This is a number made of four characters. So, specify the Extension's Data Type as Number. In the lower part of the design view, change its field size to Integer. To make sure that the user will not enter a value like 3,528, set its Format to General Number. Set the Decimal Places to 0 zero. Unfortunately, the user can still type a number like 218 or 54 for the extension, that would not abide to the rules of our company here. To make sure that the user can only type a four-character number, in the Input Mask, type 0000. As it happens, 0000 tells Microsoft Access that the user will not be allowed to type anything else than four digits, which means you can reset the Extension's Data Type to Text.
10. The Input Mask is very useful when you want to control what your users type. Most institutions would accept just one letter for the middle initial name. If you use our table now and type something in the MI field, it would accept anything. If you want to limit the user to enter only one character, you can save space on your table and eventually on your form by allowing only one letter in that field. Click the MI field and set its field size to 1. Unfortunately, the field can still accept a number, and we know that nobody has a middle name as 3, 8, or 5. To limit the entry to one letter only, type L in the Input Mask. The other extra thing you can do is ask Microsoft Access to convert to upper case any letter entered in the MI field. To do that, just type > in the Format field.
11. The Validation Rule field allows you to specify the standard that will recognize whether the user type a correct entry in an appropriate field. And you can write the write message to indicate the error to the user. Every bank has a set of rules on how its bank accounts are formatted, in the same way different companies use different formats for their employees numbers. Since we have already seen how you can set a field to use only numbers or letters, let's use a different formatting for our employees numbers. Let's say our company has decided that an employee's number will display like ED-4577-G. This number uses two letters separated by a dash, underscore, or a hyphen, followed by four number, then another separator and ending with one letter. To accomplish that, you can specify the properties of the employee number field like the following:
    
    If you use that formula, the data entry personal will have to keep in mind how employee's numbers are formatted or formulated. On the other hand, there is a better way of formatting the employee number and at the same time helping the data entry employee by providing masks. For the employee field, delete the formula you entered in the Validation Rule and, in the Input Mask, type the following: LL\-0000\-L0;0;_ This formula assures that two letters and strictly two letters will be typed first, followed by a dash, followed by four digits, then a dash, and finally one letter and one digit. Save the table.
12. To control telephone number entry (in the US and Canada), click the WorkPhone field. In the lower part of the Design View, click the Build button of the Input Mask. In the first page of the Input Mask Wizard, choose Phone Number and click Next twice. In the third page click the first radio button to allow the user to see parenthesis and the dash sign while he is typing the value. Then click Next and click Finish.
13. Change the Data Type of the FavWebSite to Hyperlink.

Validating Entries:

While you are trying to control valid entries, you can decide what Microsoft Access will accept as an acceptable value. You can set ranges of acceptable values and direct the user to what is correct.

As we are still in the Design View, we will now set the minimum wage in the company. Let's say the salary in the company starts at $6.45 and nobody should earn less.

If you want to specify a date as a text, you enclose it between pound (#) signs. For example, let's say this company was created on 24 January 1984. There is no way anybody could have been hired before that date.

Click the Salary field. In its Validation Rule field, type >=6.45 and press Enter.
In the Validation Text, type The minimum wage in this company is $6.45.

Click DateHired. In the Validation Rule, type >= #1/1/84#.

In the Validation Text, type Come On, no one was hired here before January 1st, 1984.

Setting Items For A Preset List:

You can furthermore control the user's input by presetting some values so that he can only choose what is available, in the form of a combo box (or a list box).

1. Click the Title field. For its Data Type, choose Lookup Wizard...
2. When the first page of the Lookup Wizard comes up, choose the second radio button telling the wizard that you will type values for the field, then click Next.
3. Under Col1, type Ms., then press Tab, type Mr., the third field will be Mrs., then Dr., Prof., Sir, Rev., Rabbi, Fong, Other. Click Next. Accept the column label as Title and click Finish. Click the Lookup tab in the lower part of the Design View and see the work that was done behind the scenes. To make sure that the user can only choose a preset value in the list, set the Limit To List field to Yes; on the other hand, if you want to allow the user to type new entries, you would set Limit To List to No (keep in mind that even if you restrict the user to choose only from the list, you yourself can eventually modify that list).
4. Save the table, then close the Ashburn Agency database.

Getting A Field's Data From Another Source:

Using the wizard or working manually, you can make sure that there are fields that will receive their data from another table, once more this reduces redundancy of data.

1. Open the Books3 database. Imagine that you work for a public library where books are categorized in a certain order. It is better to make sure that the new books registered follow the categories that have been preset, this reduces confusion. We will then make sure that the book categories originate from the book table.
2. Open the tblBooks table in Design View.
3. Click the Category field to highlight it. Press Tab to move to the Data Type area. Press L and press F6.
4. When the first page of the Lookup Wizard comes up, accept the first radio button and click Next.
5. You then have to choose which table or query holds the originating data. Right now we have only one table, so it is chosen by default; otherwise, you would choose tblBookCategories and click Next.
6. You can include many fields for a particular column, except that only one will be seen when its combo box is not dropped. For this example, only the BookCategory is needed (Microsoft Access will include the other, even if you don't select it). So, double-click BookCategory and click Next.
7. Agree to let Microsoft Access Hide The Primary Key and click Next. Accept the Category label for the column and click Finish.
8. Accept to save the table. When the wizard finishes, it should take you to the lower part of the view because you clicked F6 before starting the wizard. Delete the Default Value of 0 (zero). If you want to see what Microsoft Access did behind the scenes to get the lookup values, click the Lookup property sheet.
9. Save the table and switch to the Datasheet View to verify that the Category field has a combo box now and doesn't allow any value that is not coming from the tblBookCategories table.
10. Specify the Category of MFC Programming as Computer - Programming. The Category of America A Narrative History is History.
11. When you have finished, close the tblBooks table and close the Books3 database.

Extra Stuffs:

Here are some formulas you can use to control user's input.

Exercises:

Creating tables for the Georgetown Cleaning Services database:

• Open the Georgetown Cleaning Services2 database.
• In Design View, create a table named tblCustomers. Give it the following field names:
  
• Using the Table Wizard, create another table named tblEmployees. From the Employees Sample Table, select the following fields: EmployeeID, EmployeeNumber, FirstName, MiddleName (rename it MI), LastName, Address, City, State, PostalCode, Country, HomePhone, EmailAddress, WorkPhone, DateHired, Salary, EmrgcyContactName, EmrgcyContactPhone, Notes.
• Open the tblCleaningOrders table in Design View. Insert a new filed after OrderID and name it Clerk. Using the Lookup Wizard, make the Clerk field get its data from the tblEmployees table; at this time, we will need only the LastName.
• Add another field after the Clerk field and name it Customer. Using the Lookup Wizard specify Customer's data as provided by the CustomerName field from the tblCustomers table.

Improving the Silver National Bank database

• Open the Silver National Bank2 database.
• On the Database Windows, click the Tables button.
• Open the tblAccountTypes table and type Checking then Saving, and finally CD for the account types. Close the table.
• Open the tblAccounts table in Design View. Change the Input Mask of the AccountNumber field to read: 000\-000\-00.
• Using the Lookup Wizard, specify the AccountType as getting its data from the tblAccountTypes table (select only the AccountType field).
• Using the Lookup Wizard, specify that the titles will come from the list that you are going to provide. The titles are: Ms. Mr. Mrs. Dr. In the lower part of the Design View, in the Lookup tab, specify the Limit To List to Yes. Set the default value of the Country to USA.
• Switch the tblCustomers table to datasheet view. Fill it out as follows: DateJoined: 02/24/95; AccountType: Checking; Account #: 312-874-39; Title: Mrs.; First Name: Gertrude; MI: C; Last Name: Waters. Address: 4288 S. Patton St; City: Colesville; State: MD, ZIP Code: 20912.
• The next account is, DateJoined: Ctrl+'; Account #: 576-873-47; Account Type: Saving; Title: Dr.; First Name: Steve; Last Name: Yacobby; Address: 3901 Georgia Blvd; City: College Park; State: (press Ctrl + ', that will paste MD from the former State entry), ZIP Code: 20707.

Getting Help In Microsoft Access:

1. Click the Office Assistant and type Input Mask.
2. Click Create an input mask to control how data is entered in a field or control. Then proceed.
3. Click the Office Assistant again, and type Validation Rule.
4. Click Validation Rule, Validation Text Properties.
5. Click the Office Assistant again, the last entry should still be there.
6. Click Validate or restrict data entry in tables.
7. Proceed to the choice that fits your current situation.