Read an Excerpt
Chapter 4: Controlling the Program
Using Control Structures in Whisky.mdb
Back in Chapter 1, we designed the frmPeople form in Whisky.mdb and noted that we wanted the user to be able to enter their name in one text box. Now maybe you'll see why we went through the previous example. We need to code our application so that it breaks a name up into its constituent parts and stores them in the relevant fields in our table.You are now equipped to implement this feature. There is quite a bit of code involved which we'll write and then look at in detail.
Try It Out—Parsing the User's Name
In Chapter 1, we created the frmPerson form. If you select Forms tab in the database window and open frmMaintPerson, you'll find that this is the same form. We need to start by adding a new fields to the frmMaintPerson form.
1. Open the frmMaintPerson in design mode and add a new unbound text box.
2. Change the text box's name to txtName. Then select the label next to it and change its caption to Name, and its name to lblName.
3. Select Tab Order... from the View menu and move the txtName row up, so it is just beneath LocationID. This ensures that when the user fills in the Location text box and presses tab, the focus will move to the txtName text box.
4. Now set the Enabled property of the text box to No and the Locked property to Yes for all of the following text boxes: Salutation, First Name, Middle Name, Last Name and Suffix, We are going to allow the user to enter their name in the new Name text box, so we must prevent access to these text boxes.
5. The function that actually breaks up (parses) thename into its constituent parts can be found in the Parse Name module. This is a more complicated version of the name parser that you created earlier. As the user enters the name in the txtName text box, it makes sense to call the function in the On Exit event handler for this text box. Select the txtName text box and go to the property sheet. Select the Event tab and click the builder button (the one with three dots) to the right of the On Exit event.
6. Select Code Builder and then enter the following code.
Private Sub txtName_Exit (Cancel as Integer)
Dim intRetVal As Integer
Dim strSalutation As String
Dim strFirstName As String
Dim strMiddleName As String
Dim strLastName As String
Dim strSuffix As String
inRetVal = ParseName (strSalutation, strFirstName, _ strMiddleName, strLastName, strSuffix, txtName)
Salutation = strSalutation
First Name = strFirstName
MiddleName = strMiddleName
Last Name = strLastName
Suffix = strSuffix
Me.Refresh
End Sub
This is a little 'wrapper' function to pass the values to and from the name parsing routine. We simply call the function, specifying the values of its six arguments. As a result, when the user moves off the Name text box, the individual parts of the names are automatically filled in. The current record is also refreshed so that changes are seen immediately. If you want to make the form even more user-friendly, you could hide the five redundant text boxes.
7. Before you can run the form, you need to update the txtName text box when the record changes, otherwise it will contain the name from the last record. You also need to make sure that the text box is cleared when you enter a new record. The code for this should be added to the section which checks for a new record and changes the state of the command button before exiting the event procedure. Select the form's property sheet and add the following code to the On Current event handler:
If IsNull (Me.PersonID) Then
cmdPrev.SetFocus
cmdNext.Enabled =False
txtName = ""
Else
cmdNext.Enabled = True
txtName = BuildName (Salutation, FirstName, MiddleName, LastName, _ Suffix)
End If
8. Now you can finally run this function. Close the module window and the frmMaintPerson form (saving your changes) and go to the Forms tab of the database window. Open frmMaintPerson in form view and select the New button. First, you must select a location from the drop-down list in the LocationID combo box, after which you can enter a name in the Name text box, such as Dr Richard Rowe Snr.
9. When you press Tab to move to the next control, all of the entries are automatically filled in for you. Since they are locked and disabled, you're left on the Date Of Birth text box. Once you've filled this in, the record is complete. Press the Previous button and then the Next button, just to confirm that it has successfully added to the database.
Of course, all of the hard work was done by the ParseName. We'll take a look at that now.
How It Works—The Preparation
Open up the module called Parse Name and look at the (General) (Declaration) section:
Private Const csNAME_SAL As Integer = &H1
Private Const csNAME_FIRST As Integer + &H2
Private Const csNAME_MID As Integer = &H4
Private Const csNAME_LAST As Integer = &H8
Private Const csNAME_SUFFIX As Integer = &H10
Private Const csPREFIX_LIST As String = _ ".MR.MRS.MISS.MS.M/S.MME.MMLLE.DR.SIR.PROF.LORD.MAJOR.COLONEL.LADY."
Private Const csSUFFIX_LIST As String = _ ".I.II.III.IV.SR.SNR.JR.JNR.OBE."
The first five lines declare the constants that will be used to decide which parts of the name have been entered. They'll also be used as return values for the function which splits the name. The last two lines define the prefixes and suffixes. Because they are used in several places, we must declare them in the (Declarations) section to make them available to all the functions in the module.
You can have a look at the code that makes up the function by selecting ParseName in the Proc: combo box. The function has six arguments:
Function ParseName (strOutSal As String, strOutFirst As String, _ strOutMiddle As String, strOutFinal As String, _ strOutSuffix As String, ByVal strvarInName As Variant) As Integer
You will notice that the first five arguments all start strOut, which should indicate to you that they will be used to pass back the various parts of the person's name. By default, arguments are two-way: information can pass into and out of the function via them.
The last argument is the main one—this is what will hold the full name passed into the function. The argument is prefixed by a keyword that you have not yet encountered—ByVal. This means that the argument is one-way: data cannot be passed back via it...