Multiple-condition 'cascading' combo boxes?

[pitt_ph]

Hey all,

I'm not sure if this type of thing has been answered before, but I've looked at most of the available cascading sample boxes on Google, but nothing's really worked for my question so far. (In fact, I'm not even sure if this fits under the typical definition of a "cascading combo box". It was the best title I could come up with on the fly, though.)

So, I have four combo boxes -- denoted as Combo171, Combo173, Combo 175 and Combo177 -- in a form in a database. The first three combo boxes are separate entities from each other (independent events), and the fourth denotes "None" (namely, that the first three conditions aren't met).

Each combo box displays the same two conditions -- "Yes" (bound as "1" in the form and in the table) and "No" (bound as "0" in my form and table).*

I've been pretty good so far with specifying when conditions are met -- if either Combo171, Combo173 or Combo175 show a "Yes" condition, Combo177 automatically defaults to a "No" condition.

Likewise, when the "Yes" condition is selected for Combo177, Combo171, Combo173 and Combo175 all display "No" conditions. If "No" is selected for Combo177, all the other combo boxes remain blank, so the user can pick which condition is marked as "Yes".

However, where I'm drawing a blank is the situation in which the first three combo boxes all report a "No", and Combo177 reports a "Yes". I originally tried to write an If/Then statement in VBA with "And" operators, but it appears that this approach doesn't work.

The sub that I wrote in VBA to try to create a combo box value change based on multiple conditions was:

Private Sub Combo177_BeforeUpdate(Cancel As Integer)

' This is the combo that seems to be driving me up the wall.

Dim ComboNone As String

If Me.Combo171.Value = "0" And Me.Combo173.Value = "0" And Me.Combo175.Value = "0" Then
    ComboNone = (Me.Combo177.Value = "1")
Else
    ComboNone = (Me.Combo177.Value = "0")
End If

' I got a message here asking me to save the field before requerying. How do I do this?

'Me.Combo177.Requery

End Sub

I've attached a small sample database which contains the fields and macros in question. If anyone has any ideas or feedback on how to improve this, it would be greatly appreciated.

Thank you in advance!

* As a note: originally, I had these as checkboxes, but we wanted to be able to transfer the results of this form to Excel, so that appeared to be a no-go...

 

[boblarson]

The very first thing you need to do is rename your combos to something which is meaningful for them. Do NOT leave them as Combo171, etc. You know what they are today because you've been working on them. But say you don't touch it in a year and then come back, or someone else has to step in to fix something. It is a real pain to have to try to go look and figure out what they are when, if you just gave them a good name, it would be clear upon looking. (I say this because I had to fix someone's database before who had done what you have and it totally sucked and took way too much time to deal with when it would have been much easier had the control been named something meaningful).

For example, if the combo deals with a first name, cboFName is a good, short, but yet descriptive name which makes it easy to know what it is for.

As for your logic, I'll try to digest it but I wanted to post this ASAP because you really need to go fix ANYTHING where you have not renamed controls to something which reflects what they are.

 

[pitt_ph]

Hey Bob,

Thanks for the information. In reality, all the fields in the table and the controls in the forms do have meaningful names that make sense in the context of the overall database.

In the interest of stripping down the database down to the bare bones, to essentially show what I was trying to figure out, I renamed all the controls and combo boxes for this small database only.

Does this make sense?

 

[boblarson]

Hey Bob,

Thanks for the information. In reality, all the fields in the table and the controls in the forms do have meaningful names that make sense in the context of the overall database.

In the interest of stripping down the database down to the bare bones, to essentially show what I was trying to figure out, I renamed all the controls and combo boxes for this small database only.

Does this make sense?

That's fine, just wanted to catch anything as quickly as possible should that not have been the case.

 

[boblarson]

Okay, I'm a bit confused by your code in the sample.

I'm not seeing what it is that you're looking to do? What is supposed to happen if all of the first 3 are no, should the last one automatically turn yes?

 

[pitt_ph]

Hey Bob,

You're correct in your assumption. Here are all the permutations that I tried to account for with the four combo boxes:

If the first three combo boxes are answered first:

If someone answered "No" for Combo171, Combo173 and Combo175 in the example, Combo177 automatically defaults to "Yes". [What I'm stuck on.]

If either Combo171, Combo173 or Combo175 has an answer of "Yes", Combo177 automatically defaults to "No". [DONE]

If the LAST combo box is answered first (i.e., in the case where someone wants to indicate that none of these conditions exists.):

If Combo177 is answered as "Yes", Combo171, Combo173 and Combo175 all default to "No". [DONE]

If Combo177 is answered as "No", Combo171, Combo173 and Combo175 all default to a zero-length string (""), so that the correct answer can then be selected. [DONE]

I know the logic may seem pretty iffy, but I'm trying to make the answering conditions as flexible as possible for the user. I don't know what the person may answer first, and I'm trying to make a mock-up of the possible conditions for further refinement.

 

[pitt_ph]

For further reference, I might shut off some of the fields in the case of someone answering a particular case, using the .Enable property. I'm still trying to think through the logic of answering the combo boxes, and wanted to see what would be more intuitive for a user to use.

PS: congrats on the 30,000 Access posts!

 

[Pat Hartman]

If the value is mutually exclusive, you should use a single column and display it using an option group.

 

[pitt_ph]

Alright, I'm going to bite -- what exactly is an "option group"? (I'm looking at the Microsoft help sheet for an "OptionGroup Object", but I'm not sure what exactly it's telling me.)

 

[John Big Booty]

An option group is a control that allows you to select from a set and limited number of options as shown in the image below. It will return a numeric value dependant on which option is selected.

 

[pitt_ph]

Hm, everything that's been posted so far has been tremendously helpful - thank you, everyone!

Unfortunately, I'm not sure if I simply have tunnel vision towards a particular layout, but I don't think a single option group would be applicable in this context. As far as I've been able to tell looking through the help pages for option groups, it appears that an option group can seed one value (out of a range of multiple) into a single field -- it's basically like a list, but it can display all the options with option buttons.

The thing is, I have four separate fields for four separate options. Combo171, Combo173 and Combo175 are all analogous to separate situations. A user might experience all the situations, some of the situations, or none of the situations (hence, the "yes/no" context here). Combo177 is just a default "none" response, in case if the person isn't going through any of the situations but just doesn't want to answer "no" for three separate blanks.

I could just have the three main fields there (what is analogous to "Combo171", "Combo173" and "Combo175"), and not have the fourth field (what is analogous to "Combo177") appear on the form, but appear in the table, which would decrease the possible rate of user error. However, I still don't know how this would work in practice -- this still would not eradicate the programming problem that I experienced in the first place.

The annoying thing with this form is that no matter what I do, I still have to seed responses into three or four separate blanks. Therefore, I don't think an option group is available here as an option, unless I place multiple action group controls into the form (one for each field).

Any other suggestions of how to implement the option group controls then, should I choose to take this path?

 

[pitt_ph]

Alright, so as a (quick) update:

I decided to see what would happen if I decided to put in an option group into the form.

I created a new field, where a single option group (using checkboxes) was placed. I coded the previous comboboxes to be a single option group, with one number linked to each combo-box. (I'm having trouble right now linking individual scenarios to options in a combo box, but that might be worked around.)

Parsing out the answer of this string (which, I believe, can be coded as "1,2,3" or "0", depending if you want an "all" or "none" scenario) is going to be annoying, but I think that can be done efficiently with a split function.

Also, sorry for coming off as irascible in the last post -- I'm really bad without my morning caffeine fix.

 

[pitt_ph]

I've attached here an updated database with various implementations of a combo box or an option group (where each control, ideally, can put multiple values into a single field in a table).

I don't know if I did this correctly, but I was just curious to see what the better implementation (according to Access experts) would be!

 

[pitt_ph]

Hi all,

Thank you so much for your feedback! I just realized that I didn't put up a post for what I finally agreed to do with this database... but I took out the "None" condition (the equivalent of Combo177 in the database), just because it was a redundant condition.

That said, I've finally learned about option groups, and that's a tremendously helpful thing to think about in the future! Thank you for the feedback, all!