Interface Design Philosophy

·         The design philosophy for a proper application interface is very basic – keep it as simple as possible and as intuitive as possible.  By doing this, you will save yourself (the programmer) and your users a lot of problems.  This may be an obvious statement, but you would be surprised at how many programmers do not follow it.

·         A first consideration should be to determine what processes and functions you want your application to perform.  What are the inputs and outputs?  Develop a framework or flow chart of all your application's processes.  Possible functions of a database interface include: data entry, searching, deleting information, adding information, editing information, sorting data, and printing capabilities.

·         Decide if multiple forms are required.  Decide what controls from the Visual Basic toolbox you need.  Do the built-in Visual Basic tools and functions meet your needs?  Do you need to develop some tools or functions of your own?  Do you need to acquire some third-party controls?

·         Minimize the possibility of user errors.  This is a very important step.  The fewer errors your user can make, the less error checking you have to do.   If a particular input calls for numeric data, make sure your user can’t type in his name.  Choose ‘point and click’ type tools whenever they can be used to replace tools requiring the user to type in something.  For example, let the user point at a month of the year, rather than have the user type in the month.  If you can avoid letting your user type anything, do it!  Every “typed input” requires some kind of validation that means extra work on your part.

·         At all steps in the application, make it intuitive to the user what he or she is to do.  Don’t make or let the user guess.  You, as the programmer, control the flow of information from the user to the program and vice versa.  Maintain that control at all times.  Try to anticipate all possible ways a user can mess up in using your application.  It's fairly easy to write an application that works properly when the user does everything correctly.  It's difficult to write an application that can handle all the possible wrong things a user can do and still not bomb out.  And, although it is difficult, it is straightforward and just a matter of following your common sense.

·         Make your interface appealing to the user.  Use tolerable colors and don’t get carried away with too many font types.  Make sure there are no misspellings (a personal pet peeve).  Make the interface consistent with other Windows applications.  Familiarity is good in program design.  It is quite proper to ‘borrow’ ideas from other applications.

·         Although not part of the interface the user sees, you should make your code readable and traceable - future code modifiers will thank you.  Choose meaningful variable and control names.  Use comments to explain what you are doing.  Consider developing reusable code - modules with utility outside your current development.  This will save you time in future developments.

·         Debug your application completely before distributing it.  There's nothing worse than having a user call you to point out flaws in your application.  A good way to find all the bugs is to let several people try the code - a mini beta-testing program.  Let’s illustrate some of these philosophies with an example.

Example 6-1

Mailing List Revisited

Open and run the mailing list example built in Chapter 1 (Example 1).  It illustrates many of the interface design philosophies just discussed.  Notice the program flow - how it directs the user about what to do and minimizes the possibility of errors.  In particular, note:

Þ    You cannot type Address Information unless the timer has started (controlled via the Enabled property of fraMail).

Þ    When the Address Information frame is active, the cursor appears in the first text box, so the user starts typing the Name field first (controlled with the txtInput text box SetFocus method).

Þ    After the user types information in each text box, hitting <Enter> or <Tab> automatically moves them to the next text box (controlled with the SetFocus method and the TabIndex property).

Þ    After the user types in the last text box (Zip), the focus moves to the Accept command button, so a simple <Enter> accepts the mailing label (using SetFocus on the cmdAccept button).

Notice how the program flow leads the user through the input process.  Regarding the timer portion of the application, notice the Pause button is faded (Enabled is False) initially and is only active (Enabled is True) when the timer is running.  The other timer control buttons toggle accordingly.

There is some validation of inputs in this application also.  If there are not five values input, a message box appears informing the user of his error.  And, only numbers can be typed when txtInput (Index =4) is active (done in the KeyPress event).  This is the box for the Zip that can only be a number.  It would probably be more proper to also make sure the entered zip matches either the five or nine digit zip code format.  Another validation possible would be to provide a list box control with the 50 states (apologies to our foreign readers for using a provincial example) to choose from instead of asking the user to type in a state name.

Regarding the code in the example, notice the use of comments to explain what is happening in each procedure.  This helps others read and understand your code.  It also helps you know what you were doing when you look back on the code a year later.  Also notice that selection of proper variable and control names aids in understanding what is going on in the code portion of the application.  Now, let’s look at interface design in more detail.

