Interacting Functions

Often, you'll need to prompt the users to enter relevant data or to simply share information. Either way, the application is interacting with the users. The two most frequently used functions in this area are the InputBox and MsgBox functions.

The InputBox Function

Earlier in this chapter, you worked with a few examples that gathered information from you in order to finish a task. This type of interaction can really come in handy because you won't always know every possible value when you're coding the solution. In addition, this type of solution lets you use one procedure over and over in the same way you might use a parameter query.

The InputBox function takes the form

 InputBox(prompt[, title][, default][, xpos][, ypos][, helpfile, context]) 

The only required argument is prompt it's a String data type that represents the message the dialog box displays and is usually some kind of query that describes the type of data the users need to enter in response to the dialog box. The maximum number of characters allowed in prompt is 1,024. You can use the title String argument to display a title for the dialog box. If you omit this argument, VBA displays the application's name. Specify a string default value for the dialog box using the default option. The xpos and ypos arguments are both Numeric data types that specify the dialog box's position in relation to the screen. Specifically, xpos specifies the distance from the left side of the screen to the left side of the dialog box and ypos specifies the distance from the top of the screen to the top of the dialog box. You'll seldom use helpfile and context, but they go together; use them both or not at all. The helpfile argument is a String value that specifies the help file to use if the user clicks the dialog box's Help button and context is a Numeric value that specifies the context number to use within helpfile. (Note that creating Help files is beyond the scope of this book.)

The MsgBox Function

Use MsgBox to display information to users in a dialog box. You control the type of dialog box and how the users can respond. This function returns an integer value and uses the form

 MsgBox(prompt[, buttons][, title][, helpfile, context]) 

where prompt is the only required argument. It's a String data type and represents the message displayed in the message box. You control how the users respond by specifying the type of buttons offered in the message box using buttons. Table 5.10 lists the button possibilities. Use title (a string) to display text in the dialog box's title bar. Both helpfile and context are the same for MsgBox as they are for InputBox (see the previous section for information on these two optional arguments).

Table 5.10. MsgBox Button Constants

Constant	Button Description	Integer Value
vbOKOnly	OK only	0
vbOKCancel	OK and Cancel	1
vbAbortRetryIgnore	Abort, Retry, and Ignore	2
vbYesNoCancel	Yes, No, and Cancel	3
vbYesNo	Yes and No	4
vbRetryCancel	Retry and Cancel	5

Table 5.11 lists the icons you can display in the buttons argument in the form

 buttonsconstant + iconconstant 

Table 5.11. Icon Constants

Constant	Icon	Integer Value
vbCritical	Critical Message	16
vbQuestion	Warning Query	32
vbExclamation	Warning Message	48
vbInformation	Information Message	64

For instance, the function

 MsgBox "You are about to delete records. Do you wish to continue?", vbOKCancel +  vbCritical, "Warning!" 

resembles the message box shown in Figure 5.13. There are two buttons: OK and Cancel. The critical icon is the white x in the red circle. The plus sign combining vbOKCancel and vbCritical lets you display more than one element along with the message.

Figure 5.13. You determine the type of message and the available buttons in a message box.

The function's returned value comes from the user's actions the clicked button. Table 5.12 lists the possible values.

Table 5.12. Button Values

Button Clicked	Returned Value	Integer Value
OK	vbOK	1
Cancel (or Esc)	vbCancel	2
Abort	vbAbort	3
Retry	vbRetry	4
Ignore	vbIgnore	5
Yes	vbYes	6
No	vbNo	7

The message box in Figure 5.13 has two buttons, OK and Cancel. That means the MsgBox function returns either 1 or 2, accordingly.

CASE STUDY: Business Days

Part of managing all the projects tracked in TimeTrack.mdb is estimating the number of business days you have to get a particular job done. VBA's DateDiff function can quickly calculate the number of actual days, but there's no built-in function that excludes weekend days from the count.

In the following solution, you'll add a command button and a text box to the Projects form. Clicking the command button displays the number of business days between the project's start and end dates in the new text box. That way, you'll have a more accurate workday count for the project.

