Counting Formfields through a range. It did work, but now. . .

[Mizpah]

Hi,

This is quite long, so sorry in advance. I just wanted to explain as
thoroughly as I could.

I used the following code in this form and it did work for awhile. This
was my first attempt at setting a range and only working with the
formfields it that range. I don't know if this is the best most
efficient way of doing it, but it worked until I started playing with
If - Then statements for protecting and unprotecting the form &
inserting code to ensure these formfields within this range were indeed
enabled.

This is what I started with:

Dim myRange As Range

'range of usually 4 non-record formfields which are: "bkCEntity, bkDBA,
bkStateI, bkDateI

Set myRange = ActiveDocument.Range( _
Start:=ActiveDocument.FormFields("bkCEntity").Range.Start, _
End:=ActiveDocument.FormFields("bkDateI").Range.End)

With myRange
Dim intFF, intCountA As Integer
intFF = .FormFields.Count
For intCountA = 1 To intFF
If .FormFields(intCountA).Range.Font.Hidden = False
Then
.FormFields(intCountA).Range.Font.Hidden = True
End If
Next intCountA
End With

This range usually has only 4 formfields - which I mentioned above, and
intFF was returning 4 like it should.

The whole reason I am doing this is because the user uses the word
template to either be populated via an Access database with the
corresponding client and then prints it off for production. Or the user
can use the same template to edit client records - update, new, or
delete - directly. When the user enters edit mode, there are 4
formfields that would not be used in the database, they are for
production purposes only. The user has a problem with the gray shaded
formfields being there because they tab through the documents 40 or so
formfields to change the record data. Since the user doesn't really
look at where the cursor goes after tabbing through, the wrong
formfields gets updated. So I am trying to hide completely the
non-record ones during editing so this doesn't happen.

Because there are different toolbar buttons & userform buttons that the
user uses for the various tasks of editing I have to unprotect or
protect the document to help. This is the code I use:

Sub Recall()
If ActiveDocument.ProtectionType = wdAllowOnlyFormFields Then
ActiveDocument.Unprotect
End If
UserForm2.Show
End Sub

or

'Lock form for editing
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect wdAllowOnlyFormFields
End If

But then I noticed that when the form was protected. The 4 non-record
formfields where no longer hidden, but they were not usable. I couldn't
click on then. So then I added some code to ensure they were enabled. I
didn't do anything conditional statements or any loops since this time
because I wasn't sure if enabling them would work at the time. In the
end the whole section of code looked like this:

UserForm2.Hide

With myRange
Dim intFF, intCountA As Integer
intFF = .FormFields.Count
For intCountA = 1 To intFF
If .FormFields(intCountA).Range.Font.Hidden = False
Then
.FormFields(intCountA).Range.Font.Hidden = True
End If
Next intCountA
End With

MsgBox "This form can now be edited." & vbCrLf & _
"Information can only be entered into the grey fields." &
vbCrLf & _
"Any data entered on this form will be added to the
database.", vbInformation, "Create New Record"

Unload Me
End If

'Lock form for editing
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect wdAllowOnlyFormFields
End If

With ActiveDocument
.FormFields("bkCEntity").Enabled = True
.FormFields("bkDBA").Enabled = True
.FormFields("bkStateI").Enabled = True
.FormFields("bkDateI").Enabled = True
End With

But this time intFF is now returning 8 instead of 4 and now I am
getting the error: Run-time error '5941 The requested member of the
collection doesn't exist. So why does intFF return 8 now instead of 4?
I have replaced intFF with 4 in the code and it works just fine. But I
am curious as to what I did wrong, or could do different.

Thanks in advance.

 

[Mizpah]

I tried this on Word 2003, using your code, from the beginning down to the
"lock for editing" part, and didn't have the problem you describe. Every time
I tested, after protecting the document the form fields were still hidden and
they were enabled. There must be something else going on, like what happens
with userform2?

Thanks, I realized after I posted yesterday that I probably should have
included the first half of the code because of the beginning of the If
.. . . . Then. . . . statements. Here is the whole code for Userform2.
Userform 2 is nothing more than a menu that include a combobox that is
populated with clients in the database, and a text box. The problem I
am having is when the user chooses "Create New Record" in the combo
box. BTW I am using Word 2000 on Win XP.

