I will be working in the next couple of days on a situation that goes a bit like this and just wanted to know beforehand, whether it is possible. The situation is as follows:
- Suppose on a form that I have two combo-boxes (A and B), which both store multiple values.
The question: Is it possible to create cascading combo-boxes, so as Combo-box B gets populated based on which one or more values are selected in Combo-box A?
I wouldn't go this way with it, you are making more trouble for yourself than it's worth.
If you want to have one combobox control values displayed in another combobox then just use simple, normal everyday combo boxes with a table as a record source. Google cascading combo boxes for more information.
The question: Is it possible to create cascading combo-boxes, so as Combo-box B gets populated based on which one or more values are selected in Combo-box A?
I did too, which means that it's connected to a MultiValue Field, and while it may be possible, it's got to be way more complicated to code than a standard Cascading Combobox scenario, and I'd have to agree with Gizmo that it's more trouble than it's worth!
Let's clarify the situation a bit:
- Each combobox has a record source in a table and is setup so as to store multiple values.
- Combobox A holds information on the date of cultural objects (e.g. Hellenistic, Roman, etc.).
- Combobox B holds information on the ceramic shapes that you could assign to the cultural object (e.g. amphora, jug, plate, etc.).
What I want is for the user to be able to select one or more dates (the reason why is due to uncertainty), and for Combobox B to contain only the ceramic types associated with the date or dates selected in Combobox A.
I will probably need to use some form of Case in VBA in conjunction with SQL queries. Will let you know how this goes.
PS: Combobox B draws the values from a table that also has a date field, which is the source of Combobox A too. We know to which period each ceramic shape dates to, but there is uncertainty for dating and shape type when recording new cultural objects.
I haven't studied it myself because when they introduced these newfangled multi value thingys everyone said don't use them! They will lead to Great pain and suffering...
Being a bit of a masochist I am inclined to wander into Waters marked "There be Monsters here" because you can learn new stuff.
However I haven't taken on this monster yet, but in preparation for the day I did make a note of this video which looks very interesting and informative. I'm not a hundred percent sure it will be of use to you, but it's somewhere to start. I would be very interested to see what you come up with.
Are the dates and shapes that are to appears in the combo boxes already in multi-value fields? I'm betting they are not and to somehow get them like that would be a challenge. So instead of using a combo box I suggest considering using a listbox with the Multi Select enabled. When dates are selected in the listbox you would build the row source for the shape combo box with code like:
Code:
Private Sub lstDates_Click()
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strComboboxRowSource 'SQL for combo box row source
strDelim = "#" 'Delimiter appropriate to field type. See note 1.
strComboboxRowSource = "SELECT DISTINCT Table1.ObjectShape FROM Table1 "
'Loop through the ItemsSelected in the list box.
With Me.lstDates
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ObjectDate] IN (" & Left$(strWhere, lngLen) & ")"
End If
If Len(strWhere) > 0 Then
strComboboxRowSource = strComboboxRowSource & " WHERE " & strWhere & ";"
Else
strComboboxRowSource = ""
End If
Me.cboShapes = Null 'Reset the combo box for reselection
Me.SubForm.Requery 'Clear the subform
Me.cboShapes.RowSource = strComboboxRowSource
Me.cboShapes.Requery
End Sub
This is just an adaptation of the Allen Brownes code that is used to form a filter for reports. You can see this function in the attached database. Note that the data that shows up in the subform is only based on the shapes combo box.
Another way to do this might be to add a Yes/No field to your table and then display the distinct dates in a a continuous form in a subform. This is not demonstrated in the attached database but if you are interested I could set up an example of that.
If you go with either of these you should put indexes on the dates and shapes field to speed this up.
Are the dates and shapes that are to appears in the combo boxes already in multi-value fields? I'm betting they are not and to somehow get them like that would be a challenge..
So I've tried several solutions and it is very difficult to say the list, if not impossible.
I've created several tables with one-to-many relationships, but I'm stuck with the VBA code to make the multiple values listed in the Subform handling cultural periods associations filter/cascade the multiple values that can potentially be available in the combobox of the Subform handling ware associations. :banghead:
I've put the following in the AfterUpdate for the Period combobox, but does not work.
Code:
Private Sub PeriodID_AfterUpdate()
strSQL = "SELECT Wares.ID, Wares.Ware " & _
"FROM Wares WHERE (((Ware_Period.PeriodID)=" & Me.PeriodID.Column(1) & ")) ORDER BY Wares.Ware;"
Forms!Main!MW.Form!WareID.RowSource = strSQL ' set new RowSource for Ware combobox
Forms!Main!MW.Form!WareID.Requery ' refresh the combobox
Forms!Main!MW.Form!WareID.SetFocus ' move focus to the Ware combobox
End Sub
Bringing this back, so as to say thank you everyone for the various sources of inspiration. I have finally found a way to work it out after much much much trial and error. :banghead: To begin with, let's just say that selecting values in Combo-box A and populating Combo-box B, when both are multi-valued fields simply doesn't work, as it violates database integrity, while the alternative of building many-to-many tables creates too much clutter in the forms and renders them difficult to use, not to mention the vagaries of creating heavy SQL syntax within VBA.
To resolve the above, I've done the following:
- Replaced the first combobox with a series of 11 Yes/No fields representing the possible selections.
- Also added 11 Yes/No fields in each of the tables that I wanted to later draw values from and a field called Selector for use also later.
- Created a calculated field called POTSELECTOR that concatenated nested multiple IIF clauses. Each IIF clause returns a value of 1 or 0 depending on each of the abovementioned Yes/No fields. The resulting number always has 11 digits.
- This number is then passed on to a public function stored in a module that saves it as a string.
- This string is then fed to 6 Queries, which eventually will inform the six fields I want to automatically update the rowsource property of, when the user makes his/her selections in the initial 11 Yes/No fields. In each query there are two expressions each one in a separate column for each of the Yes/No fields (hence 22 columns with expressions), as follows:
Code:
expr1: IIf(Left({Call Public Function};1)="1";True;False)
expr2: Iif({Field to compare}=True;IIf([expr1]={Field to compare};True;False);False)
- The first expression varies in each column to detect the appropriate character being processed each time and makes use of string functions for Left, Right and Mid.
- There is also another column called SELSUM in each query that sums the values resulting from each expr2 column. This values can range from 0 to -11.
- Also created 6 Update queries that transfer the SELSUM value of each query to the Selector field in each of the Tables meant to be used as the Rowsource.
- Finally added a button on the main form, which is meant to be clicked after the user has made his/her selection(s) in the Yes/No fields mentioned initially. The button fires up the following code:
Code:
Me.Refresh
{Public function string} = Me.{POTSELECTOR}
DoCmd.OpenQuery "{Sorting Query}"
DoCmd.Close acQuery, "{Sorting Query}"
DoCmd.SetWarnings False
DoCmd.OpenQuery "{Update Query}"
DoCmd.SetWarnings True
DoCmd.Close acQuery, "{Update Query}"
Me.{Combobox to enter values}.Enabled = True
Me.{Combobox to enter values}.RowSource = "SELECT {Fields to select} FROM {Table to select from} WHERE ({Table to select from}.{Selector} <> 0) ORDER BY {Whatever you want};"
End Sub
- The code is reiterated six times for each of the comboboxes I need to become auto-populated with values from the initial user input.
Although it takes a couple of seconds to run the queries, it works like a charm. I hope this helps someone!
Excellent! Well-done! I would just like to say I think you've done the right thing, in that you've had a problem you wanted to solve and you have solved it.
This is an important step and attitude to take in coding, for two reasons first you learn so much more about programming like this and secondly you actually learn about the problem you are trying to solve.
If you have the time and inclination I would suggest that you revisit the problem at a later stage mainly as an opportunity for you to improve your programming skills.
I say this because I think there is some merit in adopting your earlier suggestion in using multi select combo boxes.
I still think it's a lot of trouble! However there would be the bonus of improving your programming ability, because to make this work with the multi select combo boxes (I think it might be possible but I'm not 100℅ sure) you would need to adopt what I probably incorrectly term an object orientated approach.
If you have the time and inclination I would suggest that you revisit the problem at a later stage mainly as an opportunity for you to improve your programming skills.
I say this because I think there is some merit in adopting your earlier suggestion in using multi select combo boxes.
