13
Thu, Jun
4 New Articles

The Way We Word: User Forms Help Mail Merge Automation

Microsoft
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times
In my first article on integrating iSeries data into a mail merge, I showed you how to set up a data source, a query, and a merge document that would all work together. In the second article, I showed you how to start automating that process with a VB script that asks the user for a ZIP code and modifies the query used to merge the data.

In this article, I'll show you how to enhance the level of automation by adding a user form to your MS Word VBA project. This form will allow users to go beyond the simple interactions of the earlier articles to set up more complicated queries to retrieve data. You can also incorporate more robust error checking, which will help the user get through the process. The goal is to be able to provide users with a form that asks them what data they want, and then have that data delivered automatically.

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:

Control Name
Type
Caption or [Comment]
chkZipCodes
Checkbox
Select based on Zip Codes:
frameZipOptions
Frame
Zip Code Options
optZipStartsWith
Option Button
Zip Codes Starting With:
txtZipStart
Text Box
[This is the text box to the right of optZipStartsWith]
optZipBetween
Option Button
Zip Codes Between:
txtZipBetweenLower
Text Box
[This is the first text box to the immediate right of optZipBetween]
Label1
Label
And
txtZipBetweenUpper
Text Box
[This is the text box to the right of txtZipBetweenLower, following Label1]
chkRepSelect
Checkbox
Select based on Salesman Number
frameSalesmanOptions
Frame
Salesman Options
optRepBetween
Option Button
Rep Numbers Between:
txtRepBetweenLower
Text Box
[This is the text box to the immediate right of optRepBetween]
Label2
Label
And
txtRepBetweenUpper
Text Box
[This is the text box to the right of txtRepBetweenLower, following Label2]
optRepEqual
Option Button
Rep Number Equal To:
txtRepEqual
Text Box
[Text box to the right of optRepEqual]
optRepList
Option Button
Comma delimited list of rep numbers:
txtRepList
Text Box
[Large text box below optRepList, this text box has it's multiline property set to true.]
cmdMergeToNew
Command Button
Merge to New Document
cmdMergeToPrinter
Command Button
Merge to Printer
cmdCancel
Command Button
Cancel


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

http://www.mcpressonline.com/articles/images/2002/mergetoword3v400.png

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:

Public SQLCmdText As String
Public bCancelMerge As Boolean
Public sMergeTarget As String

Sub GetZipandMerge()
Dim sZipCode As String
Dim bZipError As Boolean

bCancelMerge = False
frmCustSelect.Show vbModal

If bCancelMerge Then Exit Sub

On Error GoTo errorMain

ActiveDocument.MailMerge.DataSource.QueryString = SQLCmdText
    

With ActiveDocument.MailMerge
    Select Case sMergeTarget
           Case "DOCUMENT"
                .Destination = wdSendToNewDocument
           Case "PRINTER"
                .Destination = wdSendToPrinter
    End Select
    .Execute
End With

Exit Sub

errorMain:
  MsgBox Err.Description & "  Perhaps there aren't any matching records?", vbOKOnly

End Sub

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.

Option Explicit
Const sDialogTitle As String = "AutoMerge"
Dim arRepList() As String

Private Function formValid() As Boolean
Dim lIDX As Long
Dim bUseAnd As Boolean

formValid = True

SQLCmdText = "SELECT RCML01.CNME, RCML01.CAD1, " _
           & "RCML01.CAD2, RCML01.CAD3, " _
           & "RCML01.CSTE, RCML01.CZIP, RCML01.CSAL " _
           & "FROM ANTIGUA1.BPCS405CDF.RCML01 RCML01 " _
           & "WHERE "

bUseAnd = False

If Not chkZipCodes And Not chkRepSelect Then
   MsgBox "Please choose selection by zip code, selection by salesman, or both.", _
          vbOKOnly, sDialogTitle
   formValid = False
   Exit Function
End If

If chkZipCodes Then
   If Not optZIPStartsWith And Not optZIPBetween Then
      MsgBox "You have selected zip codes as a selection criteria. " _
           & "Please select either zip codes starting with or zip codes " _
           & "between and provide those values.", vbOKOnly, sDialogTitle
      formValid = False
      Exit Function
    End If
    
    If optZIPStartsWith Then
       If Not IsNumeric(txtZipStart) Then
          MsgBox "Zip code must be a number.  Please enter the first few " _
               & "characters of a valid zip code.", vbOKOnly, sDialogTitle
          formValid = False
          txtZipStart.SetFocus
          Exit Function
       End If
       If bUseAnd Then
          SQLCmdText = SQLCmdText & " AND "
       Else
          bUseAnd = True
       End If
       SQLCmdText = SQLCmdText & "RCML01.CZIP LIKE '" & txtZipStart & "%'"
    
    End If
    
    If optZIPBetween Then
       If Not IsNumeric(txtZipBetweenLower) Then
          MsgBox "Lower zip code of range must be a number.  Please enter " _
               & "a valid lower zip code.", vbOKOnly, sDialogTitle
          formValid = False
          txtZipBetweenLower.SetFocus
          Exit Function
       End If
       
       If Not IsNumeric(txtZipBetweenUpper) Then
          MsgBox "Upper zip code of range must be a number.  Please enter " _
               & "a valid upper zip code.", vbOKOnly, sDialogTitle
          formValid = False
          txtZipBetweenUpper.SetFocus
          Exit Function
       End If
       
       If Len(txtZipBetweenLower) < 5 Then
          MsgBox "Lower zip code of range must be at least 5 characters. " _
               & "Please enter a valid lower zip code.", vbOKOnly, sDialogTitle
          formValid = False
          txtZipBetweenLower.SetFocus
          Exit Function
       End If
       
       If Len(txtZipBetweenUpper) < 5 Then
          MsgBox "Upper zip code of range must be at least 5 characters. " _
               & "Please enter a valid upper zip code.", vbOKOnly, sDialogTitle
          formValid = False
          txtZipBetweenUpper.SetFocus
          Exit Function
       End If
       
       If CLng(txtZipBetweenUpper) < CLng(txtZipBetweenLower) Then
          MsgBox "Lower zip code of range must be less than upper " _
               & "zip code of range.  Please make an adjustment.", _
               vbOKOnly, sDialogTitle
          formValid = False
          txtZipBetweenLower.SetFocus
          Exit Function
       End If
       If bUseAnd Then
          SQLCmdText = SQLCmdText & " AND "
       Else
          bUseAnd = True
       End If
       SQLCmdText = SQLCmdText & "RCML01.CZIP BETWEEN '" & txtZipBetweenLower _
                  & "' AND '" & txtZipBetweenUpper & "'"
    End If
End If


If chkRepSelect Then
   If Not optRepBetween And Not optRepEqual And Not optRepList Then
      MsgBox "You have selected salesman number as a selection criteria. " _
           & "Please select either rep number range, rep number, or a rep " _
           & "number list. ", vbOKOnly, sDialogTitle
      formValid = False
      Exit Function
    End If
    
    If optRepBetween Then
       If Not IsNumeric(txtRepBetweenLower) Then
          MsgBox "Lower rep number of range must be a number.  Please enter " _
               & "a valid lower rep number.", vbOKOnly, sDialogTitle
          formValid = False
          txtRepBetweenLower.SetFocus
          Exit Function
       End If
       
       If Not IsNumeric(txtRepBetweenUpper) Then
          MsgBox "Upper rep number of range must be a number.  Please enter " _
               & "a valid upper rep number.", vbOKOnly, sDialogTitle
          formValid = False
          txtRepBetweenUpper.SetFocus
          Exit Function
       End If
       
       If CLng(txtRepBetweenUpper) < CLng(txtRepBetweenLower) Then
          MsgBox "Lower rep number of range must be less than upper " _
               & "rep number of range.  Please make an adjustment.", _
               vbOKOnly, sDialogTitle
          formValid = False
          txtRepBetweenLower.SetFocus
          Exit Function
       End If
    
       If bUseAnd Then
          SQLCmdText = SQLCmdText & " AND "
       Else
          bUseAnd = True
       End If
       SQLCmdText = SQLCmdText & "RCML01.CSAL BETWEEN " & txtRepBetweenLower _
                  & " AND " & txtRepBetweenUpper
    End If
    
    If optRepEqual Then
       If Not IsNumeric(txtRepEqual) Then
          MsgBox "Rep number must be a number.  Please enter a valid rep " _
               & "number.", vbOKOnly, sDialogTitle
          formValid = False
          txtRepEqual.SetFocus
          Exit Function
       
       End If
       If bUseAnd Then
          SQLCmdText = SQLCmdText & " AND "
       Else
          bUseAnd = True
       End If
       SQLCmdText = SQLCmdText & "RCML01.CSAL = " & txtRepEqual
    
    End If
    
    If optRepList Then
       Erase arRepList
       arRepList = Split(txtRepList, ",")
       For lIDX = LBound(arRepList) To UBound(arRepList)
           If Not IsNumeric(arRepList(lIDX)) Then
              MsgBox "Rep numbers in the list must be numeric.  Please correct " _
                   & "your list.", vbOKOnly, sDialogTitle
              formValid = False
              txtRepList.SetFocus
              Exit Function
           End If
       Next
       
       If bUseAnd Then
          SQLCmdText = SQLCmdText & " AND "
       Else
          bUseAnd = True
       End If
       SQLCmdText = SQLCmdText & "("
       For lIDX = LBound(arRepList) To UBound(arRepList) - 1
           SQLCmdText = SQLCmdText & "RCML01.CSAL = " & arRepList(lIDX) & " OR "
       Next
       SQLCmdText = SQLCmdText & "RCML01.CSAL = " & arRepList(lIDX) & ")"
    
    End If
     
End If

End Function

Private Sub cmdCancel_Click()

bCancelMerge = True
Unload Me

End Sub

Private Sub cmdMergeToNew_Click()

If Not formValid() Then Exit Sub

sMergeTarget = "DOCUMENT"
Unload Me

End Sub

Private Sub cmdMergeToPrinter_Click()

If Not formValid() Then Exit Sub
sMergeTarget = "PRINTER"
Unload Me

End Sub

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.

Private Sub chkZipCodes_Click()

If Not chkZipCodes Then
   optZIPBetween = False
   optZIPStartsWith = False
   txtZipBetweenLower = ""
   txtZipBetweenUpper = ""
   txtZipStart = ""
End If

End Sub
Private Sub optRepBetween_Click()

If optRepBetween Then
   txtRepEqual = ""
   txtRepList = ""
End If

End Sub

Private Sub optRepEqual_Click()

If optRepEqual Then
   txtRepBetweenLower = ""
   txtRepBetweenUpper = ""
   txtRepList = ""
End If

End Sub

Private Sub optRepList_Click()

If optRepList Then
   txtRepBetweenLower = ""
   txtRepBetweenUpper = ""
   txtRepEqual = ""
End If

End Sub

Private Sub optZIPBetween_Click()

If optZIPBetween Then
   txtZipStart = ""
End If

End Sub

Private Sub optZIPStartsWith_Click()

If optZIPStartsWith Then
   txtZipBetweenLower = ""
   txtZipBetweenUpper = ""
End If

End Sub

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

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$0.00 Raised:
$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: