Microsoft Office 2000/Visual Basic Programmer's Guide

Thinking About Data Entry and User Input

If your solution requires that users enter data, another key part of your design process is determining how your solution should get data from users and validate it. The extent to which your solution needs to control user input is another factor in choosing which application to use as the basis for your solution.

If you decide to store the data in a relational database such as an Access or SQL Server database, it's a good idea to separate the solution into two parts: a back-end database and a front-end data-entry component. The back-end database contains the tables, where all of the data is stored, while the front end displays the data and manages data entry. By designing the solution this way, you can store the data in a central location on a network server and distribute a copy of the front-end file or files to each user. You can build the front-end data-entry component in any Office 2000 application or in Microsoft Visual Basic.

Using Access to Create a Data-Entry Component

An Access database makes a good front-end data-entry component for data stored in a relational database. If your back-end database is an Access .mdb file, you can create another .mdb file to function as the front end, and link it to the tables in the back-end database. In the front-end component, create the queries, forms, reports, data access pages, macros, and modules that you need to build the solution and manipulate and display the data.

If your back-end database is a SQL Server database, you can create an Access project file (.adp) to function as the front-end data-entry component. In an Access project, you can create forms, reports, data access pages, macros, and modules, just as you can in an .mdb file; you can also create SQL-specific objects like views, stored procedures, and database diagrams. By using an Access project, you can display the data in a SQL Server database, and you can also create a database or modify the database structure.

Creating either an Access database or an Access project file is the best approach if your solution requires that users enter lots of data, and if you need to control the way that data is stored. The design of your database itself enforces certain rules on the way that users can enter data. For example, a user cannot violate a table's primary key by adding a duplicate record. In addition, you can establish custom validation rules in Access that prevent users from entering invalid data.

For more information about creating an Access-based solution or a client/server solution, see Chapter 5, "Working with Office Applications," Chapter 14, "Working with the Data Access Components of an Office Solution," and Chapter 16, "Multiuser Database Solutions."

Validating User Input from Excel, Word, and PowerPoint-Based Solutions

Excel, Microsoft Word, and Microsoft PowerPoint are good for displaying data that's stored in a relational database, but they don't provide much flexibility for entering data into the database. However, if your data is stored in Excel, Word, or PowerPoint, and your solution requires that you control and validate the data that users enter or other user input, you may still need to perform data validation. Here are some ways to control and validate user input from these applications:

• Use the InputBox function to prompt users to input small amounts of information. Verify within code that users entered data and that it's the type of data you're expecting. For example, you can use the IsNumeric and IsDate functions to determine whether users entered a number or a date. For more information about performing data validation in VBA code, see Chapter 7, "Getting the Most Out of Visual Basic for Applications."
  
  
• Create a custom dialog box that prompts users to enter data or select options. Using this approach provides more flexibility than using the InputBox function does. By using a combo box, for example, you can force users to choose an existing value from a list, thus preventing errors that might occur if users typed the value in themselves.
  
  
• Disable controls on the form until users have entered the data that you're expecting. For example, within the Change event procedure for a text box control, you can check whether the value of the text box is a zero-length string (""). If it is, you can disable the command buttons on the form so that users cannot proceed without entering text or canceling, as shown in the following procedure:

  ' The Len function returns zero if there is no data in the text box, 
  ' and a nonzero value if there is, so using the CBool function 
  ' converts the numeric value to True (nonzero) or False (zero).
  Private Sub txtGetData_Change()
     cmdOK.Enabled = CBool(Len(txtGetData.Value))
  End If
  

• Create a wizard that walks users through an operation, prompting them at each step to supply the necessary data. Again, you can prevent users from proceeding by disabling the command buttons that the wizard uses for navigation. For more information about creating wizards, see Chapter 11, "Add-ins, Templates, Wizards, and Libraries."

You can also use these strategies for controlling and validating data input through script in a Web page.