This chapter is from the book
This chapter is from the book
This chapter is from the book
Interaction
At times you need to provide information to the application's user or get information from them. This is interacting with the users. Two functions that can perform such an action are the MsgBox and InputBox functions.
The MsgBox Function
You use the MsgBox function to present information to users with an opportunity to respond to the information. You have control over how the message box appears and what response the user can make. The MsgBox function uses the following syntax, where prompt is the only required argument and represents a text string that constitutes the message presented by the message box:
MsgBox(prompt[, buttons][, title][, helpfile, context])
The users can respond through a choice of one or more buttons. Table 4.11 lists various button options you can use. You can supply a string value for title that displays in the title bar of the message box. The other two optional arguments—helpfile and context—are seldom used and go together. The helpfile argument is a string that points to a help file to be used if the user clicks the message box's Help button. The context argument is a numeric value that specifies a number to be used within the help file. (Note: Creating help files is outside the scope of this book.)
Table 4.11. MsgBox Button Constants
|
Constant |
Description |
Integer Value |
|
vbOkOnly |
OK button |
0 |
|
vbOKCancel |
OK and Cancel buttons |
1 |
|
vbAbortRetryIgnore |
Abort, Retry, and Ignore buttons |
2 |
|
vbYesNoCancel |
Yes, No, and Cancel buttons |
3 |
|
vbYesNo |
Yes and No buttons |
4 |
|
vbRetryCancel |
Retry and Cancel buttons |
5 |
Table 4.12 lists constants for the icons that can be displayed in the message box. You can display both icons and buttons using the following syntax:
buttonconstant + iconconstant
Table 4.12. Icon Constants
|
Constant |
Description |
Integer Value |
|
vbCritical |
Critical message |
16 |
|
vbQuestion |
Warning message |
32 |
|
vbExclamation |
Warning message |
48 |
|
vbInformation |
Information message |
64 |
As an example, the following function displays the message box shown in Figure 4.6. There are two buttons—OK and Cancel—and a question mark icon.
)
Figure 4.6 A message box asking whether the user wants to save a record.
MsgBox("Do you want to save this record?", vbOKCancel + vbQuestion,"Warning")
When the user clicks one of the buttons, the function returns its value. Table 4.13 shows the values returned for each button.
Table 4.13. Button Values
|
Button |
Returned Value |
Integer Value |
|
OK |
vbOK |
1 |
|
Cancel |
vbCancel |
2 |
|
Abort |
vbAbort |
3 |
|
Retry |
vbRetry |
4 |
|
Ignore |
vbIgnore |
5 |
|
Yes |
vbYes |
6 |
|
No |
vbNo |
7 |
The following code snippet is built around the message box function previously shown:
Private Function cmdSave_OnClick()
Dim strMsg As String
strMsg = "Do you want to save this record?"
If MsgBox("strMsg, vbOKCancel + vbQuestion,"Warning") = vbOK Then
DoCmd.RunCommand acCmdSaveRecord
Else
Me.Undo
End If
The InputBox Function
The Inputbox function displays a dialog box with a prompt that allows the user to enter a value that can then be assigned to a variable (see Figure 4.7). The following is the syntax for this function, where prompt is a String that displays a message for the user and is the only required argument:
InputBox(prompt[, title][, default][, xpos][, ypos][, helpfile, context])
)
Figure 4.7 An input box asking the user to enter a filename.
The message is used to let the user know what data should be input. The title is a String that is displayed in the title bar of the window. The default is used to set a default value when the box opens. The xpos and ypos arguments allow you to precisely position the box in terms of the top and left of the screen. The helpfile and context arguments are the same as for the MsgBox.
You usually use InputBox to retrieve a value from the user during processing of code. An example follows:
Dim strFilename As String
strFilename = InputBox("Enter file to be imported!", "Import file")
DoCmd.TransferText acExportDelim,, "Import", strFilename
Case Study: Add Work Days
Sometimes you might need to figure a delivery or follow-up date for a shipment. You want to calculate such dates based on business days, not calendar days. The following function allows you to enter a start date and the number of business days and returns the date equal to that number of business days:
- Open the UDFs module or a new one.
- Enter the following code into the module:
Public Function AddWorkdays(dteStart As Date, intnNumDays As Integer) As Date Dim dteCurrDate As Date Dim i As Integer dteCurrDate = dteStart AddWorkdays = dteStart i = 1 Do While i < intNumDays If Weekday(dteCurrDate, vbMonday) <= 5 AND _ IsNull(DLookup("[Holiday]","tblHolidays", "[HolDate] = #" & _ dteCurrDate & "#")) Then i = i + 1 End If dteCurrDate = dteCurrDate + 1 Loop AddWorkdays = dteCurrDate Exit_AddWorkDays: End Function - Test the code by entering the following into the Immediate window. Figure 4.8 shows the results.
? AddWorkDays(#5/16/07#,15)
Figure 4.8 The results from using the AddWorkDays function.
)