469,636 Members | 1,558 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,636 developers. It's quick & easy.

SubForm Filter based on Combo box (List box) Multiselects

Hi All,

I'm a newbie in terms of Access and some of its functionality . I've been stuck on this problem for a couple days, even after searching the Web, etc.

Currently I have five combo boxes (actually list boxes) that are multiselects in my main form. I need to use these combo boxes to filter a subform within my main form.

My combo boxes are as follows:
1. A - 4 select options
2. B - 10 select options
3. C - 4 select options
4. D - 3 select options
5. E - 4 select options

My subform is a listing of all the possible combinations of these select options (therefore there are 4 x 10 x 4 x 3 x 4 records).

I am having trouble understanding how to link these unbound list boxes to the subform to make the filter work once a user has selected their options from the 5 combo boxes.

Case example:
If I select options 1, 2, and 3 from list box A, and option 2 from listbox B, C, D, and E; I would want the subform to filter based on this query:


SELECT * FROM TABLE WHERE
(A_id = 1 OR A_id = 2 OR A_id = 3) AND
B_id = 2 AND
C_id = 2 AND
D_id = 2 AND
E_id = 2;


I hope this makes sense. Can anyone please help me out on this one. I'd be indebted to you! I really appreciate it

Any help on which functions or what code to put and where to put it would be great!

Thanks,
Nate
Nov 13 '06 #1
9 15493
MMcCarthy
14,534 Expert Mod 8TB

Currently I have five combo boxes (actually list boxes) that are multiselects in my main form. I need to use these combo boxes to filter a subform within my main form.
Always refer to them as listboxes as the functionality is different and you will confuse the experts.


listboxes are as follows:
1. A - 4 select options
2. B - 10 select options
3. C - 4 select options
4. D - 3 select options
5. E - 4 select options

My subform is a listing of all the possible combinations of these select options (therefore there are 4 x 10 x 4 x 3 x 4 records).

I am having trouble understanding how to link these unbound list boxes to the subform to make the filter work once a user has selected their options from the 5 combo boxes.

Case example:
If I select options 1, 2, and 3 from list box A, and option 2 from listbox B, C, D, and E; I would want the subform to filter based on this query:


SELECT * FROM TABLE WHERE
(A_id = 1 OR A_id = 2 OR A_id = 3) AND
B_id = 2 AND
C_id = 2 AND
D_id = 2 AND
E_id = 2;
OK

I would create a command button (cmdFindRecords) and put this code behind it.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdFindRecords_Click()
  3. Dim strWhere As String
  4. Dim iSelected As Variant
  5.  
  6.   strWhere = "(("
  7.   For Each iSelected In Me.ListBoxA_Name.ItemsSelected
  8.     strWhere = strWhere & "[A_id]=" & Me.ListBoxA_Name.ItemData(iSelected) & " OR "
  9.   Next iSelected
  10.  
  11.   ' remove last OR, close the bracket and add AND with open bracket
  12.   strWhere = Left(strWhere, Len(strWhere) - 4) & ") AND ("
  13.  
  14.   For Each iSelected In Me.ListBoxB_Name.ItemsSelected
  15.     strWhere = strWhere & "[B_id]=" & Me.ListBoxB_Name.ItemData(iSelected) & " OR "
  16.   Next iSelected
  17.  
  18.   ' remove last OR, close the bracket and add AND with open bracket
  19.   strWhere = Left(strWhere, Len(strWhere) - 4) & ") AND ("
  20.  
  21.   For Each iSelected In Me.ListBoxC_Name.ItemsSelected
  22.     strWhere = strWhere & "[C_id]=" & Me.ListBoxC_Name.ItemData(iSelected) & " OR "
  23.   Next iSelected
  24.  
  25.   ' remove last OR, close the bracket and add AND with open bracket
  26.   strWhere = Left(strWhere, Len(strWhere) - 4) & ") AND ("
  27.  
  28.   For Each iSelected In Me.ListBoxD_Name.ItemsSelected
  29.     strWhere = strWhere & "[D_id]=" & Me.ListBoxD_Name.ItemData(iSelected) & " OR "
  30.   Next iSelected
  31.  
  32.   ' remove last OR, close the bracket and add AND with open bracket
  33.   strWhere = Left(strWhere, Len(strWhere) - 4) & ") AND ("
  34.  
  35.   For Each iSelected In Me.ListBoxE_Name.ItemsSelected
  36.     strWhere = strWhere & "[E_id]=" & Me.ListBoxE_Name.ItemData(iSelected) & " OR "
  37.   Next iSelected
  38.  
  39.   ' remove last OR and close the both brackets
  40.   strWhere = Left(strWhere, Len(strWhere) - 4) & "))"
  41.  
  42.   Forms![MainFormName]![SubFormName].Form.RecordSource = "SELECT * FROM TableName WHERE " & strWhere & ";"
  43.   Me.SubFormName.Requery
  44.  
  45. End Sub
  46.  
  47.  
