Cascading Dropdowns

[dattagal]

Hi folks, I've been "nosing around" on this forum for a couple of days and have found a lot of interesting "stuff" but can't seem to find the answer to my specific problem so I will now post.

:banghead:

I have a Word form (Office 2000) on which I have 3 dropdowns:

a list of group offices [GODD] (for Group Office Drop Down)
a list of sales reps [SRDD]
a list of account assistants [AADD]I used (or tried to use) code I found from fumei (thank you fumei) but I keep getting the error message "data member not found".

I use VBA extensively in Access (well I use basically the same type of code modified extensively (mostly If -then statements)) but I cannot for the life of me get this Word VBA to work for me. (have never used a case statement before).

Anyway, I want the selection from the group office drop down (which for simplicity for now only has Atlanta, Chicago, Denver and San Francisco) to determine what names show up in the sales reps and account assistants drop downs.

Here is my code (probably a silly error on my part - I just have never been able to wrap my brain around a case statement).

Your assistance would be greatly appreciated!!

Thank you.

Sub FirstFieldExit()

Dim Atlanta(3) As String
Dim Chicago(5) As String
Dim Denver(2) As String
Dim i As Integer
Dim var

'Create your multiple lists here (dropdown #2)

Atlanta(0) = "Betty Boop"
Atlanta(1) = "Sponge Bob"
Atlanta(2) = "Katy Did"
Atlanta(3) = "Chatty Cathy"

Chicago(0) = "Superman"
Chicago(1) = "Superboy"
Chicago(2) = "Superwoman"
Chicago(3) = "Supergirl"
Chicago(4) = "Superdog"
Chicago(5) = "Supercat"

Denver(0) = "Pink Floyd"
Denver(1) = "Big Red"
Denver(2) = "Little Red"






'Use the value of the dropdown to case select condition

Select Case ActiveDocument.FormFields("Dropdown1").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("Result").DropDown.ListEntries.Clear
For var = 1 To 4
ActiveDocument.FormFields("Result").DropDown.ListEntries.Add Name:=Atlanta(i)
i = i + 1
Next
ActiveDocument.FormFields("Result").DropDown.Value = 1
Case 2
ActiveDocument.FormFields("Result").DropDown.ListEntries.Clear
For var = 1 To 4
ActiveDocument.FormFields("Result").DropDown.ListEntries.Add Name:=Chicago(i)
i = i + 1
Next
ActiveDocument.FormFields("Result").DropDown.Value = 1
Case 3
ActiveDocument.FormFields("Result").DropDown.ListEntries.Clear
For var = 1 To 4
ActiveDocument.FormFields("Result").DropDown.ListEntries.Add Name:=Denver(i)
i = i + 1
Next
ActiveDocument.FormFields("Result").DropDown.Value = 1
End Select
End Sub

ps: I don't have San Fransisco listed in the code b/c I want to use it as a testing tool - to see what happens when I select a value that is not included in the code.

[fumei]

As you have sent me a PM regarding this, and am going to send me your document, I will wait to see that before a real post.

Also note, as this is a public request, I will also post my response here.

[Steiner]

I haven't tested the code, but what looks strange to me, is that your arrays range from 0 to 2, 3, 5, but your statements to fill the lists always go from 1 to 4, may this be the reason why?

Daniel

[fumei]

OK, due to your other issue raised regarding the 25 limit on formfield items, there are major changes here.

This is a partial demo. I have Group Office, and Sales rep, and Account Assistance working via ActiveX control.

NOTE! this means I have removed those items as formfields. They are NO LONGER in the document as formfields. The information these items comes from the userform (displayed on Document Open), and are inserted properly into bookmarks. In other words, because they are not formfield, the user will not be edited formfields if they want to change something.

NOTE! the data listed in the textbox controls on the userform does NOT come from hard coded arrays within the userform. They come from separate doc files:

AtlantaSalesRep.doc
AtlantaAccountAssistant.doc
ChicagoSalesRep.doc
ChicagoAccountAssistant.doc
DenverSalesRep.doc
DenverAccountAssistant.doc

I am including a ZIP of all files. Unzip them to the same folder, and it should work. What this does is open the appropriate file - if Atlanta is selected as the group Office, then ONLY AtlantaSalesRep.doc is opened, and the names pulled from that file, and loaded into Sales Rep. Further, it will open AtlantaAccountAssistant and loads THOSE names into Account Assistants.

This allows you edit - add/subtract names for each location independently. It is NOT hard coded. You want to change the names for the Atlanta Sales Reps...open that file and change the names. The userform simply loads the contents of the file, by paragraph. Do NOT put anything else in the data file. Just the names, each on their own paragraph line.

This is a bit dirty. It may be better/faster to use text files, rather than Word docs. But it may give you some different ideas.

OK, the Done button on the userform takes the values from GroupOffice, SalesRep, AccountAssistant and dumps them into the document - in BOOKMARKS, not formfields.

The userform is displayed on Document Open, but there is also a InputData button to display the userform as well. And there is a ReSet button at the end of the document.

Properly done, any RESET should either:

a) clear all fields and start totally fresh; OR
b) reload existing data and allow changes.

Hopefully this may help. I kind of ignored your original question regarding the formfields.....

Steiner, yeah there was a count misdirection. dattagirl, it really does not matter whether you do things as 0 To X, or 1 TO X, but it is a good idea to be consistent. When using arrays decide on a mthod, and stick with it. There ARE some reasons to change things, but for the most part consistency is a good thing.

[PKisielewski]

http://www.vbaexpress.com/forum/showthread.php?t=9265

Just wanted to mention that I had a similar problem and Lucas helped me out. I have created a Word form that is very user friendly and as I call it an intelligent list that depending on what I select on combo box 1 then text box 2 and 3 will reflect the correct info. It is hard coded in the vb but it works great.
I have attached the link above to my previous question.
If anyone is interested I can attach my updated word page. It really is very easy. I really appreciated Lucas' help. :hi: