Introduction to Built-In Procedures

Built-In Functions and Procedures

Introduction

Microsoft Access and Microsoft Visual Basic ship with a lot of functions and procedures you can use in your database. Before creating your own procedures, you should know what is already available so you don't have to re-invent and waste a great deal of your time. The functions already created are very efficient and were tested in various scenarios so you can use them with complete reliability. The available functions range in various types. There are so many built-in functions and procedures that we can only introduce some of them here. You can find out about the other in the Help files because they are fairly documented.

When studying variables, we introduced and also reviewed the conversion functions. Here is a summary of these functions.

A message box is a special dialog box used to display a piece of information to the user. As opposed to a regular form, the user cannot type anything on the message box. There are usually two kinds of message boxes you will create: one that simply displays information and one that expects the user to make a decision.

A message box is created using the MsgBox function. Its syntax is:

MsgBox([Message] [Buttons] [Title] [HelpFile] [Context])

The MsgBox function takes five arguments and only the first one is required: the Message.

The Message argument is the string that the user will see displaying on the message box. As a string, you can display it in double quotes, like this "That's All Folks". Here is an example:

Private Sub cmdMessageBox_Click()
    MsgBox ("Your credentials have been checked.")
End Sub

This would produce:

You can also create it from other pieces of strings. The Message argument can be made of up to 1024 characters. To display the Message on multiple lines, you can use either the constant vbCrLf or the combination Chr(10) & Chr(13) between any two strings. Here is an example:

Private Sub cmdMessageBox_Click()
    MsgBox ("Your logon credentials have been checked." & _
            vbCrLf & "To complete your application, please " & _
            "fill out the following survey")
End Sub

This would produce:

The Buttons argument specifies what button(s) should display on the message box. There are different kinds of buttons available and Visual Basic recognizes them by a numeric value assigned to each. The Buttons argument can have one of the following constant

Button Constant	Value	Display
vbOKOnly	0
vbOKCancel	1
vbAbortRetryIgnore	2
vbYesNoCancel	3
vbYesNo	4
vbRetryCancel	5

If you decide to display one of these buttons, you Here is an example that displays the Yes and the No buttons on the message box:

Private Sub cmdMessageBox_Click()
    Dim iAnswer As Integer
    
    iAnswer = MsgBox("Your logon credentials have been checked " & _
                    "and your application has been approved: Congratulations!" & _
                     vbCrLf & "Before leaving, would you like " & _
                     "to take our survey survey now?", vbYesNo)
End Sub

This would produce:

Besides the buttons, to enhance your message box, you can display an icon in the left section of the message box. To display an icon, you can use the following icons on the message box

Icon Constant	Numeric Value	Description
vbCritical	16
vbQuestion	32
vbExclamation	48
vbInformation	64

To use one of these icons, you must combine its Icon Constant with one of the Button Constants reviewed previously. To perform this combination, you use the OR operator. Here is an example:

Private Sub cmdMessageBox_Click()
    Dim iAnswer As Integer
    
    iAnswer = MsgBox("Your logon credentials have been checked " & _
                    "and your application has been approved: Congratulations!" & _
                     vbCrLf & "Before leaving, would you like " & _
                     "to take our survey survey now?", vbYesNo Or vbQuestion)
End Sub

This would produce:

If you create a message box with more than one button, the most left button usually has a thick border, indicating that it is the default. If the user presses Enter after viewing the button, the effect would be the same as if he had clicked the default button. If you want, you can designate another button as the default. To do this, you can use one of the following values:

Once again, to specify a default value, use the OR operator to combine a Default Button Constant with any other combination. Here is an example:

Private Sub cmdMessageBox_Click()
    Dim iAnswer As Integer
    
    iAnswer = MsgBox("Your logon credentials have been checked " & _
                    "and your application has been approved: Congratulations!" & _
                     vbCrLf & "Before leaving, would you like " & _
                     "to take our survey survey now?", _
                     vbYesNoCancel Or vbQuestion Or vbDefaultButton2)
End Sub

This would produce:

These additional buttons can be used to further control what the user can do:

The Title argument is the caption that would display on the title bar of the message box. It is a string whose word or words you can enclose between parentheses or that you can get from a created string. The Title argument is optional. As you have seen so far, if you omit, the message box is equipped with the "Microsoft Access" string as its default value. Otherwise, if you want a custom title, you can provide it as the third argument to the MsgBox() function. The caption can be a simple string. Here is an example:

Private Sub cmdMessageBox_Click()
    Dim iAnswer As Integer
    
    iAnswer = MsgBox("Your logon credentials have been checked " & _
                    "and your application has been approved: Congratulations!" & _
                     vbCrLf & "Before leaving, would you like " & _
                     "to take our survey survey now?", _
                     vbYesNoCancel Or vbQuestion Or vbDefaultButton2, _
                     "Crofton Circle of Friends - Membership Application")