1. Open the Projects form in Design view and add a command button and a text box to the right of the Estimated End Date control. Name the command button cmdCalculate and the text box txtBusinessDays and set the text box control's Visible property to No.
   
   

2. Click the Code button on the Form Design toolbar to open the form's module in the VBE.
   
   

3. Enter the following event procedures:
   
   

   
   

    Private Sub cmdCalculate_Click()   'Determine the number of business days   'allotted for the project   'This procedure does not allow for holidays   Dim dtmStart As Date   Dim dtmEnd As Date   Dim intTotalDays As Integer   Dim intWeekendDays As Integer   Dim intBusinessDays As Integer   If IsNull(StartDate) Then     MsgBox "Please enter a start date", _      vbOKOnly, "Error"     Exit Sub   End If   If IsNull(EstimatedEndDate) Then     MsgBox "Please enter an end date", _      vbOKOnly, "Error"     Exit Sub   End If   dtmStart = StartDate   dtmEnd = EstimatedEndDate   Select Case DatePart("w", dtmStart, vbMonday)     Case Is = 6       dtmStart = DateAdd("d", dtmStart, 2)     Case Is = 7       dtmStart = DateAdd("d", dtmStart, 1)   End Select   Select Case DatePart("w", dtmEnd, vbMonday)     Case Is = 6       dtmEnd = DateAdd("d", dtmEnd, -1)     Case Is = 7       dtmEnd = DateAdd("d", dtmEnd, -2)   End Select   intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1   intWeekendDays = DateDiff("ww", dtmStart, dtmEnd, vbMonday) * 2   intBusinessDays = intTotalDays - intWeekendDays   txtBusinessDays = intBusinessDays   txtBusinessDays.Visible = True End Sub Private Sub Form_Current()   'Reset txtBusinessDays control   txtBusinessDays = ""   txtBusinessDays.Visible = False End Sub 

   
   

4. Save the code and return to Access by clicking the View Microsoft Office Access button on the VBE's standard toolbar or by clicking the form button on the Windows taskbar.
   
   

5. Click the View button to see the modified form in Form view, as shown in Figure 5.14.
   
   

   Figure 5.14. You can't see the new text box because its Visible property is set to False.

   
   
   

6. Click the Exclude Weekends command button to display the number of business days between the project's start and end dates, as shown in Figure 5.15. Don't worry if your answer differs from the example. The actual number of days depends on the actual day (not the day on which this example was evaluated).
   
   

   Figure 5.15. Click the new command button to calculate and display the number of estimated working days for the current project.

   
   
   

After declaring and defining a number of variables, the IsNull function checks for a start and end date. If either is missing, the procedure alerts the user and exits the procedure.

When both dates are present, the first Select Case statement adjusts the start date if it falls on a Saturday or Sunday by adding 1 or 2, as appropriate. The second Select Case statement adjusts the end date by subtracting 1 or 2, accordingly.

Learn about the Select Case and If End statements in "Using Select Case," p. 94, and "Using If…Then…Else," p. 91.

The procedure in this case study responds to a controls event procedure. Learn more about this process in "Responding to Events," p. 161, and "The Event Sequence for Controls," p. 162.

The remaining calculations determine the total number of days and the total number of weekend days (Saturday and Sunday) between the two adjusted dates. The number of business days between the two dates is the difference between the total number of days and the total number of weekend days.

There's nothing in this procedure to prevent you from entering an end date that's earlier than the start date. You probably won't want to do this, but occasionally this type of calculation is warranted. You have to decide for yourself if you want to add the necessary functionality to limit date values.

TIP

You might remember that Chapter 3 stressed the importance of using appropriate data types for each field. Now you can see at least one benefit of that rule. You don't have to worry about invalid date entries because the underlying table field is a Date/Time data type. If you try to enter anything but a date, Access rejects the entry. That's one error-handling task you can skip because you eliminated the possibility of that error at the application's very foundation the table.

You might be wondering what the Current event procedure does. That simple procedure resets the contents of the text box and sets its Visible property back to False when you move to another record.

The procedure itself incorporates many of the good coding practices you learned about in Chapter 2. Specifically, each line is indented appropriately and there's a descriptive comment at the beginning of both procedures that describes their purpose. In addition, the procedure declares and uses several variables of various types.