Back to the Future, Part 1!

This article assumes that you know how to work in forms in Visual Basic, how to add controls to forms, and how to set properties. If you don't, you may have difficulty following along. It might be good to look at the Help topics presented by the VB editor. Start reading about and playing around with some of this stuff. Remember, we learn by doing.

Now, to start, refer back to the CustomerLabels document from last month's column. For today's lesson, I've added to the query by including the field CSAL, which represents the salesman number. I've also added the field CSAL to the document. To add fields to your query, bring up the Mail Merge Wizard (Tools/Mail Merge) and click the Edit button next to the data source, or you can click the Edit Data Source button on the Merge toolbar. Then, add the field to the document.

I've found that it's easier with mailing labels to simply create a new document using the data source I've already created. The steps are fairly simple and are detailed in my previous column.

inFORMing the User

In today's project, you're adding a user form so that you can offer more selection options to the user. To do this, open the VB editor (Alt+F11 from Word) and either right-click in the Project Explorer and choose Insert/User Form, or choose User Form on the Insert menu. When the form is added, name it frmCustSelect.

Now, you have to add controls to the form. Do this by using the Controls toolbox that comes up when you are working with the form. Here are the controls I added, going from the top to the bottom:

The form should now look like the one shown in Figure 1.

Figure 1: This is what your form should end up looking like.

You might wonder why you'd use frames. Good question! In this form, the frames serve as a container for the controls that live inside the frame. Option buttons have a special attribute: Only one option button within a container can be selected. If you didn't use frames to contain the option buttons that relate to one another, you'd only be able to select one of the option buttons on the form. Since you want to select one of the two relating to ZIP codes, and/or one of the two relating to rep numbers, you contain them in frames. The effect is that only one option button in each frame can be selected.

enCODEing the Form

Since all the action takes place in your form, your module code looks a little different from what was built in the last column. Please see Figure 2:

Figure 2: This is the module code for your form.

The real difference is the new global variables. You've declared SQLCmdText, bCancelMerge, and sMergeTarget as public variables so that your form can use them to execute your macro.

SQLCmdText will store your SQL statement, bCancelMerge will be True for cancel and False otherwise, and sMergeTarget will be "PRINTER" or "DOCUMENT", depending on what happens in your form.

You'll notice that the first thing you do is show the form, and you do it modally, meaning that the code in the GetZipandMerge subroutine won't continue to execute until the form unloads. Once that happens, you check the value of bCancelMerge, and bail if it's True. If it's False, you set the QueryString to the value of SQLCmdText and establish the merge target based on sMergeTarget. This is followed by the execution of the merge, same as last time.

All of these variables are set by code in the user form you added earlier. The form's code is shown in Figure 3.

Figure 3: This is the code for the user form.

Let's start at the top. First, you use Option Explicit to mandate that all variables must be declared. This is a good habit, because it prevents mis-keyed variable names from getting you all screwed up. Then you set up a constant called sDialogTitle with the value "AutoMerge". Since you'll be using the MsgBox method a lot in your error checking, you'll use the constant as a message box title. Change the constant to change the title. You'll use the string array arRepList to store a list of rep numbers.

All of the action here is in the function formValid(), which is called by both the cmdMergeToPrinter and cmdMergeToNew click events. When your user clicks the command button, the click event is triggered.

First, formValid() returns True or False, and your command buttons respond like a high school football coach: "No pass, no play." Then, formValid() sets up the beginning of the SQLCmdText string and adds to it as it validates. I don't think it's necessary to go through each bit of code (you've gotten this far, so you must be able to read), but I will go over one or two special points.

The validation function starts by assuming that our values will pass, so it sets its return value to True. It then proceeds to check the items on the form for general validity, and if a test fails, the program talks to the user with the MsgBox function, sets the focus of the control that failed, sets formValid() to False, and exits the function. Note that only those items that are selected are tested.

As it finds conditions that pass, formValid() incrementally builds the SQLCmdText string. If you want to see what the string contains, put MsgBox SQLCmdText at the end of the function.

And then you push a button. If you push the Cancel button, the form sets bCancelMerge to True and unloads the form. The macro GetZipandMerge reacts to that by bailing as soon as it gets control back.

If you push the Merge to Printer or the Merge to New Document button, and formValid() returns True, the value of sMergeTarget is set to "PRINTER" or "DOCUMENT" (respectively), and the form unloads. GetZipandMerge reacts to that by setting the QueryString to SQLCmdText, setting the document's merge target property appropriately and executing the merge.

One point to bear in mind is the use of the split function to separate the comma-delimited list stored in txtRepList. Split won't work in Word versions prior to 2000, so if you're working with Office 95 or 97 products, you'll have to build your own function to parse the list and separate the items into the array for processing.

You can also rename GetZipandMerge to AutoExec, and it will execute when the document is opened.

You also want to keep the form clean. What if users change their minds about the options they've selected? Leaving the values they've typed in place can be confusing. To keep things neat, clean, and less confusing for the user, you can use the click events of the various check boxes and option buttons to clear the text fields of the unselected options. The code in Figure 4 does that.

Figure 4: This code allows users to clear the text fields.

Now you know how to automate a Word mail merge based on iSeries data. So the next time the phone rings and someone is screaming for labels, you can say, "Dude, put some Avery 5162 labels in your printer, look at your network shared documents drive, and open CustomerLabels.doc. Fill in the form and press Go."

This of course, frees up your time to practice for the all-important network DOOM contest!

If you are an IT Manager and you are reading this, you can be assured that your people aren't playing DOOM on the network. That was a joke.

DOOM is past its time....

Chris Devous is the Director of IT Systems Development at The Antigua Group, Inc., a Peoria, Arizona, garment manufacturer. Chris has been in IT since '82 and lives Arizona with his wife, three children, a bird, two dogs, a cat, and various marine life forms. He can be reached by email at This email address is being protected from spambots. You need JavaScript enabled to view it..