End Sub

This would produce:

Notice that the caption is now customized instead of the routine "Microsoft Access". The caption can also be a string created from an expression or emanating from a variable or value. Here is an example:

Private Sub cmdMessageBox_Click()
    Dim iAnswer As Integer
    Dim dteCurrent As Date
    Dim strTitle As String
    
    dteCurrent = Date
strTitle = "Crofton Circle of Friends - Membership Application: " + Str(dteCurrent)
    iAnswer = MsgBox("Your logon credentials have been checked " & _
                    "and your application has been approved: Congratulations!" & _
                     vbCrLf & "Before leaving, would you like " & _
                     "to take our survey survey now?", _
                     vbYesNoCancel Or vbQuestion Or vbDefaultButton2, _
                     strTitle)
End Sub

If your application is using a help file, you can specify this and let the message box use it. The HelpFile argument is a string that specifies the name of the help file, and the Context argument provides the number that corresponds to the appropriate help topic for the message box.

The MsgBox() function can be used to return a value. This value corresponds to the button the user clicks on the message box. Depending on the buttons the message box is displaying, after the user has clicked, the MsgBox() function can return one of the following values:

If the user click	The function returns	Numeric Value
	vbOK	1
	vbCancel	2
	vbAbort	3
	vbRetry	4
	vbIgnore	5
	vbYes	6
	vbNo	7

 

Practical Learning: Creating Message Boxes

1. Open the Exercise2 database and, in the Database Window, click Forms
2. Double-click the Messages form to open it
    
3. Switch the form to Design View.
4. Right-click the Message 1 button and click Build Event... On the Choose Builder dialog box, click Code Builder and click OK
5. In the Code Editor, implement it as follows:
    

   Private Sub cmdMessage1_Click() MsgBox "This is Visual Basic as simple as it can get" End Sub

6. To test the form, return to Microsoft Access and switch the form to Form View.
7. On the form, click the Message 1 button.
8. Notice that a message box displays. Also notice the caption on the title bar displays Microsoft Access.
9. Click OK to close the message box.
10. Switch the form back to Design View and return to the Code Editor
11. Instead of the title bar displaying Microsoft Access as the caption, you can set your own caption. This is done through the 3rd argument of the MsgBox function. To see an example, on the Object combo box, select cmdMessage2 and implement its Click event as follows:
     

    Private Sub cmdMessage2_Click() MsgBox "Before formatting a floppy disk, " & _ "make sure you know its content", , _ "Disk Formatting Instructions" End Sub

12. Test the form and the Message 2 button. Then return to the Code Editor
13. When creating a message box using the MsgBox function, you can decide which button you want to use, using one of the constants we have listed earlier.
    To see an example, on the Object combo box, select cmdMessage3 and implement its Click event as follows:
     

    Private Sub cmdMessage3_Click() MsgBox "This will be your only warning", _ vbOKOnly + vbExclamation, _ "Attention! Attention!! Attention!!!" End Sub

14. Test the form and the Message 3 button. Return to the Code Editor
15. If you want to display a message on different lines, you can use the vbCrLf constant. As an example, on the Object combo box, select cmdMessage4 and implement its Click event as follows:
     

    Private Sub cmdMessage4_Click() MsgBox "You are about to embark on a long journey." & _ vbCrLf & "If your courage is still fresh, " & _ "now is the time to let us know!", _ vbOKCancel + vbQuestion, _ "Accept or Cancel the Mission" End Sub

16. Test the form and experiment with the Message 4 button. Then return to the Code Editor.
17. You can also display a message on various lines using the Chr() function. To see an example, on the Object combo box, select cmdMessage5 and implement its Click event as follows:
     

    Private Sub cmdMessage5_Click() MsgBox "This message usually appears when trying " & _ "to format a floppy disk while the floppy drive " & _ "is empty. " & Chr(13) & Chr(10) & _ "When or if this happens, make sure you have a " & _ " floppy disk in the floppy drive.", _ vbAbortRetryIgnore + vbCritical, _ "Floppy Disk Formatting" End Sub

18. Test the form and the Message 5 button. Then return to the Code Editor.
19. The usefulness of the MsgBox function is demonstrated in your ability to perform an action based on the button the user has clicked on the message box. Indeed, the implementations we have used so far were on the MsgBox method. If you want to get the button that the user has clicked, you have to use the function itself. The true capture of the clicked button is revealed by your finding out the clicked button. This is done using conditional statements that we have not learned so far. Therefore, we will just learn how to implement the function and how to assign a response button to it; throughout this tutorial, and whenever necessary, we will eventually see what to do when a certain button has been clicked. To see an example, on the Object combo box, select cmdMessage6 and implement its Click event as follows:
     

    Private Sub cmdMessage6_Click() Dim intResponse As Integer intResponse = MsgBox("Your printing configuration " & _ "is not fully set." & vbCrLf & _ "If you are ready to print, click" & vbCrLf & _ "(1) Yes: To print the document anyway" & vbCrLf & _ "(2) No: To configure printing" & vbCrLf & _ "(3) Cancel: To dismiss printing", _ vbYesNoCancel + vbInformation, _ "Critical Information") End Sub

