By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,519 Members | 2,412 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,519 IT Pros & Developers. It's quick & easy.

SubForm Filter based on Combo box (List box) Multiselects

P: 3
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
Share this Question
Share on Google+
9 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534

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
Expert Mod 15k+
P: 31,494
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
Expert Mod 10K+
P: 14,534
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
Expert Mod 15k+
P: 31,494
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
Expert Mod 10K+
P: 14,534
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

P: 3
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
Expert Mod 15k+
P: 31,494
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

P: 1
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

P: 1
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.