Visual Basic Standard Controls

·         The first step in building a Visual Basic interface is to ‘draw’ the application on a form.  We place the required controls on the form, set properties, and write BASIC code for the needed event and general procedures.  As the interface designer, you need to decide which controls best meet your needs regarding efficiency, applicability, and minimization of error possibilities.

·         In this section, we briefly look at the standard Visual Basic controls (available with both DAO and ADO technologies).  We examine how they might be used in a database ‘front-end’ and present some of the important properties, events, and methods associated with these controls.  This information is provided as a quick review of what is available in the Visual Basic toolbox - a “one-stop” reference to controls and how they are used with databases.  A later look at custom controls will complete the reference.

Form Control

·         The Form is where the user interface is drawn.  It is central to the development of Visual Basic applications, whether for databases or other uses.

·         Form Properties:

Appearance             Selects 3-D or flat appearance.

BackColor                Sets the form background color.

BorderStyle             Sets the form border to be fixed or sizeable.

Caption                     Sets the form window title.

Enabled                    If True, allows the form to respond to mouse and keyboard events; if False, disables form and all controls.

Font                           Sets font type, style, size.

ForeColor                 Sets color of text or graphics.

Visible                       If False, hides the form.

·         Form Events:

Activate                     Form_Activate event is triggered when form becomes the active window.

Click                           Form_Click event is triggered when user clicks on form.

DblClick                    Form_DblClick event is triggered when user double-clicks on form.

Load                          Form_Load event occurs when form is loaded.  This is a good place to initialize variables and set            any run-time properties.

Command Button Control

·         The command button is probably the most widely used control.  It is used to begin, interrupt, or end a particular process.  With databases, it is used to navigate among records, add records, and delete records.

·         Command Button Properties:

Appearance             Selects 3-D or flat appearance.

BackColor                Background color of button (applies only if Style is Graphical).

Cancel                       Allows selection of button with Esc key (only one button on a form can have this property True).

Caption                     String to be displayed on button.

Default                       Allows selection of button with Enter key (only one button on a form can have this property True).

Font                           Sets font type, style, size.

Picture                       Picture appearing on button (applies only if Style is Graphical).

Style                           Button can be Standard or Graphical.

·         Command Button Event:

Click                           Event triggered when button is selected either by clicking on it or by pressing the access key.

·         Command Button Method:

SetFocus                  Places the focus on the command button.

Label Control

·         A label is a control you use to display text.  The text in a label can be changed at run-time in response to events.  It is widely used in database applications for information display.

·         Label Properties:

Alignment                 Aligns caption within border.

Appearance             Selects 3-D or flat appearance.

BackColor                Background color of label.

BorderStyle             Determines type of border.

Caption                     String to be displayed in box (property bound to database).

DataField                  Field in database table, specified by DataSource (or DataMember), bound to label (DAO or ADO).

DataMember            Specifies the Command object establishing the database table (ADO data environment only).

DataSource              Specifies the data control (DAO or ADO) or data environment (ADO) the label is bound to.

Font                           Sets font type, style, size.

ForeColor                 Color of text in label.

·         Label Events:

Click                           Event triggered when user clicks on a label.

DblClick                    Event triggered when user double-clicks on a label.

Text Box Control

·         A text box is used to display information entered at design time, by a user at run-time, or assigned within code.  The displayed text may be edited.  This is the tool used in database applications for editing fields.

·         Text Box Properties:

Appearance             Selects 3-D or flat appearance.

BackColor                Background color of text box.

BorderStyle             Determines type of border.

DataField                  Field in database table, specified by DataSource (or DataMember), bound to text box (DAO or ADO).

DataMember            Specifies the Command object establishing the database table (ADO data environment only).

DataSource              Specifies the data control (DAO or ADO) or data environment (ADO) the text box is bound to.

Font                           Sets font type, style, size.

ForeColor                 Color of text in text box.

Locked                      When True, the text box contents cannot be edited.

MultiLine                   Specifies whether text box displays single line or multiple lines.

ScrollBars                Determines what scroll bars (if any) appear.

Text                            Displayed text (property bound to database).

·         Text Box Events:

Change                     Triggered every time the Text property changes.

