Dependant Multi-Selection List Boxes~HELP, I'm Stuck!

[karen]

Help, I've spent all day reviewing forms, books, and help features and am
getting no where!

I have a form with 12 list boxes that pull list values from 12 different
tables.
As the User selects values from the first list box I want the second list
box to reflect the possible choices given the first list box values selected,
and so on down the line of the 12 list boxes. Then, at the end of the series
of boxes, I would like to have a button to "Find" the user selections within
the links across the various tables.

I've messed up the process enough that all i've got is the 12 list boxes set
to multi-select, and primed with a query as the source that provides a
distinct list of values for each list box based on it's relative table when
the form opens. (ideally each list box would remain blank until populated
with the users filtered selections)

I tried going farther with macros but broke the form and had to start again.

I tried going farther with VBA but know NOTHING so just going based on
examples I've seen but these do not apply to my scenario and the code breaks.

ANYTHING might help!


Here is one of the queries used as the source for one of the list boxes.....

SELECT DISTINCT Tax_Kingdom.Kingdom
FROM Tax_Kingdom INNER JOIN Taxonomy ON Tax_Kingdom.Kingdom=Taxonomy.Kingdom
ORDER BY Tax_Kingdom.Kingdom;

I also tried adding this where clause but it didn't seem to do anything....

WHERE ("Taxonomy.Empire=Forms.Taxonomy_Search.Empire.Column(0, VarItem)")

Here is the VBA code that is broken.....

Private Sub Update_Click()
Dim strWhere As String, varItem As Variant
If Forms.Taxonomy_Search.Empire.ItemsSelected.Count = 0 Then Exit Sub
For Each varItem In Forms.Taxonomy_Search.Empire.ItemsSelected
strWhere = strWhere & Forms.Taxonomy_Search.Empire.Column(0,
varItem) & ","
Next varItem
strWhere = Left$(strWhere, Len(strWhere) - 1)
strWhere = "(" & strWhere & ") IN [Empire]"
'strWhere = "[Empire] IN (" & strWhere & ") And (Inactive = False"
DoCmd.Save AcString, "strWhere"
DoCmd.SelectObject AcString, strWhere
Forms!Taxonomy_Search!Kingdom.SetFocus
DoCmd.ApplyFilter "Search Kingdom based on Empire", "Empire = 'strWhere'"
'DoCmd.GoToControl "Forms.Taxonomy_Search.Update_Kingdom"
End Sub

Empire is the name of the first list box in the form called Taxonomy_Search
while Kingdom is the name of the second list box. Update is the button the
user clicks on to update the filtered selections in the next list box.
Update_Kingdom is the name of the next button the user can select to produce
the filtered selections in the Kingdom list box. Some rows in the VBA code
are commented out while I was testing to see what lines broke. They all seem
to break. When it does work then I'm prompted for the paramaters of the
query, which I do not want to happen I want the process to occur based on the
user's selection in the previous list box.

This is all greek to me, so lamen's terms are appreciatted!!!!

 

[Jeanette Cunningham]

Hi Karen,
what you are trying to do is fairly complicated for even experienced
developers.
You know what they say about learning to walk before you can run, the same
applies with using Access.
If you don't know any VBA and don't have any experience with
coding/programming, you will find this difficult.
It can take several months to learn enough about access to set up a form to
do what you want.
There is a example of filtering by several terms and a sample datbaase at
this link

http://allenbrowne.com/ser-62.html

You will find it much easier if you set your listboxes so the multi select
is set to No.
You will also find it easier if you make a copy of the form and remove 11 of
the listboxes.
When you get it working with one listbox, then it is time to add another
listbox and make them dependant.


Jeanette Cunningham -- Melbourne Victoria Australia

Help, I've spent all day reviewing forms, books, and help features and am
getting no where!

I have a form with 12 list boxes that pull list values from 12 different
tables.
As the User selects values from the first list box I want the second list
box to reflect the possible choices given the first list box values
selected,
and so on down the line of the 12 list boxes. Then, at the end of the
series
of boxes, I would like to have a button to "Find" the user selections
within
the links across the various tables.

I've messed up the process enough that all i've got is the 12 list boxes
set
to multi-select, and primed with a query as the source that provides a
distinct list of values for each list box based on it's relative table
when
the form opens. (ideally each list box would remain blank until populated
with the users filtered selections)

I tried going farther with macros but broke the form and had to start
again.

I tried going farther with VBA but know NOTHING so just going based on
examples I've seen but these do not apply to my scenario and the code
breaks.

ANYTHING might help!


Here is one of the queries used as the source for one of the list
boxes.....

SELECT DISTINCT Tax_Kingdom.Kingdom
FROM Tax_Kingdom INNER JOIN Taxonomy ON
Tax_Kingdom.Kingdom=Taxonomy.Kingdom
ORDER BY Tax_Kingdom.Kingdom;

I also tried adding this where clause but it didn't seem to do
anything....

WHERE ("Taxonomy.Empire=Forms.Taxonomy_Search.Empire.Column(0, VarItem)")

Here is the VBA code that is broken.....

Private Sub Update_Click()
Dim strWhere As String, varItem As Variant
If Forms.Taxonomy_Search.Empire.ItemsSelected.Count = 0 Then Exit Sub
For Each varItem In Forms.Taxonomy_Search.Empire.ItemsSelected
strWhere = strWhere & Forms.Taxonomy_Search.Empire.Column(0,
varItem) & ","
Next varItem
strWhere = Left$(strWhere, Len(strWhere) - 1)
strWhere = "(" & strWhere & ") IN [Empire]"
'strWhere = "[Empire] IN (" & strWhere & ") And (Inactive = False"
DoCmd.Save AcString, "strWhere"
DoCmd.SelectObject AcString, strWhere
Forms!Taxonomy_Search!Kingdom.SetFocus
DoCmd.ApplyFilter "Search Kingdom based on Empire", "Empire =
'strWhere'"
'DoCmd.GoToControl "Forms.Taxonomy_Search.Update_Kingdom"
End Sub

Empire is the name of the first list box in the form called
Taxonomy_Search
while Kingdom is the name of the second list box. Update is the button
the
user clicks on to update the filtered selections in the next list box.
Update_Kingdom is the name of the next button the user can select to
produce
the filtered selections in the Kingdom list box. Some rows in the VBA
code
are commented out while I was testing to see what lines broke. They all
seem
to break. When it does work then I'm prompted for the paramaters of the
query, which I do not want to happen I want the process to occur based on
the
user's selection in the previous list box.

This is all greek to me, so lamen's terms are appreciatted!!!!