SubForm Filter based on Combo box (List box) Multiselects

[natwong]

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

[MMcCarthy]

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.

 
Private Sub cmdFindRecords_Click()
Dim strWhere As String
Dim iSelected As Variant
 
  strWhere = "(("
  For Each iSelected In Me.ListBoxA_Name.ItemsSelected
    strWhere = strWhere & "[A_id]=" & Me.ListBoxA_Name.ItemData(iSelected) & " OR "
  Next iSelected
 
  ' remove last OR, close the bracket and add AND with open bracket
  strWhere = Left(strWhere, Len(strWhere) - 4) & ") AND ("
 
  For Each iSelected In Me.ListBoxB_Name.ItemsSelected
    strWhere = strWhere & "[B_id]=" & Me.ListBoxB_Name.ItemData(iSelected) & " OR "
  Next iSelected
 
  ' remove last OR, close the bracket and add AND with open bracket
  strWhere = Left(strWhere, Len(strWhere) - 4) & ") AND ("
 
  For Each iSelected In Me.ListBoxC_Name.ItemsSelected
    strWhere = strWhere & "[C_id]=" & Me.ListBoxC_Name.ItemData(iSelected) & " OR "
  Next iSelected
 
  ' remove last OR, close the bracket and add AND with open bracket
  strWhere = Left(strWhere, Len(strWhere) - 4) & ") AND ("
 
  For Each iSelected In Me.ListBoxD_Name.ItemsSelected
    strWhere = strWhere & "[D_id]=" & Me.ListBoxD_Name.ItemData(iSelected) & " OR "
  Next iSelected
 
  ' remove last OR, close the bracket and add AND with open bracket
  strWhere = Left(strWhere, Len(strWhere) - 4) & ") AND ("
 
  For Each iSelected In Me.ListBoxE_Name.ItemsSelected
    strWhere = strWhere & "[E_id]=" & Me.ListBoxE_Name.ItemData(iSelected) & " OR "
  Next iSelected
 
  ' remove last OR and close the both brackets
  strWhere = Left(strWhere, Len(strWhere) - 4) & "))"
 
  Forms![MainFormName]![SubFormName].Form.RecordSource = "SELECT * FROM TableName WHERE " & strWhere & ";"
  Me.SubFormName.Requery
 
End Sub
 
 

[NeoPa]

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.

[MMcCarthy]

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

[NeoPa]

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).

[MMcCarthy]

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

[natwong]

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.

[NeoPa]

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.

[helenevans80]

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.

Private Sub Command113_Click()
Dim strWhere As String
Dim iSelected As Variant
 
  strWhere = "(("
 
 
  For Each iSelected In Me.aclist.ItemsSelected
  If Not IsNull(aclist) Then
 strWhere = strWhere & "[Activity]=" & Me.aclist & ")"
  End If
  Next iSelected
 
  ' remove last OR, close the bracket and add AND with open bracket
  strWhere = Left(strWhere, Len(strWhere) - 1)
  If Left(strWhere, 1) = Not Null And Me.Combo58 = Not Null Then
  strWhere = strWhere & ")AND("
  End If
  For Each iSelected In Me.Combo58.ItemsSelected
    If Not IsNull(Combo58) Then
      strValues = strValues & "[Activity_theme]=" & Me.Combo58 & ")"
 
 End If
 Next iSelected
 
  ' remove last OR, close the bracket and add AND with open bracket
  strWhere = Left(strWhere, Len(strWhere) - 1)
 
 
   If Left(strWhere, 1) = Not Null And Me.Combo60 = Not Null Then
  strWhere = strWhere & ")AND("
  End If
  For Each iSelected In Me.Combo60.ItemsSelected
  If Not IsNull(Combo60) Then
    strWhere = strWhere & "[Activity_group]=" & Me.Combo60 & ")"
  End If
  Next iSelected
  ' remove last OR, close the bracket and add AND with open bracket
  strWhere = Left(strWhere, Len(strWhere) - 1)
 
   If Right(strWhere, 1) = ")" And Me.Scheme_gp = Not Null Then
  strWhere = strWhere & ")AND("
  End If
  For Each iSelected In Me.Scheme_gp.ItemsSelected
  If Not IsNull(Scheme_gp) Then
    strWhere = strWhere & "[Scheme_group]=" & "[Forms]![rep]![Scheme_gp]" & ")"
  End If
 Next iSelected
 
  ' remove last OR, close the bracket and add AND with open bracket
  strWhere = Left(strWhere, Len(strWhere) - 1)
  If Right(strWhere, 1) = ")" And Me.Combo64 = Not Null Then
  strWhere = strWhere & ")AND("
  End If
  For Each iSelected In Me.Combo64.ItemsSelected
  If Not IsNull(Combo64) Then
    strWhere = strWhere & "[CH_theme]=" & "[Forms]![rep]![Combo64]"
  End If
 Next iSelected
 
 
  ' remove last OR and close the both brackets
  strWhere = Left(strWhere, Len(strWhere) - 1) & ")"
 
  Forms![rep]![Master2].Form.RecordSource = "SELECT * FROM Master WHERE " & strWhere & ";"
  Me.Master2.Requery

[zahid hameed]

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