Solved

Multiple multiselect list boxes and option groups

Posted on 2003-11-30
4
395 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
  • 2
  • 2
4 Comments
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Andrew, you are an absolute gem!!!

Thanks a million.

;-)
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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…
Familiarize people with the process of utilizing SQL Server views 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 Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now