Two or More Dependent Lists

[RonZ]

Good Evening All -
New to the VBA thing and wanted to see if anyone could answer the question about adding an additional drop down dependent on the first drop down. This would be a variation on Fumei's code:

Option Explicit

Sub FirstFieldExit()

Dim Burglary(5) As String
Dim BurglarySec(2) As String

Dim Robbery(3) As String
Dim Assault(4) As String
Dim Theft(3) As String
Dim Vehicle(2) As String
Dim i As Integer
Dim iSec As Integer
Dim var
Dim VarSec

Burglary(0) = "RESIDENCE NIGHT (6PM-6AM)"
Burglary(1) = "RESIDENCE DAY (6AM-6PM)"
Burglary(2) = "RESIDENCE UNK TIME"
Burglary(3) = "NON-RESIDENCE NIGHT (6PM-6AM)"
Burglary(4) = "NON-RESIDENCE DAY (6AM-6PM)"
Burglary(5) = "NON-RESIDENCE UNK TIME"


Robbery(0) = "FIREARM"
Robbery(1) = "KNIFE/CUTTING"
Robbery(2) = "OTH DANGEROUS WEAPON"
Robbery(3) = "HANDS/FEET/FISTS"

Assault(0) = "FIREARM"
Assault(1) = "KNIFE/CUTTING"
Assault(2) = "OTH DANGEROUS WEAPON"
Assault(3) = "HANDS/FEET/FISTS"
Assault(4) = "NO WEAPON"

Theft(0) = "OVER $400"
Theft(1) = "LOSS BETW $200-$400"
Theft(2) = "LOSS BETW $50-$199.99"
Theft(3) = "LOSS UNDER $50"

Vehicle(0) = "Auto"
Vehicle(1) = "Trucks / Busses"
Vehicle(2) = "Motorcycle / Other"

' use the value of the dropdown to case select condition
Select Case ActiveDocument.FormFields("Dropdown1").DropDown.Value
Case 1
ActiveDocument.FormFields("Result").DropDown.ListEntries.Clear
For var = 1 To 6
ActiveDocument.FormFields("Result").DropDown.ListEntries.Add Name:=Burglary(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:=Robbery(i)
i = i + 1
Next
ActiveDocument.FormFields("Result").DropDown.Value = 1
Case 3
ActiveDocument.FormFields("Result").DropDown.ListEntries.Clear
For var = 1 To 5
ActiveDocument.FormFields("Result").DropDown.ListEntries.Add Name:=Assault(i)
i = i + 1
Next
ActiveDocument.FormFields("Result").DropDown.Value = 1
Case 4
ActiveDocument.FormFields("Result").DropDown.ListEntries.Clear
For var = 1 To 4
ActiveDocument.FormFields("Result").DropDown.ListEntries.Add Name:=Theft(i)
i = i + 1
Next
ActiveDocument.FormFields("Result").DropDown.Value = 1
Case 5
ActiveDocument.FormFields("Result").DropDown.ListEntries.Clear
For var = 1 To 3
ActiveDocument.FormFields("Result").DropDown.ListEntries.Add Name:=Vehicle(i)
i = i + 1
Next
ActiveDocument.FormFields("Result").DropDown.Value = 1


End Select
End Sub


My puzzle is how to add an additional dependency and then wrap the whole thing in a case statement. For instance, if 'Burglary' was selected from the first drop down, and 'Force' was selected from the second, (got this much , but getting lost here) then a third drop down would be auto populated with three choices. Depending on the .Value of the concateneated first box, second box, and third box, I want to write a case statement that outputs a code.

As an example, if Burglary, Residence Day(6AM - 6PM), Force is selected, output to a text box '05-A-02'

[gmaxey]

I can't follow your objective. You state:

"For instance, if 'Burglary' was selected from the first drop down, and 'Force' was selected from the second, (got this much , but getting lost here)"

Yet nowhere in your code that I see is there a dependency established between burglary and force. In the code you provided if Burglary is selected the results field is going to show one of the residency options.

Is the second a static field that you don't describe here (e.g., leathal force, non-leathal force, no force) and the third what is possilbly the second shown above?

Assuming this is the case:

DropDown1 = Burglary DropDown2 = Residence DropDown3 = Force

and you want a result built from all three.

I would use a function vice an elaborate Case Statement to build the rusults. Something like this:

Sub ConcateneatOnExit()
Dim oFF As FormFields
Set oFF = ActiveDocument.FormFields
oFF("Output").Result = Concateneated(oFF("DropDown1").DropDown.Value, oFF("DropDown2").DropDown.Value, oFF("DropDown3").DropDown.Value)
End Sub
Function Concateneated(ByRef A As Long, B As Long, C As Long)
Dim pStr As String
Select Case B
Case 1: pStr = "A"
Case 2: pStr = "B"
Case 3: pStr = "C"
End Select
Concateneated = Format(A, "00") & "-" & pStr & "-" & Format(C, "00")
End Function

[gmaxey]

Ron,

I have attached a document with an example of the method I posted earlier.

[RonZ]

Greg,

Thanks for the reply - I am studying the concatenate function you wrote - pretty slick. You have helped me most of the way and I a very grateful. I do have a couple of questions:


How do I expand the concatenate function to include the appropriate output?


The table attached to the document you were kind enough to post shows what the text box output should be. 2. I clearly have extraneous variables in my code, but I do not know how to Dim the Var for the selections in the second and third Drop Down or if I need to.

Family just came in - if I am not being clear it is because I am in a rush. I will explain more later - sry........

[gmaxey]

Ron,

These things can get complicated fast. I have attached another demo.

Shouldn't the first numbers in your output columne be 01 vice 05? Burglary is the first item in the list.

There are a lot of other conditions that will effect the output. It appears that "Force" is a sub-set of the burglary set. It therefore should probably be excluded if one of the other crimes are selected. FormFields begin to get very clunky at this point as the things that you might like to do on exit or on entry don't always do at you wish.

You might want to consider a UserForm to collect your data. I feel that you have much more control over how the data is collected and what you can to with it once collected.

Good luck.

[gmaxey]

Ron,

Edited 8/1/10. Please disregard this post and the attached file.

Attached is a file that provides a UserForm demo for collecting data and building the output string.

[gmaxey]

Ron,

Like you I was in a bit of a rush yesterday. I realized this morning that there were some critical errors in the UserForm demo that I provided you. While not perfect, the attached has corrected those errors.

For a little more on UserForms and tips on how you might incorporate the "output" string or any of the data collected see:

http://gregmaxey.mvps.org/Create_and_employ_a_UserForm.htm

[fumei]

RonZ, essentially anything you want to do regarding content of a dropdown (or any other control really) can probably be done. It is a simple matter of logic.

That being said, it certainly does NOT mean that the logic is simple! Once you get into third order (Condition_C comes from Condition_B which comes from Condition_A), it can get into headache territory. Nervtheless, the rules of logic still apply.

The bottom line is that if you can write it out (on paper helps), then you can code it.

[RonZ]

Greg, thanks for the help all & Fumei, you were right; once it was on paper, I could see where I was going wrong.

This was sorted out after much trial and error, but at least it got sorted!