20. Test the form and the Message 6 button. Then return to the Code Editor
21. When a message box displays, one of the buttons, if more than one is displaying, has a thicker border than the other(s); such a button is called the default button. By default, this is the 1st or most left button on the message box. If you want to control which button would be the default, use one of the default constant buttons listed above. To see an example, on the Object combo box, select cmdMessage7 and implement its Click event as follows:
     

    Private Sub cmdMessage7_Click() Dim intAnswer As Integer intAnswer = MsgBox("Do you want to continue this " & _ "operation?", _ vbYesNoCancel + vbQuestion + vbDefaultButton2, _ "National Inquiry") End Sub

22. Test the form and the Message 7 button. Then return to the Code Editor.
23. Although the user cannot type on a message box, not only can you decide what it displays, but you can also use string variables that would be available only when the form is running. As an example, on the Object combo box, select cmdMessage8 and implement its Click event as follows:
     

    Private Sub cmdMessage8_Click() Dim strEmplName As String Dim intInquiry As Integer strEmplName = CStr(txtEmployeeName) intInquiry = MsgBox("Hi, " & strEmplName & Chr(13) & _ "I think we met already." & vbCrLf & _ "I don't know when. I don't know where." & vbCrLf & _ "I don't know why. But I bet we met somewhere.", _ vbYesNo + vbInformation, _ "Meeting Acquaintances") End Sub

24. To test the form, return to Microsoft Access
25. On the Employee Name text box, type Joseph Douglas
26. Click the Message 8 button and see the result.
27. Click one of the buttons to close the message box.
28. Close the running form, click its close button .

The Input Box

Microsoft Visual Basic provides a function that allows you to request information from the user who can type it in a text field of a dialog box. The function used to accomplish this is called InputBox and its basic syntax is:

InputBox(prompt)

The most basic piece of information you should provide to the InputBox() function is referred to as the prompt. It should be a string that the user will read and know what you are expecting. Here is an example:

Private Sub cmdRequestDOB_Click()
    InputBox "Enter your date of birth (mm/dd/yyyy):"
End Sub

This would produce

Upon reading the message on the Input box, the user is asked to enter a piece of information. The type of information the user is supposed to provide depends on you, the programmer. Therefore, there are two important things you should always do. First you should let the user know what type of information is requested. Is it a number (what type of number)? Is it a string (such as the name of a country or a customer's name)? Is it the location of a file (such as C:\Program Files\Homework)? Are you expecting a Yes/No True/False type of answer (if so how should the user provide it)? Is it a date (if it is a date, what format is the user supposed to enter)? These questions mean that you should state a clear request to the user and specify what kind of value you are expecting. For example, instead of the question above, you can implement the InputBox() function as follows:

Private Sub cmdRequestDOB_Click()
    InputBox "Please enter your date of birth (mm/dd/yyyy):"
End Sub

Another solution, also explicit enough, consists of providing an example to the user.

The second thing you should take care of is the value the user would have typed. After typing a value, the user would click one of the buttons: OK or Cancel. If the user clicks OK, you should retrieve the value the user would have typed. It is also your responsibility to find out whether the user typed a valid value. Because the InputBox() function can return any type of value, it has no mechanism of validating the user's entry. To retrieve the value of the Input Box dialog when the user clicks OK, you must use the InputBox() function. Here is an example:

Private Sub cmdRequestDOB_Click()
    Dim dteDOB As Date
    
    dteDOB = InputBox("Please enter your date of birth (mm/dd/yyyy):")
    txtDOB = dteDOB
End Sub

Sometimes, even if you provide an explicit request, the user might not provide a new value but click OK. The problem is that you would still need to get the value of the text box and you might want to involve it in an expression. You can solve this problem and that of providing an example to the user by filling the text box with a default value. Besides the prompt, Microsoft Visual Basic provides a more elaborate InputBox() function that allows you to specify more options, including a default value. The syntax used then is:

InputBox(prompt, Title, Default, XPos, YPos, HelpFile, Context)

Using this syntax, you can provide a title to display on the title bar of the Input Box dialog. This is taken care of by the Title argument. The XPos and YPos arguments allow you decide the position of the Input Box from left (XPos) and top (YPos) measurements of the screen.