Nov 13 '06 #2
NeoPa
32,203 Expert Mod 16PB
I would do something very like that, except I feel that the IN() SQL function would work perfectly for ListBoxes, rather than the more complicated [X]=A OR [X]=B etc.
Although not usually used for a single itemed list, IN() will work correctly in that scenario too.
ComboBoxes would be different, but IN() is better for MULTISELECT ListBoxes.
BTW Congratulations on a clearly stated question.
Nov 13 '06 #3
MMcCarthy
14,534 Expert Mod 8TB
I would do something very like that, except I feel that the IN() SQL function would work perfectly for ListBoxes, rather than the more complicated [X]=A OR [X]=B etc.
Although not usually used for a single itemed list, IN() will work correctly in that scenario too.
ComboBoxes would be different, but IN() is better for MULTISELECT ListBoxes.
BTW Congratulations on a clearly stated question.
Good suggestion Adrian

Never thought of using IN

Mary
Nov 13 '06 #4
NeoPa
32,203 Expert Mod 16PB
That's funny Mary.
I posted that a while back, and just in the last minute, I came across some code you posted for something similar which used a loop to create the comparison string - and IN() aswell.

The code may prove useful here too (Passing Multi-Select field parameters to a query).
Nov 13 '06 #5
MMcCarthy
14,534 Expert Mod 8TB
That's funny Mary.
I posted that a while back, and just in the last minute, I came across some code you posted for something similar which used a loop to create the comparison string - and IN() aswell.

The code may prove useful here too (Passing Multi-Select field parameters to a query).
I can't remember what I did 5 minutes ago and and you want me to remember how I answered another question. AHH!!!

Mary

Nov 14 '06 #6
Thanks so much everyone! It helped a lot. I really appreciate all your help and advice. I tweaked it a little, but the basic gist really helped! Thanks a bunch.
Nov 16 '06 #7
NeoPa
32,203 Expert Mod 16PB
It's always nice to have someone come back and let us know they've been helped.
Everybody likes to feel appreciated and, more importantly perhaps, we can consider a thread finished.
So thank you.
Nov 16 '06 #8
I've used the above code and tweeked it a little to allow the user to pick some list boxes to filter, but not others. (code posted below)

I get - 'run time error '5': Invalid procedure call or reference

I've had my head stuck in it for so long now, I'm not sure I could find the problem if it were staring me in the face. Any help would be much appreciated.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command113_Click()
  2. Dim strWhere As String
  3. Dim iSelected As Variant
  4.  
  5.   strWhere = "(("
  6.  
  7.  
  8.   For Each iSelected In Me.aclist.ItemsSelected
  9.   If Not IsNull(aclist) Then
  10.  strWhere = strWhere & "[Activity]=" & Me.aclist & ")"
  11.   End If
  12.   Next iSelected
  13.  
  14.   ' remove last OR, close the bracket and add AND with open bracket
  15.   strWhere = Left(strWhere, Len(strWhere) - 1)
  16.   If Left(strWhere, 1) = Not Null And Me.Combo58 = Not Null Then
  17.   strWhere = strWhere & ")AND("
  18.   End If
  19.   For Each iSelected In Me.Combo58.ItemsSelected
  20.     If Not IsNull(Combo58) Then
  21.       strValues = strValues & "[Activity_theme]=" & Me.Combo58 & ")"
  22.  
  23.  End If
  24.  Next iSelected
  25.  
  26.   ' remove last OR, close the bracket and add AND with open bracket
  27.   strWhere = Left(strWhere, Len(strWhere) - 1)
  28.  
  29.  
  30.    If Left(strWhere, 1) = Not Null And Me.Combo60 = Not Null Then
  31.   strWhere = strWhere & ")AND("
  32.   End If
  33.   For Each iSelected In Me.Combo60.ItemsSelected
  34.   If Not IsNull(Combo60) Then
  35.     strWhere = strWhere & "[Activity_group]=" & Me.Combo60 & ")"
  36.   End If
  37.   Next iSelected
  38.   ' remove last OR, close the bracket and add AND with open bracket
  39.   strWhere = Left(strWhere, Len(strWhere) - 1)
  40.  
  41.    If Right(strWhere, 1) = ")" And Me.Scheme_gp = Not Null Then
  42.   strWhere = strWhere & ")AND("
  43.   End If
  44.   For Each iSelected In Me.Scheme_gp.ItemsSelected
  45.   If Not IsNull(Scheme_gp) Then
  46.     strWhere = strWhere & "[Scheme_group]=" & "[Forms]![rep]![Scheme_gp]" & ")"
  47.   End If
  48.  Next iSelected
  49.  
  50.   ' remove last OR, close the bracket and add AND with open bracket
  51.   strWhere = Left(strWhere, Len(strWhere) - 1)
  52.   If Right(strWhere, 1) = ")" And Me.Combo64 = Not Null Then
  53.   strWhere = strWhere & ")AND("
  54.   End If
  55.   For Each iSelected In Me.Combo64.ItemsSelected
  56.   If Not IsNull(Combo64) Then
  57.     strWhere = strWhere & "[CH_theme]=" & "[Forms]![rep]![Combo64]"
  58.   End If
  59.  Next iSelected
  60.  
  61.  
  62.   ' remove last OR and close the both brackets
  63.   strWhere = Left(strWhere, Len(strWhere) - 1) & ")"
  64.  
  65.   Forms![rep]![Master2].Form.RecordSource = "SELECT * FROM Master WHERE " & strWhere & ";"
  66.   Me.Master2.Requery
Oct 13 '08 #9
i m greatful to you submitting this code i was wondering before watching this code i got idea from it and convert it to my own needs

thanks again
Aug 5 '10 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Nicolae Fieraru | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.