Solved

Multiple multiselect list boxes and option groups

Posted on 2003-11-30
4
399 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

749 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