LostFocus                Triggered when the user leaves the text box.  This is a good place to examine the contents of a text box after editing.

KeyPress                  Triggered whenever a key is pressed.  Used for key trapping, as seen in Example 6-1.

·         Text Box Methods:

SetFocus                  Places the cursor in a specified text box.

Check Box Control

·         Check boxes provide a way to make choices from a list of potential candidates.  Some, all, or none of the choices in a group may be selected.  With databases, check boxes are used for many kinds of choices.

·         Check Box Properties:

Caption                     Identifying text next to box.

DataField                  Field in database table, specified by DataSource (or DataMember), bound to check box (DAO or ADO).

DataMember            Specifies the Command object establishing the database table (ADO data environment only).

DataSource              Specifies the data control (DAO or ADO) or data environment (ADO) the check box is bound to.

Font                           Sets font type, style, size for Caption.

Value                         Indicates if unchecked (0, vbUnchecked), checked (1, vbChecked), or grayed out (2, vbGrayed) (property bound to database).

·         Check Box Event:

Click                           Triggered when a box is clicked.  Value property is automatically changed by Visual Basic.

Option Button Control

·         Option buttons provide the capability to make a mutually exclusive choice among a group of potential candidate choices.  Hence, option buttons work as a group, only one of which can have a True (or selected) value.  Option buttons on a form work as an independent group as do groups of options buttons within frames.  Option buttons are not data bound controls, yet they can still be used for a variety of options in database interfaces.

·         Option Button Properties:

Caption                     Identifying text next to button.

Font                           Sets font type, style, size.

Value                         Indicates if selected (True) or not (False).  Only one option button in a group can be True.  One button in each group of option buttons should always be initialized to True at design time.

·         Option Button Event:

Click                           Triggered when a button is clicked.  Value property is automatically changed by Visual Basic.

Frame Control

·         Frames provide a way of grouping related controls on a form.  Option buttons within a frame act independently of other option buttons in an application.

·         Frame Properties:

Caption                     Title information at top of frame.

Font                           Sets font type, style, size.

Picture Box Control

·         The picture box allows you to place graphics information on a form.  In a database, picture boxes are used to store graphic data.

·         Picture Box Properties:

AutoSize                   If True, box adjusts its size to fit the displayed graphic.

DataField                  Field in database table, specified by DataSource (or DataMember), bound to picture box (DAO or ADO).

DataMember            Specifies the Command object establishing the database table (ADO data environment only).

DataSource              Specifies the data control (DAO or ADO) or data environment (ADO) the picture box is bound to.

Picture                       Establishes the graphics file to display in the picture box (property bound to database). 

Image Control

·         An image control is very similar to a picture box in that it allows you to place graphics information on a form.  The advantage to an image control is its ability to scale displayed graphics.

·         Image Box Properties:

DataField                  Field in database table, specified by DataSource (or DataMember), bound to image control (DAO or ADO).

DataMember            Specifies the Command object establishing the database table (ADO data environment only).

DataSource              Specifies the data control (DAO or ADO) or data environment (ADO) the image control is bound to.

Picture                       Establishes the graphics file to display in the image box (property bound to database).

Stretch                      If False, the image box resizes itself to fit the graphic.  If True, the graphic resizes to fit the control area.

Example 6-2

Authors Table Input Form

In Chapter 7, we will build a complete database management system for the books database.  Each table in the database will require some kind of input form.  In this chapter, we build such a form for the Authors table.  Even though it is a very simple table (only three fields:  Au_ID, Author, Year Born), it provides an excellent basis to illustrate many of the steps of proper interface design.  We need an input form that allows a user to edit an existing record, delete an existing record or add a new record.  The form should also allow navigation from one record to another.

The books database management example (including the Authors input form) will be built using the DAO data control.  If you prefer to use either the ADO data control or ADO data environment, we provide needed modification steps.  These steps will be in shaded boxes.  In the code accompanying this course, we use a special naming convention for all files (projects, forms) to distinguish among examples built using each technology.  The convention is:

FileName (no suffix, DAO data control)

FileNameAD (AD suffix, ADO data control)

FileNameDE (DE suffix, ADO data environment)

We suggest you use this same naming convention.

