Cascading 3 fields in word form

[MKMeshref]

I have seen the code written by fumei for the following problem:
"In Word, Create form fields that, when one is selected, the options under the second one change. Also called cascading dropdowns.
In more detail:
Suppose you have a form you've created using the Forms toolbar. In it you have a dropdown for each department in your company. But once the person using the form chooses the department, you want them to choose a specific supervisor in that department. That means that the values showing in the second dropdown box are dependent on the value chosen in the first dropdown box. You CAN do it, and here's how. "

My question is: How can we cascade 3 fields. I mean after selecting an item from the second field how can we show a specific list for that item in the third field? How can we modify the code of fumei to be applied on 3 fields and not just 2 fields?

MK Meshref

[fumei]

You use the same type of logic as from the first to second, for the second to third.

#1 fruit, meat, cars

if #1 = fruit then #2 = apples, oranges, pears
if #1 = meat then #2 = chicken, beef, goat
if #1 = cars then #2 = ferrari, corvair, ford

if #2 = apples then #3 = macintosh, spartan, delicious
if #2 = oranges then #3 = florida, barcelona, mandarin
if #2 = pears then #3 = anjou, bartlett
if #2 = chicken then #3 = roasted, fried, raw
if #2 = beef then #3 = kobe, kabob, steak
if #2 = goat then #3 = billy, mama, greek
if #2 = ferrari then #3 = genoa, turin, way too fast
etc. etc.

These would be determined by the exit code for the #2 formfield.

As an possible alternative, perhaps a global variable that is set from #1 and reset by #2. A test of it could be used to set up the items in #3.

There are other ways it could be done.

[fumei]

I do want to add that the knowledge base article has four items per formfield. The number is the SAME - i.e. four. Four fruit, four veggies etc.

If there are different numbers - four fruit, three veggies - then it gets a bit trickier.

[MKMeshref]

Many thanks fumei.
1. As you have done in the 2 field cascading, would you please send me the complete code of the 3 field cascading.
2. Instead of hard coding the contents of lists within the code, how can we make a simple way, for the normal user, to update such lists without going to dig into the code?

[fumei]

#2 Please explain the circumstances that you want users to update lists. To do so will STILL require "digging" into the code. After all, the process uses code. It would require getting - via MORE code - the inputs from the users. And it would require checking EVERY TIME to see if they want to add/change the items. I am not saying it is not possible but it would be significantly more complicated.

Where would you want to do that checking/asking? When the document opens? When the user clicks some button? When they enter into the first formffield? After the exit the first, but change the second? After they exit the second and enter the third?

What happens if they do NOT exit the first (or second) but click directly into the second (or third). Then what do you want to happen? Do you start with default items?

The point being, if you allow user modification (as is the case for all user modifications), you need to plan out EXACTLY what happens, including error checking to make sure the user does not screw something up. After of course carefully figuring out what defines a screw up, and what would be the response to it.


#1 I will try to get around to it as soon as I can. Logically though, you should be able to do it yourself. It is exactly the same as the code you have, just with more elements. If you actually understand what is happening with 2 formfield, you should be able to make it for three.

[fumei]

I really think you should make an attempt yourself. If you get into trouble I can help.

[fumei]

Just to help you get started, here is a small portion of what you will need to do. This is based on the document in the knowledge base article. The first FF (formfield) is named DropDown1, the second Result, and now I added the third Result3. BTW: if would be easier for the exercise if the dropdowns were named consecutively Dropdown1, 2, and 3...but there ya go. Good luck.[vba]Sub SecondFieldExit()
Dim ChoseApples()
Dim ChosePeaches()
Dim ChosePears()
Dim ChoseBananas()
Dim ChoseGreenBeans()
Dim ChoseCorn()
Dim ChoseLettuce()
Dim ChoseSquash()
Dim ChoseBeef()
Dim ChoseChicken()
Dim ChosePork()
Dim ChoseVeal()
ChoseApples() = Split("macintosh,spartan,delicious", ",")
ChosePeaches() = Split("white,sungold,rotten", ",")
ChosePears() = Split("anjou,bartlett,green", ",")
ChoseBananas() = Split("mcgregor,ripe,plantain", ",")

ChoseGreenBeans() = Split("french,trimmed,raw", ",")
ChoseCorn() = Split("blue,spartan,delicious", ",")
' etc - all the rest of the 12 possible choices in second FF