Option Explicit
Dim datConnection As New ADODB.Connection
Dim datRS As New ADODB.Recordset
Dim myRange As Range

Private Sub cmdCancel_Click()

Unload Me
ActiveDocument.FormFields.Shaded = True
End
End Sub

Private Sub cmdClear_Click()
'On Error Resume Next
Dim intMax As Integer
Dim intCount As Integer

intMax = ActiveDocument.Bookmarks.Count
For intCount = 1 To intMax
ActiveDocument.FormFields(intCount).Result = ""
Next intCount

ActiveDocument.FormFields.Shaded = True
End
End Sub

Private Sub cmdEdit_Click()

'range of 4 non-record formfields which are: "bkCEntity, bkDBA,
bkStateI, bkDateI
Set myRange = ActiveDocument.Range( _
Start:=ActiveDocument.FormFields("bkCEntity").Range.Start, _
End:=ActiveDocument.FormFields("bkDateI").Range.End)

With myRange
Dim intFF, intCountA As Integer
intFF = .FormFields.Count
For intCountA = 1 To intFF
If .FormFields(intCountA).Range.Font.Hidden = False
Then
.FormFields(intCountA).Range.Font.Hidden = True
End If
Next intCountA
End With

Unload Me
frmEdit.Show
End Sub

Private Sub cmdOK_Click()
Dim strService As String
Dim strSQL As String

'range of 4 non-record formfields which are: "bkCEntity, bkDBA,
bkStateI, bkDateI
Set myRange = ActiveDocument.Range( _
Start:=ActiveDocument.FormFields("bkCEntity").Range.Start, _
End:=ActiveDocument.FormFields("bkDateI").Range.End)

If cboService.ListIndex = 0 Then 'A check to see if the user
wants to create a new record

Dim intText, intCount, intMax As Integer 'A check to see if
existing data already exists
intMax = ActiveDocument.Bookmarks.Count
For intCount = 1 To intMax
intText = intText +
Len(ActiveDocument.FormFields(intCount).Result)
Next intCount