1.    Start a new application.  We need three label controls and three text boxes to display the fields.  We need two command buttons to move from one record to the next.  We need five command buttons to control editing features and one command button to allow us to stop editing.  Lastly, a DAO data control is needed.  Place these controls on a form.  The layout should resemble:

2.    Set these properties for the form and controls:

Form1:

Name                                     frmAuthors

BorderStyle                           1-Fixed Single

Caption                                  Authors

Data1:

Name                                     datAuthors

DatabaseName                    BIBLIO.MDB (point to your copy)

RecordSource                      SELECT * FROM Authors ORDER BY Author

Visible                                    False

Label1:

Caption                                  Author ID

Text1:

Name                                     txtAuthorID

DataSource                           datAuthors

DataField                               Au_ID

Locked                                   True

Label2:

Caption                                  Author Name

Text2:

Name                                     txtAuthorName

DataSource                           datAuthors

DataField                               Author

Locked                                   True

Label3:

Caption                                  Year Born

Text3:

Name                                     txtYearBorn

DataSource                           datAuthors

DataField                               Year Born

Locked                                   True

Command1:

Name                                     cmdPrevious

Caption                                  <= Previous

Command2:

Name                                     cmdNext

Caption                                  Next =>

Command3:

Name                                     cmdEdit

Caption                                  &Edit

Command4:

Name                                     cmdSave

Caption                                  &Save

Command5:

Name                                     cmdCancel

Caption                                  &Cancel

Command6:

Name                                     cmdAddNew

Caption                                  &Add New

Command7:

Name                                     cmdDelete

Caption                                  &Delete

Command8:

Name                                     cmdDone

Caption                                  Do&ne

Note, we lock (Locked = True) all the text boxes.   We will unlock them when we (as the programmer) decide the user can change a value (remember, we are in control).  At this point, the form should appear as:

3.    We will add features to this input application as we progress through the chapter.  At this point, we add code to allow us to navigate through the Authors table records.  There are two event procedures to code.  First, the cmdPrevious_Click event:

Private Sub cmdPrevious_Click()

datAuthors.Recordset.MovePrevious

If datAuthors.Recordset.BOF Then

  datAuthors.Recordset.MoveFirst

End If

End Sub

And, the cmdNext_Click event:

Private Sub cmdNext_Click()

datAuthors.Recordset.MoveNext

If datAuthors.Recordset.EOF Then

  datAuthors.Recordset.MoveLast

End If

End Sub

4.    Save the application.  Run it.  Navigate among the records.  Note you cannot edit anything.  The text boxes are locked.  As we progress through this chapter (and the next), we will continue to add features to this example until it is complete.

Message Box

·         Many times, in a database application, you will want to impart some information to your user.  That information may be a courtesy message (“New record written”) or a question requiring feedback (“Do you really want to delete this record?”).  Visual Basic (and Windows) provides an excellent medium for providing such information – the message box.

·         A message box displays a message, an optional icon, and a selected set of command buttons.  The user responds to the message box by clicking one of the button(s).  If you’ve done any work in the Windows environment, you have seen message boxes.  For example, here’s one that appears in Visual Basic when the floppy disk drive is not ready:

The user responds by fixing the problem and clicking Retry (the default response) or by clicking Cancel.  Visual Basic then takes the necessary actions depending on user response.  We can add this same capability to our Visual Basic database applications.  The great thing about the message box is that it is familiar to the user (familiarity is good) and it is easy to use.

·         To use a message box in BASIC code requires just one line of code.  There are two forms for the message box.  The statement form returns no value (it simply displays the box).  The code syntax is:

MsgBox Message, Type, Title

where

Message             Text of message to be displayed (string)

Type                    Type of message box (integer, discussed in a bit)

Title                      Text in title bar of message box (string)

You have no control over where the message box appears on the screen (it is usually centered).

·    The function form of the message box returns an integer value (corresponding to the button clicked by the user).  Example of use (Response is returned value):

Dim Response as Integer

Response = MsgBox(Message, Type, Title)

·    The Type argument is formed by summing four values corresponding to the button(s) to display, any icon to show, which button is the default response, and the modality of the message box. 

·    The first component of the Type value specifies the buttons to display:

Value             Meaning                                      Symbolic Constant

