Home

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.

Conversion Functions

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

Function  
Name Return Type Description
CBool Boolean Converts an expression into a Boolean value
CByte Byte Converts an expression into Byte number
CDate Date Converts and expression into a date or time value
CDbl Double Converts an expression into a flowing-point (decimal) number
CInt Integer Converts an expression into an integer (natural) number
CCur Currency Converts an expression into a currency (monetary) value
CLng Long Converts an expression into a long integer (a large natural) number
CSng Single Converts an expression into a flowing-point (decimal) number
CStr String Converts an expression into a string
 

Message and Input Boxes

 

The Message Box

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 OK
vbOKCancel 1 OK Cancel
vbAbortRetryIgnore 2 Abort Retry Message Box Button: Ignore
vbYesNoCancel 3 Yes Message Box Button: No Cancel
vbYesNo 4 Yes Message Box Button: No
vbRetryCancel 5 Retry Cancel

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 Question
vbExclamation 48 Exclamation
vbInformation  64 Information

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:

Default Button Constant Numeric Value If the message box contains more than one button, the default would be
vbDefaultButton1  0 The first button
vbDefaultButton2  256 The second button
vbDefaultButton3  512 The third button
vbDefaultButton4  768 The fourth button

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:

Constant  Value Effect
vbApplicationModal 0 The user must dismiss the message box before proceeding with the current database
vbSystemModal 4096 The user must dismiss this message before using any other open application of the computer

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
OK vbOK 1
Cancel vbCancel 2
Abort vbAbort 3
Retry vbRetry 4
Ignore vbIgnore 5
Yes vbYes 6
No vbNo 7
 
 

Practical Learning 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.

 
 

Previous Copyright © 2005-2010 FunctionX, Inc. Next