Solved

Multiple multiselect list boxes and option groups

Posted on 2003-11-30
4
401 Views
Last Modified: 2009-07-29
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
0
Comment
Question by:bagni99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 28

Expert Comment

by:TextReport
ID: 9849049
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
 

Author Comment

by:bagni99
ID: 9849205
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
 
LVL 28

Accepted Solution

by:
TextReport earned 500 total points
ID: 9849216
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
 

Author Comment

by:bagni99
ID: 9849252
Andrew, you are an absolute gem!!!

Thanks a million.

;-)
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question