0                 OK button only                           vbOKOnly

1                 OK/Cancel buttons                    vbOKCancel

2                 Abort/Retry/Ignore buttons       vbAbortRetryIgnore

3                 Yes/No/Cancel buttons            vbYesNoCancel

4                 Yes/No buttons                          vbYesNo

5                 Retry/Cancel buttons                vbRetryCancel

Pick the set of buttons that meets your need.

·         The second component of Type specifies the icon to display in the message box:

Value             Meaning                                      Symbolic Constant

0                 No icon                                        (None)

16               Critical icon                                 vbCritical

32               Question mark                            vbQuestion

48               Exclamation point                      vbExclamation

64               Information icon                         vbInformation

Pick an icon that corresponds to the displayed message.

·    The third component of Type specifies which button is default (i.e. pressing Enter is the same as clicking the default button):

Value             Meaning                                      Symbolic Constant

0                 First button default                    vbDefaultButton1

256             Second button default              vbDefaultButton2

512             Third button default                   vbDefaultButton3

Always try to make the default response the “least damaging,” if the user just blindly accepts it.

·    The fourth and final component of Type specifies the modality:

Value             Meaning                                      Symbolic Constant

0                 Application modal                      vbApplicationModal

4096           System modal                            vbSystemModal

If the box is Application Modal, the user must respond to the box before continuing work in the current application.  If the box is System Modal, all applications are suspended until the user responds to the message box.

·    Note for each option in Type, there are numeric values listed and symbolic constants.  It is strongly suggested that the symbolic constants be used instead of the numeric values.  You should agree that vbOKOnly means more than the number 0 when selecting the button type.

·    The value returned by the function form of the message box is related to the button clicked:

Value             Meaning                                      Symbolic Constant

1                 OK button selected                    vbOK

2                 Cancel button selected             vbCancel

3                 Abort button selected                vbAbort

4                 Retry button selected                vbRetry

5                 Ignore button selected              vbIgnore

6                 Yes button selected                   vbYes

7                 No button selected                    vbNo

·         Message boxes should be used whenever your application needs to inform the user of action or requires user feedback to continue.  It is probably better to have too many message boxes, than too few.  You always want to make sure your application is performing as it should and the more information you have, the better.

Example 6-3

Authors Table Input Form (Message Box)

There are two places where we could use message boxes in the Authors Table example.  A statement form after saving an update to let the user know the save occurred and a function form related to deleting records.

1.    Load Example 6-2 completed earlier.  We will modify this example to include message boxes.

2.    Attach this code to the cmdSave_Click event:

Private Sub cmdSave_Click()

MsgBox "Record saved.", vbOKOnly + vbInformation, "Save"

End Sub

Obviously, there will be more code in this event as we continue with this example.  This code just implements the message box.

3.    Attach this code to the cmdDelete_Click event:

Private Sub cmdDelete_Click()

Dim Response As Integer

Response = MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion + vbDefaultButton2, "Delete")

If Response = vbNo Then

  Exit Sub

End If

End Sub

Note we exit the procedure if the user selects No.  And, notice the No button is default – this makes the user think a bit before hitting Enter.  Like above, there will be more code in this procedure as we proceed.

4.    Save the application and run it.  Click the Save and Delete buttons to see how the message boxes appear.

Application State

·         When presenting a Visual Basic database interface to a user, it should be obvious, to the user, what needs to be done.  Options should be intuitive and the possibility of mistakes minimized, if not completely eliminated.  To maintain this obvious quality, you should always be aware of what state your application is in.

·         Application state implies knowing just what is currently being done within the interface.  Are you adding a record, editing a record, deleting a record, or perhaps leaving the application?  Once you know the state the application is in, you adjust the interface so that options needed for that particular state are available to the user.  You also need to know when and how to transition from one state to another.

·         What options are adjusted to reflect application state?  A primary option is a control’s Enabled property.  By setting Enabled to False, you disable a control, making it unavailable to the user.  So, if the user is not able to save a current record, the command button that does the save should have an Enabled property of False.  A more drastic disabling of a control is setting its Visible property to False.  In this case, there is no misunderstanding about application state.  As the application moves from one state to another, you need to determine which controls should be enabled and which should be disabled.