If intText <> 0 Then 'If some sort of text was found then
alert user so some sort of undesired screw-up doesn't happen
Dim intResponse As Integer
intResponse = MsgBox("There is data already on this form.
Do you want to keep it?", 36, "Existing Data Found")
If intResponse = 7 Then 'User doesn't want data so it
needs to be cleared
For intCount = 1 To intMax
ActiveDocument.FormFields(intCount).Result = ""
Next intCount
End If
End If

If ActiveDocument.FormFields.Shaded = False Then 'Ensure the
formfields are shaded so the user can see them
ActiveDocument.FormFields.Shaded = True
End If

UserForm2.Hide

With myRange
Dim intFF, intCountA As Integer
intFF = .FormFields.Count
For intCountA = 1 To intFF
If .FormFields(intCountA).Range.Font.Hidden = False
Then
.FormFields(intCountA).Range.Font.Hidden = True
End If
Next intCountA
End With

'MsgBox "The count is " & intff & ".", vbInformation
'Lock form for editing

If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect wdAllowOnlyFormFields
End If

MsgBox "This form can now be edited." & vbCrLf & _
"Information can only be entered into the grey fields." &
vbCrLf & _
"Any data entered on this form will be added to the
database.", vbInformation, "Create New Record"

Unload Me
End
End If

'Lock form for editing
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect wdAllowOnlyFormFields
End If

With ActiveDocument
.FormFields("bkCEntity").Enabled = True
.FormFields("bkDBA").Enabled = True
.FormFields("bkStateI").Enabled = True
.FormFields("bkDateI").Enabled = True
End With

'Connect to the database if list index is not 0
datConnection.ConnectionString = "data
source=C:\datastores\clients.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
datConnection.Open
'Find selected client
strService = cboService.Text
'places listname on document in hidden text field so other modules
can access this variable.
ActiveDocument.FormFields("bkListName").Result = strService

strSQL = "SELECT * FROM Main WHERE Main!ListName = '" & strService
& "';"
datRS.Open strSQL, datConnection, adOpenKeyset, adLockOptimistic

Call FillForm

datRS.Close
datConnection.Close

Set datRS = Nothing
Set datConnection = Nothing

Unload Me
'If user is using form for production then fill non-record fields
ActiveDocument.FormFields("bkCEntity").Result = txtCEntity.Text
ActiveDocument.ActiveWindow.View.Zoom.PageFit = wdPageFitBestFit
End
End Sub

Private Sub cmdPinfo_Click()

Pinfo.Show
End Sub

Private Sub UserForm_Initialize()
Dim strSQL As String

datConnection.ConnectionString = "data
source=C:\datastores\clients.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
datConnection.Open

strSQL = "SELECT ListName FROM Main ORDER BY ListName;"
datRS.Open strSQL, datConnection, adOpenKeyset, adLockOptimistic

If Not datRS.EOF Then
cboService.AddItem "Create New Record?"
Do Until datRS.EOF
cboService.AddItem datRS!ListName
datRS.MoveNext
Loop
cboService.ListIndex = 0
End If

datRS.Close
datConnection.Close

Set datRS = Nothing
Set datConnection = Nothing

'range of 4 non-record formfields which are: "bkCEntity, bkDBA,
bkStateI, bkDateI
Set myRange = ActiveDocument.Range( _
Start:=ActiveDocument.FormFields("bkCEntity").Range.Start, _
End:=ActiveDocument.FormFields("bkDateI").Range.End)

With myRange
Dim intFF, intCountA As Integer
intFF = .FormFields.Count
For intCountA = 1 To intFF
If .FormFields(intCountA).Range.Font.Hidden = True Then
.FormFields(intCountA).Range.Font.Hidden = False
End If
.FormFields(intCountA).Enabled = True
Next intCountA
End With

End Sub

Public Sub FillForm()
On Error Resume Next
With ActiveDocument
.FormFields("bkCName").Result = datRS!Client
.FormFields("bkAttn").Result = datRS!Attn
.FormFields("bkCStreet").Result = datRS!Street
.FormFields("bkcCity").Result = datRS!City
.FormFields("bkCST").Result = datRS!ST
.FormFields("bkCZip").Result = datRS!Zip
.FormFields("bkCTele").Result = datRS!Tele
.FormFields("bkCFax").Result = datRS!Fax
.FormFields("bkCEmail").Result = datRS!Email
.FormFields("bkAccount").Result = datRS!CAccount
.FormFields("bkARIAccount").Result = datRS!ARIAccount

.FormFields("bkAttnA").Result = datRS!AttnA
.FormFields("bkCoA").Result = datRS!ClientA
.FormFields("bkStreetA").Result = datRS!StreetA
.FormFields("bkcityA").Result = datRS!CityA
.FormFields("bkSTA").Result = datRS!STA
.FormFields("bkZipA").Result = datRS!ZipA
.FormFields("bkTeleA").Result = datRS!TeleA
.FormFields("bkFaxA").Result = datRS!FaxA
.FormFields("bkCEmailA").Result = datRS!EmailA

.FormFields("bkAttnB").Result = datRS!AttnB
.FormFields("bkCoB").Result = datRS!ClientB
.FormFields("bkStreetB").Result = datRS!StreetB
.FormFields("bkcityB").Result = datRS!CityB
.FormFields("bkSTB").Result = datRS!STB
.FormFields("bkZipB").Result = datRS!ZipB
.FormFields("bkTeleB").Result = datRS!TeleB
.FormFields("bkFaxB").Result = datRS!FaxB

.FormFields("bkAttnC").Result = datRS!AttnC
.FormFields("bkCoC").Result = datRS!ClientC
.FormFields("bkStreetC").Result = datRS!StreetC
.FormFields("bkcityC").Result = datRS!CityC
.FormFields("bkSTC").Result = datRS!STC
.FormFields("bkZipC").Result = datRS!ZipC
.FormFields("bkTeleC").Result = datRS!TeleC
.FormFields("bkFaxC").Result = datRS!FaxC
End With
End Sub