Related Information...
- Read
Dian's beginning AutoForm series of articles: Please
Fill Out This Form, published in Computor
Companion Magazine.
- Check
out TechTrax Ezine,
click Archives and
enter VBA to find many more articles.
- Purchase Dian's AutoForms &
Beginning VBA eBook, which
includes an online version and eBook.
- Learn AutoForms in the best
possible manner—have Dian show you how it's done through
her enhanced video version of
the AutoForms & Beginning VBA course.
Training > Tutorials > Word AutoForms > Code Snippets
On this page...
Code Snippets
I receive many fan letters from readers of my AutoForm articles. I'm happy that I've been able to provide useful information, which so many users appreciate. Along with many of these thank you letters, users occasionally ask for help when they have ideas about how to create their forms, but have trouble implementing their ideas into working code. Although I may not always be able to provide the ultimate answer, I do my best to help them out with solutions.
As time permits, I'll post these custom solutions here. However, also know that MouseTrax.com sponsors two VBA support groups that you can join, free. Get the help you need, 24x7. See our Resources page for details.
Problem: A user has a survey form, with dropdown fields, that allows the user to select a rating number for each question. At the end of the form, she wanted to display the total, as well as an average, of their answers. But she was having trouble figuring out how to add the answers, automatically insert the total, and then get and insert the average to provide the user with a calculated final answer.
Solution: The main issue here is that the text selected in a dropdown box is considered a string (text). In order to use a math calculation to add the answers, the code needs to realize it must treat these strings as numbers, integers. Then the numbers can be added together and also averaged through their variables. However, should the user decide to tab their way through another round of the form (something they frequently like to do, apparently), we also have to consider the fact that a number may already be sitting in the answer location, generated from the previous round. So we need to check that fact and delete any previous total, should the user have changed their answers in the form, thereby regenerating a new total to place in the answer location.
Here's my code suggestion with commented explanations...
Sub pAddAnswers()
'
first set variables for all the field results
in
the form
' note that you have to use VAL to set the
STRING (Text)
' answer to an INTEGER or number! If you
left it as a string,
' when you'd add...1+1 you would get 11 and not 2.
When you
' set it to a Val, you get 1+1=2
vOne
= Val(ActiveDocument.FormFields("Expect1").Result)
vTwo = Val(ActiveDocument.FormFields("Expect2").Result)
vThree = Val(ActiveDocument.FormFields("Expect3").Result)
vFour = Val(ActiveDocument.FormFields("Expect4").Result)
vFive = Val(ActiveDocument.FormFields("Expect5").Result)
vSix = Val(ActiveDocument.FormFields("Expect6").Result)
vSeven = Val(ActiveDocument.FormFields("Expect7").Result)
vEight = Val(ActiveDocument.FormFields("Expect8").Result)
'
now set a variable equal to the value of
each
of these
' integers, added together
vTotal = vOne + vTwo + vThree + _
vFour
+ vFive + vSix + vSeven + vEight
'
Change the total cell to a bookmark
' versus
a form field as the USER will
' not need to access the number. If you
' leave it as a field, they can enter
' the field and change the number. By using
' a bookmark,
they cannot access
' this number. However, since it's not a
' form field,
it's also locked behind
' the protection of the document. So you
' must QUICKLY
open the document. This
' line below calls the separate procedure
' (see
below for code) that toggles
' the doc protection
Call pToggleProtectDoc
' once unlocked...you zip over to the total bookmark
ActiveDocument.Bookmarks("myTotalBookmark").Select
' assuming the user may go through
' the doc more than
once and there may already
' be an answer in the total...you need
' to delete any
possible previous answer so
' you can replace it with the new number.
' Without the
lines below...you would just
' keep adding new total numbers after the old.
' These
select the line after the bookmark
' and deletes any previous info there
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Selection.Delete
' now you set the bookmark to
' the result of the variable
you created
' when you added the numbers together
ActiveDocument.Bookmarks("myTotalBookmark").
_
Range.Text
= vTotal
' now you can create a new variable
' that is
equal to the
' total divided by 8 (the number of Qs).
' Since you
already converted the individual
' answers to integers, the total variable
' (vTotal)
is already
' an number (not text), so you do not
' need to convert
it again to use math here.
vAverage = vTotal / 8
' now do the same as you did
with the total...
'
select the new bookmark
' (that was a text field) in the average box
ActiveDocument.Bookmarks("myAverageBookmark").Select
' delete any possible, previously entered average number
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Selection.Delete
' and
set the bookmark result to
' the result of the
average variable
ActiveDocument.Bookmarks("myAverageBookmark").Range.Text = vAverage
' now
call the toggle procedure again
'
so you can
relock the form so users
' can cycle through it again, if
'
necessary, to change
their answers
Call pToggleProtectDoc
End Sub
Sub pToggleProtectDoc()
' this
code toggles the form protection.
'
If it's on, it
turns it off.
' If it's off, it turns it on. But
'
rather than using
the menu option,
' this code relocks the form without
'
clearing
the form fields (.NoReset),
' as they normally would if you
'
clicked protect manually
while testing!
' first
I declare the word DOC
'
to represent the active
doc cos'
' I'm just too lazy to keep typing
'
ActiveDocument
and check for
' form protection
Set Doc = ActiveDocument
If Doc.ProtectionType = wdNoProtection Then
' if
the form is password protected, then
' you also need to add the argument below
' Password:="myPasswordName",
Doc.Protect NoReset:=True, Type:=wdAllowOnlyFormFields
Else
Doc.Unprotect
' remember
to add the password argument
'
in the above line,
too, if there is one.
End If
End Sub
Filling
a DropDown on the Fly
Note! See article
# 5 for further DataBase/Form Connectivity
details.
Problem: A user, who was thrilled to finally learn how to connect a form to a database, was zipping along on a form for his wife's business, when he hit a snag trying to figure out how to fill a form dropdown box on the fly—pulling current data from a connected database. Although a ListBox is generally better for this, since the DropDown is limited to 25 entries, he assured me that it would work because he would never have more than 25 names for this particular field.
Solution: The user was attempting to put the data into an array that would populate the dropdown, but was having trouble making it work. My solution was to simply Loop through the data and have it use the AddName method to fill the entries. However, again, should the user cycle through the form a second time, a second set of entries would be added. So first we needed to always verify that the dropdown was empty and then populate it. I also added a testing Message Box to display the selection so he could test it and would also know how to retrieve the answer, should he want to use that as a variable elsewhere in his form. The macro to populate the field would be put in the Entry field of the dropdown, to be activated when the user enters the field. The testing message box macro is placed in the Exit field of the dropdown, to retrieve whatever the user selected.
Here's my code suggestion with commented explanations...
Sub pFillDropDown()
'declare variables for new connection and recordset
Dim vConnection as New ADODB.Connection
Dim vRecordSet as New ADODB.Recordset
'provide connection string for data using Jet Provider for Access database
vConnection.ConnectionString
= "data source=c:\myDataFolder\myDatabase.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
'open connection
vConnection.Open
'open
a RecordSet with all
'
records via an SQL query
vRecordSet.Open "SELECT * FROM
ClientInfo",
_
vConnection, adOpenKeyset, adLockOptimistic
' make
sure dropdown is empty of
'
entries, should user
cycle
' through form a second time
ActiveDocument.FormField("myDropDown").
_
DropDown.ListEntries.Clear
' if
there are no null entries...
' add records to the
dropdown
Do Until vRecordSet.EOF
If vRecordSet!LName <> "" Then
With ActiveDocument.FormFields _
("myDropDown").DropDown.ListEntries
.Add Name:=vRecordSet!Lname
End With
End If
vRecordSet.MoveNext Loop
'close objects
vRecordSet.Close
vConnection.Close
'clear objects to free up memory
Set vRecordSet = Nothing
Set vConnection = Nothing
End Sub
Sub pDisplayDropDownChoice()
' a
simple message box to display
'
the user choice for
testing verification
MsgBox
ActiveDocument.FormFields _
("myDropDown").Result
End Sub