Multiple multiselect list boxes and option groups

Hi,
I have a form with 2 multiselect list boxes and 2 option group frames on it.
I have code that works for the 2 list boxes and 1 option group (see below):
How do I add the second option group to the code?

Thanks in advance.

Private Sub SearchButton_Click()
On Error GoTo SearchButton_Error
   
   Dim Q As QueryDef, db As Database
   Dim Criteria As String
   Dim ctl As Control
   Dim Itm As Variant
   Dim Criteria5 As String
   Dim ctl5 As Control
   Dim Itm5 As Variant
           
   ' Build a list of the selections.
   Set ctl = Me![List0]
     
   For Each Itm In ctl.ItemsSelected
      If Len(Criteria) = 0 Then
         Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
      Else
         Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
          & Chr(34)
      End If
   Next Itm

   If Len(Criteria) = 0 Then
      Itm = MsgBox("You must select one or more items in the" & _
        " Pilot Name list box!", 0, "Ooops")
      Exit Sub
   End If
   
    Set ctl5 = Me![List5]

    For Each Itm In ctl5.ItemsSelected
      If Len(Criteria5) = 0 Then
         Criteria5 = Chr(34) & ctl5.ItemData(Itm) & Chr(34)
      Else
         Criteria5 = Criteria5 & "," & Chr(34) & ctl5.ItemData(Itm) _
          & Chr(34)
      End If
   Next Itm

   If Len(Criteria5) = 0 Then
      Itm5 = MsgBox("You must select one or more items in the" & _
        " Check/Currencies list box!", 0, "Ooops")
      Exit Sub
   End If
     
   Select Case Me.Frame23
    Case 1: strSQL = " AND [Date Due]"
    Case 2: strSQL = " AND [Date Due]<=Date()"
    Case 3: strSQL = " AND [Date Due]<=Date()+30"
    Case 4: strSQL = " AND [Date Due]Between #" & Format(Me![Text38], "dd/mm/yyyy") & "# And #" & Format(Me![Text40], "dd/mm/yyyy") & "#"
   End Select

         ' Modify the Query.
   Set db = CurrentDb()
   Set Q = db.QueryDefs("Query")
   Q.SQL = "Select * From Currencies Where [Pilot Name] In(" & Criteria & ") AND Requirements In(" & Criteria5 & ")" & strSQL & ";"
   Q.Close

   ' Run the query.
   DoCmd.OpenQuery "Query"

SearchButton_Exit:
Exit Sub

SearchButton_Error:
Select Case Err
Case 3075
  MsgBox "Please enter the dates you wish to search between.", 0, "Ooops"
Case Else
 MsgBox Error, vbCritical, "Error: " & Err
End Select

Resume SearchButton_Exit
End Sub
bagni99Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TextReportCommented:
Add the following lines above the 'Modify Query section

Cheers, Andrew

   Select Case Me.Frame25
    Case 1: strSQL = " AND [Field]"
    Case 2: strSQL = " AND [Field]<=Date()"
    Case 3: strSQL = " AND [Field]<=Date()+30"
    Case 4: strSQL = " AND [Field] Between #" & Format(Me![Text38], "dd/mm/yyyy") & "# And #" & Format(Me![Text40], "dd/mm/yyyy") & "#"
   End Select

 
0
bagni99Author Commented:
Hello again Andrew,
I have applied what you suggested but now the query is ignoring the first option group.  

This is what I added to the code (above the Modify query section).

Select Case Me.Frame77
    Case 1: strSQL = " AND [Check or Currency]"
    Case 2: strSQL = " AND [Check or Currency]= ""Check"""
    Case 3: strSQL = " AND [Check or Currency]=""Currency"""
   End Select

Any ideas as to why it's ignoring the first option group.  
0
TextReportCommented:
OK You need to add strSQL & to the right of each = and I suspect that Case 1 should just be left alone ad you are probably wanting to select all records if option 1 is selected

Select Case Me.Frame77
    Case 1
    Case 2: strSQL = strSQL & " AND [Check or Currency]= ""Check"""
    Case 3: strSQL = strSQL & " AND [Check or Currency]=""Currency"""
End Select

Cheers, Andrew
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bagni99Author Commented:
Andrew, you are an absolute gem!!!

Thanks a million.

;-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.