' test the FIRST FF
Select Case ActiveDocument.FormFields("Dropdown1").DropDown.Value
Case 1 ' this MEANS second FF (Result) = apples, peaches, pears, bananas
' with that NOW test against the SECOND FF
Select Case ActiveDocument.FormFields("Result").DropDown.Value
Case 1 ' apples selected
ActiveDocument.FormFields("Result3").DropDown _
.ListEntries.Clear
For var = 1 To 3
ActiveDocument.FormFields("Result3").DropDown _
.ListEntries.Add Name:=ChoseApples(i)
i = i + 1
Next
Case 2 ' peaches selected
ActiveDocument.FormFields("Result3").DropDown _
.ListEntries.Clear
For var = 1 To 3
ActiveDocument.FormFields("Result3").DropDown _
.ListEntries.Add Name:=ChosePeaches(i)
i = i + 1
Next
Case 3 ' pears selected
ActiveDocument.FormFields("Result3").DropDown _
.ListEntries.Clear
For var = 1 To 3
ActiveDocument.FormFields("Result3").DropDown _
.ListEntries.Add Name:=ChosePears(i)
i = i + 1
Next
Case 4
ActiveDocument.FormFields("Result3").DropDown _
.ListEntries.Clear
For var = 1 To 3
ActiveDocument.FormFields("Result3").DropDown _
.ListEntries.Add Name:=ChoseBananas(i)
i = i + 1
Next
ActiveDocument.FormFields("Result").DropDown.Value = 1
End Select
Case 2 ' this MEANS the second FF (Result) are green beans, corn, lettuce, squash
' with that NOW test against the SECOND FF
Select Case ActiveDocument.FormFields("Result").DropDown.Value
Case 1 ' green beans selected
ActiveDocument.FormFields("Result").DropDown _
.ListEntries.Clear
For var = 1 To 3
ActiveDocument.FormFields("Result").DropDown _
.ListEntries.Add Name:=ChooseGreenBeans(i)
i = i + 1
Next
Case 2 ' corn selected
ActiveDocument.FormFields("Result").DropDown _
.ListEntries.Clear
For var = 1 To 3
ActiveDocument.FormFields("Result").DropDown _
.ListEntries.Add Name:=ChooseCorn(i)
i = i + 1
Next
'..... the rest of Case 2 Veggies selected in FF 1
Case 3
Case 4
End Select
Case 3 ' Meat selected in FF 1
Select Case ActiveDocument.FormFields("Result").DropDown.Value
Case 1
.....
Case 2
.....
Case 3
.....
Case 4
.....
End Select
End Select

ActiveDocument.FormFields("Result").DropDown.Value = 1
End Select[/vba]

Essentially the logic, while perhaps tedious, is simple.

You have three choices in FF1.

You have twelve choices in the second FF.

[MKMeshref]

many thanks Fumei.

I'll try it on my specific case.

Thanks again

[MKMeshref]

I have 3 fields
1. First one for Department
2. Second for Name of employee
3. Third for Position.

I have a VBA code as follows, but when I select the department and press Tab, going to the next field, it works all right and I find the names of this department. Selecting a name and pressing Tab to go to the third field, it converts the second field to BLANK. Please advise.

Here is the code:

Option Explicit
Sub FirstFieldExit()

Dim PMD() As String
Dim IT() As String
Dim Admin() As String
Dim i As Integer
Dim var As Integer
' ReDim PMD(3)
' ReDim IT(2)
' ReDim Admin(2)

' Splitting the alternatives and putting them in array elements
PMD() = Split("Mohamed Meshref,Mostafa Al Sagier,Amal Zaher,Hadeer Mohamed", ",")
IT() = Split("M. Abd AlRahman,Marwan Awad,Reham Nabil", ",")
Admin() = Split("Gamal Nafe3,Adel Abdo,Saied Ibrahim", ",")


'Use the value of the dropdown to case select condition

Select Case ActiveDocument.FormFields("Departments").DropDown.Value

'For each one of these cases, change the " .Add Name:= part to be
'one of the options in your #1 dropdown box

