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.
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
More 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.
TRY 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.
Forms 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.
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:
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.
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:
LATEST COMMENTS
MC Press Online