·         For text box controls, a property of importance is the Locked property.  If a value in a text box is not to be edited, set Locked to True.  When editing is allowed (the state changes), toggle the Locked property to False.  For text boxes that are always locked (used for display, not editing purposes), use color (red is good) to indicate they are not accessible.  When editing in a text box, use the SetFocus method to place the cursor in the box, making it the active control (giving it focus) and saving the user a mouse click.  The SetFocus method can also be used to programmatically move the user from one text box to the next in a desired order.

·         Another mechanism for moving from one control to another in a prescribed order is the TabIndex property, in conjunction with TabStop.  If TabStop is True, TabIndex defines the order controls become active (only one control can be active at a time) as the <Tab> key is pressed (the order is reversed when <Shift>-<Tab> is pressed).  When controls are placed on a form at design time, they are assigned a TabIndex value with TabStop = True.  If you don’t want a control to be made active with <Tab>, you need to reset its TabStop property to False.  If the assigned order is not acceptable, reset the TabIndex properties for the desired controls, starting with a low number and increasing that value with each control added to the <Tab> sequence.  A primary application for <Tab> sequencing is moving from one text box to the next in a detailed input form.

·         If the concepts of control focus and tab movements are new or unfamiliar, try this.  Start a new application in Visual Basic.  Add three command buttons (Command1, Command2, Command3), then three text boxes (Text1, Text2, Text3).  Run the application.  The first command button (Command1) should have focus (a little outline box is around the caption).  If you press <Enter> at this point, this button is ‘clicked.’  Press the <Tab> key and the focus moves to the second command button.  Press <Tab> twice.  The focus should now be in the first text box (the cursor is in the box).  Keep pressing <Tab> and watch the focus move from one control to the other, always in the same order.  Pressing <Shift>-<Tab> reverses the order.  Now, for each command button, set the TabStop property to False (removing them from the tab sequence).  Re-run the application and you should note the focus only shifts among the text boxes.  Try resetting the TabIndex properties of the text boxes to change the shift direction.  Always use the idea of focus in your applications to indicate to the user what control is active.

·         All of this application state talk may sound complicated, but it really isn’t.  Again, it’s all just a matter of common sense.  After you design your interface, sit back and step through your application in the Visual Basic environment, exercising every option available.  With each option, ask yourself what the user needs to see.  Implement the necessary logic to make sure this is all the user sees.  Make sure moves from one state to another are apparent and correct.  Try lots of things until you are comfortable with the finished product.  The Visual Basic environment makes performing such tasks quite easy.

Example 6-4

Authors Table Input Form (Application State)

The Authors Table Input Form can operate in one of three states: View state, Add state or Edit state.  In View state, the user can navigate from record to record, access adding and/or editing records, delete a record, or exit the application.  In View state, data cannot be changed.  In both Add and Edit states, no navigation should be possible, data can be changed, and the user should have access to the Save and Cancel functions.  Each of these states can be implemented using command button Enabled properties and text box Locked properties.  We use TabIndex (and TabOrder) to control shift of focus in the text box controls.  We will use a general procedure to allow switching from one state to another.

1.    Open Example 6-3 in the Visual Basic environment.  We will modify this example to include state considerations.

2.    Remove the command buttons from tab sequencing by setting all (eight buttons) of their TabStop properties to False.  Also set TabStop to False for the txtAuthorID text box (we will not edit this value - we’ll explain why later).  Set TabIndex for txtAuthor to 1 and TabIndex for txtYearBorn to 2.

3.    Add a general Sub procedure named SetState with string argument AppState.  To add this procedure, with the code window active, select Tools and Add Procedure.  Fill in the blanks appropriately and a framework appears in the code window.

4.    Add this code to the SetState procedure (note, the added AppState argument):

Private Sub SetState(AppState As String)

Select Case AppState

Case "View"

  txtAuthorID.BackColor=vbWhite

  txtAuthor.Locked = True

  txtYearBorn.Locked = True

  cmdPrevious.Enabled = True

  cmdNext.Enabled = True

  cmdAddNew.Enabled = True

  cmdSave.Enabled = False

  cmdCancel.Enabled = False

  cmdEdit.Enabled = True

  cmdDelete.Enabled = True

  cmdDone.Enabled = True

  txtAuthor.SetFocus

Case "Add", "Edit"

  txtAuthorID.BackColor=vbRed

  txtAuthor.Locked = False

  txtYearBorn.Locked = False

  cmdPrevious.Enabled = False

  cmdNext.Enabled = False

  cmdAddNew.Enabled = False

  cmdSave.Enabled = True

  cmdCancel.Enabled = True

  cmdEdit.Enabled = False

  cmdDelete.Enabled = False

  cmdDone.Enabled = False

  txtAuthor.SetFocus

End Select

End Sub

This code sets the application in View, Add or Edit state.  Note which buttons are available and which are not.  Notice the Author ID box is red in Add and Edit state to indicate it cannot be changed.  Notice that the Add and Edit states are the same (for now) and are just a ‘toggling’ of the View state – this will occur quite often – a great place for ‘cut and paste’ coding.  We now need to modify the application code to use this procedure to move from state to state.

4.    We want to be in the View state when the application is initialized.  Attach this code to the Form_Activate event:

Private Sub Form_Activate()

Call SetState("View")

End Sub

5.    When the Add New button is clicked, we want to switch to Add state.  Add this line of code at the top of the cmdAddNew_Click event procedure:

Call SetState("Add")

6.    When the Edit button is clicked, we switch to Edit state.  Add this line of code at the top of the cmdEdit_Click event procedure:

Call SetState("Edit")

7.    Following a Cancel or Save operation (in Add or Edit state), we want to return to View state.  Place this line at the end of the cmdCancel_Click and cmdSave_Click event procedures:

Call SetState("View")

The Delete button does not need any change of state code – it only works in View state and stays in that state following a delete.

8.    We’re almost done.  This is a small change, but an important one that gives your application a professional touch.  Notice that if you click the Previous button and the recordset pointer is at the first record, nothing changes.  Similarly, at the end of the recordset, if you click Next, nothing changes.  This lack of change might confuse the user.  To give the user some feedback that they’ve reached a limit, I like to provide some audible feedback.  In both the cmdPrevious_Click and cmdNext_Click procedures, add the Visual Basic Beep statement within the If/End If structure.  Then, when the user bumps a limit, a little beep is heard.

9.    Save and run the application.  Notice how the various buttons change state as different functions are accessed on the interface form.  In Add and Edit state (the ID box is red), check the tab order of the two text boxes (a very short tab order!).  A warning – if you change any value in Add or Edit mode, it will be saved in the database (a feature of the Jet engine).  In each state, it is obvious to the user what functions are available and when they are available.  Do you hear the beep when you try to move past a limit at the end or beginning of the recordset?

Entry Validation

·         Throughout your work with databases, you will find that viewing database information is an easy task with Visual Basic.  Things quickly become difficult, though, when you want to modify information in a database.  And, things become very difficult when you allow your user to type information.  That’s why, if at all possible, don’t allow your user to type things.  Use point and click type controls whenever possible.

·         Checking input information from a user requires programming on your part.  You must insure information being put in a database is correct.  There are two steps to checking information from a user: entry validation and input validation.  Entry validation is associated with text box controls and checks for proper keystrokes.  Input validation is associated with several control types and checks to make sure entries and choices meet certain requirements.  In this section, we address entry validation.  Input validation is addressed in the next section of this chapter.

·         As mentioned, entry validation checks for proper keystrokes.  For example, if a numerical entry is needed, only allow the pressing of number keys.  If spaces are not allowed, don’t allow them.  If an input must be in upper case letters, don’t allow lower case letters to be typed.  Restricting keystrokes is referred to as key trapping.

·    Key trapping is done in the KeyPress event procedure of a text box.  Such a procedure has the form (for a text box named txtText):

Sub txtText_KeyPress (KeyAscii as Integer)

  .

  .

  .

End Sub

In this procedure, every time a key is pressed in the corresponding text box, the ASCII code for the pressed key is passed to the procedure as the argument KeyAscii.  With key trapping, if KeyAscii is an acceptable value, we do nothing.  If KeyAscii is not acceptable, we set KeyAscii equal to zero and exit the procedure.  Doing this has the same result of not pressing a key at all.   ASCII values for all keys are available via the on-line help in Visual Basic.  The BASIC function Asc can also be used to determine a key’s ASCII code.  And some keys are also defined by symbolic constants.  Where possible, we will use symbolic constants; else, we will use the ASCII values.