Case 1
ActiveDocument.FormFields("Staff").DropDown.ListEntries.Clear
For var = 1 To 4
ActiveDocument.FormFields("Staff").DropDown.ListEntries.Add Name:=PMD(i)
i = i + 1
Next
' ActiveDocument.FormFields("Staff").DropDown.Value = 1
Case 2
ActiveDocument.FormFields("Staff").DropDown.ListEntries.Clear
For var = 1 To 3
ActiveDocument.FormFields("Staff").DropDown.ListEntries.Add Name:=IT(i)
i = i + 1
Next
'ActiveDocument.FormFields("Staff").DropDown.Value = 1
Case 3
ActiveDocument.FormFields("Staff").DropDown.ListEntries.Clear
For var = 1 To 3
ActiveDocument.FormFields("Staff").DropDown.ListEntries.Add Name:=Admin(i)
i = i + 1
Next
'ActiveDocument.FormFields("Staff").DropDown.Value = 1
End Select
End Sub

[fumei]

It would help ifyou posted the actual code for the formfield that goes blank. Also, please use the VBA code tags. Thanks.

[MKMeshref]

It would help ifyou posted the actual code for the formfield that goes blank. Also, please use the VBA code tags. Thanks.

=================================
I have already posted the actual code. Here is it again:
Option Explicit
Sub FirstFieldExit()

Dim PMD() As String
Dim IT() As String
Dim Admin() As String
Dim i As Integer
Dim var As Integer
' ReDim PMD(3)
' ReDim IT(2)
' ReDim Admin(2)

' Splitting the alternatives and putting them in array elements
PMD() = Split("Mohamed Meshref,Mostafa Al Sagier,Amal Zaher,Hadeer Mohamed", ",")
IT() = Split("M. Abd AlRahman,Marwan Awad,Reham Nabil", ",")
Admin() = Split("Gamal Nafe3,Adel Abdo,Saied Ibrahim", ",")


'Use the value of the dropdown to case select condition

Select Case ActiveDocument.FormFields("Departments").DropDown.Value
'ActiveDocument.FormFields("Staff").DropDown.ListEntries.Clear

'For each one of these cases, change the " .Add Name:= part to be
'one of the options in your #1 dropdown box

Case 1
ActiveDocument.FormFields("Staff").DropDown.ListEntries.Clear
For var = 1 To 4
ActiveDocument.FormFields("Staff").DropDown.ListEntries.Add Name:=PMD(i)
i = i + 1
Next
' ActiveDocument.FormFields("Staff").DropDown.Value = 1
Case 2
ActiveDocument.FormFields("Staff").DropDown.ListEntries.Clear
For var = 1 To 3
ActiveDocument.FormFields("Staff").DropDown.ListEntries.Add Name:=IT(i)
i = i + 1
Next
'ActiveDocument.FormFields("Staff").DropDown.Value = 1
Case 3
ActiveDocument.FormFields("Staff").DropDown.ListEntries.Clear
For var = 1 To 3
ActiveDocument.FormFields("Staff").DropDown.ListEntries.Add Name:=Admin(i)
i = i + 1
Next
'ActiveDocument.FormFields("Staff").DropDown.Value = 1
End Select
End Sub

[MKMeshref]

I found that it was Word problem. Nothing to do with the VBA code. The 3 fields were on the top of a table. The remaining fields were following on the same table. When I separated the first 3 fields in a separate table and inserted a section break, it solved the problem. Why and How? God knows. May be Microsoft can explain.

[fumei]

Actually I asked fo the code for the SECOND formfield...which you did not post. You stated the second went blank:

"converts the second field to BLANK"

Again, the code you posted is for the FIRST formfield.

[MKMeshref]

For this specific problem, I am using a code for only the first field. Going from the second field to the third one, which has no code, it gets the choice of the second field into blank. I have solved this problem as I have stated in my quote here above.
Thanks Fumei

[MKMeshref]

For this specific problem, I am using a code for only the first field. Going from the second field to the third one, which has no code, it gets the choice of the second field into blank. I have solved this problem as I have stated in my quote here above.
Thanks Fumei

May be putting this problem here, in the "cascading 3 fields in Word", is getting confusing. But this is another problem. I'm coding only the first field, while the other fields are normal Word fields.

Thank you
Mohamed Meshref

[fumei]

I know I am confused. Here is your original question.

"How can we cascade 3 fields. I mean after selecting an item from the second field how can we show a specific list for that item in the third field?"

I showed how with code for the SECOND formfield.

'For this specific problem, I am using a code for only the first field. Going from the second field to the third one, which has no code, it gets the choice of the second field into blank. I have solved this problem as I have stated in my quote here above"

So if this is solved...what are you asking now.