Solved

Multiple multiselect list boxes and option groups

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

829 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