·    As an example, say we have a text box (named txtExample) and we only want to be able to enter upper case letters (ASCII codes 65 through 90, or, correspondingly, symbolic constants vbKeyA through vbKeyZ).  The KeyPress procedure would look like (the Beep causes an audible tone if an incorrect key is pressed):

Sub txtExample_KeyPress(KeyAscii as Integer)

If KeyAscii >= vbKeyA And KeyAscii <= vbKeyZ Then

  Exit Sub

Else

  KeyAscii = 0

  Beep

End If

End Sub

·    In key trapping, it's advisable to always allow the backspace key (ASCII code 8; symbolic constant vbKeyBack) to pass through the KeyPress event.  Else, you will not be able to edit the text box properly.  Modifying the above example, this code would be:

Sub txtExample_KeyPress(KeyAscii as Integer)

If (KeyAscii >= vbKeyA And KeyAscii <= vbKeyZ) Or KeyAscii = vbKeyBack Then

  Exit Sub

Else

  KeyAscii = 0

  Beep

End If

End Sub

·         Rather than just beep when an unacceptable keystroke is encountered, you can also use key trapping to automatically correct invalid inputs.  For example, if the input requires all upper case letters, you can use the BASIC UCase function to convert any lower case letters to upper case letters.

Example 6-5

Authors Table Input Form (Entry Validation)

In the Authors Table Input Form, the Year Born field can only be numeric data. 

1.    Load Example 6-4 completed earlier.  We will modify this example to include entry validation.

2.    Attach this code to the txtYearBorn_KeyPress event (make sure you select the proper event in the code window – don’t use the Change event!):

Private Sub txtYearBorn_KeyPress(KeyAscii As Integer)

If (KeyAscii >= Asc("0") And KeyAscii <= Asc("9")) Or KeyAscii = vbKeyBack Then

  Exit Sub

Else

  Beep

  KeyAscii = 0

End If

End Sub

3.    Save and run the application.  Click Edit to switch to Edit state..  Click the Year Born text box.  Try some typing.  You should only be able to type numbers (or use the backspace key) in the Year Born entry box.

Input Validation

·         In the example just studied, although the user can only input numeric data for the Year Born field, there is no guarantee the final input would be acceptable.  What if the input year is past the current year?  What if the year is 1492?  A second step in validation is to check values in context.  Do the input values make sense?  Do the values meet established rules?  This step is input validation.

·         Some common validation rules are:

Þ    Is this field required?  If a field is required and no input is provided, this could cause problems. 

Þ    Is the input within an established range?  For example, if entering a day number for the month of April, is the value between 1 and 30?

Þ    Is the input the proper length?  Social security numbers (including hyphens) require 11 characters.  If 11 characters are not detected, the input is not a valid social security number.  The BASIC Len function can be used here, as can a text box MaxLength property (to limit the length).

Þ    Is the input conditional?  Some fields only need to filled in if other fields are filled in.  For example, if a user clicks to ship to another address, you need to make sure that address exists.

Þ    Is the input a primary key?  If so, and the user has the capability of entering a value, we must insure it is a unique value.  Each primary key value in a table must be different.

·         The amount of input validation required is dependent on the particular field.  Many times, there is none needed.  You, as the programmer, need to examine each input field and answer the questions posed above: is the field required, must it be within a range, is it length restricted, is it conditional?  Any Yes answers require BASIC code to do the validation.  You will probably find additional questions as you develop your database skills.

·         Where does the validation code go?  It really depends on what database technology (DAO or ADO) you are using and how you implement database editing.  We will discuss this topic in detail in Chapter 7.  For our example we have been creating, we will write a general procedure named ValidateData that is called in the Click event of the Save button.  The user clicks this button when done editing, making it a great place to check validity.  If any validation rules are violated, we don’t allow the requested change(s).

·         We see entry and input validation require a bit of programming on our part.  But, it is worth it.  Field validation insures the integrity of the information we are putting in a database.  We always need to maintain that integrity.  And, one last time for emphasis (are you getting the idea this is important) – if you can eliminate user typing – do it!