VB 6.0 - How to filter year in two combo box. and display in listview

Asked By kiki2012 ki on 26-Feb-13 01:03 AM
Dear Sir,
            I have two combo box which is populate from access 2003 database . So how to filter the  year from one combo box with another combo box and the date display in the listview belong to the filter year only. I have tried to filter using between in query but it just display only the first combo box year only. I want it to display From which year to which year as filter. Here is the code sir .Kindly sir see what i missed.

Option Explicit
 
'----------------------------------- Populate Combo Box ---------------------------------------
 
Sub populatecombo()
 
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim str As String
 
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Test.mdb;Persist Security Info=False;Jet OLEDB:Database Password=1234"
 
str = "select Distinct (year(DateofBirth)) as doeYear from test"
rs.Open str, con, adOpenForwardOnly, adLockReadOnly
 
   Dim x As Integer
  While rs.EOF = False
   With ComboMyYear
    .AddItem rs!doeYear
    End With
    x = x + 1
    rs.MoveNext
  Wend
  ComboMyYear.ListIndex = 0
 
  rs.Close
  con.Close
  Set rs = Nothing
  Set con = Nothing
   
   
End Sub
 
Sub populatecomboFrom()
 
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim str As String
 
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Test.mdb;Persist Security Info=False;Jet OLEDB:Database Password=1234"
 
str = "select Distinct (year(DateofBirth)) as doeYear from test"
rs.Open str, con, adOpenForwardOnly, adLockReadOnly
 
   Dim x As Integer
  While rs.EOF = False
   With ComboFrom
    .AddItem rs!doeYear
    End With
    x = x + 1
    rs.MoveNext
  Wend
  ComboFrom.ListIndex = 0
   
  rs.Close
  con.Close
  Set rs = Nothing
  Set con = Nothing
   
   
End Sub
 
Sub populatecomboUpto()
 
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim str As String
 
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Test.mdb;Persist Security Info=False;Jet OLEDB:Database Password=1234"
 
str = "select Distinct (year(DateofBirth)) as doeYear from test"
rs.Open str, con, adOpenForwardOnly, adLockReadOnly
 
  Dim x As Integer
  While rs.EOF = False
   With ComboUpto
    .AddItem rs!doeYear
    End With
    x = x + 1
    rs.MoveNext
  Wend
  ComboUpto.ListIndex = 0
   
  rs.Close
  con.Close
  Set rs = Nothing
  Set con = Nothing
   
   
End Sub
 
Private Sub cmdNewTable_Click()
NewTableForm.Show
End Sub
 
 
Private Sub cmdSort_Click()
 
Dim rsview As ADODB.Recordset
Dim LI As ListItem
Dim x As Integer
Dim number As Integer
    
      
    If NameCheck.Value = 1 Then
      number = 1
      NameText.Enabled = True
       
      Else
       
       If SurnameCheck.Value = 1 Then
         number = 2
         SurnameText.Enabled = True
          
         Else
          
          If AddressCheck.Value = 1 Then
          number = 3
          AddressText.Enabled = True
           
          Else
           
            If DistrictCheck.Value = 1 Then
            number = 4
            DistrictText.Enabled = True
              
             Else
             
              If StateCheck.Value = 1 Then
              number = 5
              StateText.Enabled = True
               
              Else
                If OccupationCheck.Value = 1 Then
                  number = 6
                  OccupationText.Enabled = True
                   
                  Else
                   
                  If YearCheck.Value = 1 Then
                    number = 7
                    YearCheck.Enabled = True
                     
                     Else
                      
                    If AllRecordCheck.Value = 1 Then
                    number = 8
                     
                    Else
                     
                    If YearFromCheck.Value = 1 Then
                      number = 9
                      YearFromCheck.Enabled = True
                       
                      Else
                       
                      If YearUptoCheck.Value = 1 Then
                        number = 10
                        YearUptoCheck.Enabled = True
                         
                        Else
                         
                        If (YearFromCheck.Value = 1 And YearUptoCheck.Value = 1) Then
                        number = 11
                        YearFromCheck.Enabled = True
                        YearUptoCheck.Enabled = True
                        End If
                      End If
                    End If
                    End If
                   End If
                    
                End If
                 
              End If
            End If
          End If
      End If
    End If
       
   ListView1.ListItems.Clear
 
 
 Set rsview = New ADODB.Recordset
  
connect
 
Select Case number
 
Case 1:
   If NameText.Text = "" Then
     MsgBox " Please enter Initial Name : "
     Exit Sub
    Else
              
      rsview.Open "SELECT * FROM test where (Name) like '" & NameText.Text & "%'ORDER BY Name ", con, adOpenStatic, adLockOptimistic
   
        If rsview.BOF = True Or rsview.EOF = True Then
         MsgBox " No Record Found  :   " & Me.NameText.Text
           NameText.Text = ""
          Exit Sub
        Else
     
   
     
    Do Until rsview.EOF = True
     
      Set LI = ListView1.ListItems.Add(, , rsview(0))
       
      ListView1.ListItems.Add
      For x = 1 To 8
             
      LI.SubItems(x) = rsview(x)
       
      Next x
        
      rsview.MoveNext
    Loop
     
    End If
  End If
   
rsview.Close
con.Close
Set rsview = Nothing
Set con = Nothing
 
Case 2:
   
  MsgBox " To be add later"
   
Case 3:
   MsgBox "To be add later"
 
Case 4:
 
   MsgBox "To be add later"
 
Case 5:
   
   MsgBox "To be add later"
    
Case 6:
    
   MsgBox "To be add later"
    
Case 7:
  If ComboMyYear.Text = "" Then
     MsgBox " Please Choose  : "
     ComboMyYear.Refresh
     Exit Sub
      
    Else
     
    rsview.Open "SELECT  * FROM test where Year(DateofBirth) like '" & ComboMyYear.Text & "%'ORDER BY DateofBirth ", con, adOpenStatic, adLockOptimistic
   
        If rsview.BOF = True Or rsview.EOF = True Then
         MsgBox " No Record Found  :   " & Me.ComboMyYear.Text
           ComboMyYear.Clear
          Exit Sub
        Else
     
   
     
    Do Until rsview.EOF = True
     
      Set LI = ListView1.ListItems.Add(, , rsview(0))
      ListView1.Refresh
      For x = 1 To 8
             
      LI.SubItems(x) = rsview(x)
       
      Next x
      rsview.MoveNext
     Loop
     
    End If
  End If
   
rsview.Close
con.Close
Set rsview = Nothing
Set con = Nothing
 
Case 8:
          
  rsview.Open "SELECT * FROM test ", con, adOpenStatic, adLockOptimistic
    
   If rsview.BOF = True Or rsview.EOF = True Then
    MsgBox " Record Was Damage  :   "
    Exit Sub
     Else
    
    Do Until rsview.EOF = True
     
      Set LI = ListView1.ListItems.Add(, , rsview(0))
     
      For x = 1 To 8
             
      LI.SubItems(x) = rsview(x)
       
      Next x
       rsview.MoveNext
    Loop
     
    End If
   
rsview.Close
con.Close
Set rsview = Nothing
Set con = Nothing
 
Case 9:
 
  If ComboFrom.Text = "" Then
     MsgBox " Please Choose  : "
     ComboFrom.Refresh
     Exit Sub
      
    Else
     
    rsview.Open "SELECT * FROM test where Year(DateofBirth) like '" & ComboFrom.Text & "%'ORDER BY DateofBirth ", con, adOpenStatic, adLockOptimistic
   
        If rsview.BOF = True Or rsview.EOF = True Then
         MsgBox " No Record Found  :   " & Me.ComboFrom.Text
           ComboFrom.Clear
          Exit Sub
        Else
     
   
     
    Do Until rsview.EOF = True
     
      Set LI = ListView1.ListItems.Add(, , rsview(0))
      ListView1.Refresh
      For x = 1 To 8
             
      LI.SubItems(x) = rsview(x)
       
      Next x
      rsview.MoveNext
     Loop
     
    End If
  End If
   
rsview.Close
con.Close
Set rsview = Nothing
Set con = Nothing
 
Case 10:
 
  If ComboUpto.Text = "" Then
     MsgBox " Please Choose  : "
     ComboUpto.Refresh
     Exit Sub
      
    Else
     
    rsview.Open "SELECT * FROM test where Year(DateofBirth) like '" & ComboUpto.Text & "%'ORDER BY DateofBirth ", con, adOpenStatic, adLockOptimistic
   
        If rsview.BOF = True Or rsview.EOF = True Then
         MsgBox " No Record Found  :   " & Me.ComboUpto.Text
           ComboUpto.Clear
          Exit Sub
        Else
     
   
     
    Do Until rsview.EOF = True
     
      Set LI = ListView1.ListItems.Add(, , rsview(0))
      ListView1.Refresh
      For x = 1 To 8
             
      LI.SubItems(x) = rsview(x)
       
      Next x
       
      rsview.MoveNext
     Loop
     
    End If
  End If
   
rsview.Close
con.Close
Set rsview = Nothing
Set con = Nothing
 
Case 11:
 
  If YearFromCheck.Value = 1 & YearUptoCheck.Value = 1 Then
     ComboFrom.Enabled = True
     ComboUpto.Enabled = True
      
   rsview.Open "SELECT DateofBirth FROM test where DateofBirth BETWEEN '" & ComboFrom.Text & "' AND '" & ComboUpto.Text & "'", con, adOpenStatic, adLockOptimistic
  'rsview.Open "Select DateofBirth FROM test where DateofBirth >= '" & Format(" # ", "mm/dd/yyyy") & "' and DateofBirth <= '" & Format(" # ", "mm/dd/yyyy") & "'"
    
        If rsview.BOF = True Or rsview.EOF = True Then
         MsgBox " No Record Found  :   " & Me.ComboFrom.Text, Me.ComboUpto.Text
           ComboFrom.Clear
           ComboUpto.Clear
          Exit Sub
        Else
     
   
     
    Do Until rsview.EOF = True
     
      Set LI = ListView1.ListItems.Add(, , rsview(0))
      ListView1.Refresh
      For x = 1 To 8
             
      LI.SubItems(x) = rsview(x)
       
      Next x
       
      rsview.MoveNext
     Loop
      
    End If
    End If
     
  rsview.Close
  con.Close
  Set rsview = Nothing
  Set con = Nothing
   
End Select
        
End Sub
 
Private Sub cmdClear_Click()
 
'-------------------------------------DEACTIVATE TEXTBOX -------------------------
  NameText.Enabled = False
  SurnameText.Enabled = False
  AddressText.Enabled = False
  DistrictText.Enabled = False
  StateText.Enabled = False
  OccupationText.Enabled = False
  ComboMyYear.Enabled = False
  RegistrationText.Enabled = False
   
'--------------------------------------- CLEAR TEXTBOX ---------------------------
 
  NameText.Text = ""
  SurnameText.Text = ""
  AddressText.Text = ""
  DistrictText.Text = ""
  StateText.Text = ""
  OccupationText.Text = ""
  ComboMyYear.Clear
  RegistrationText.Text = ""
  ComboFrom.Clear
  ComboUpto.Clear
 
'--------------------------------------CLEAR CHECKBOX ------------------------------
 
  NameCheck.Value = 0
  SurnameCheck.Value = 0
  AddressCheck.Value = 0
  DistrictCheck.Value = 0
  StateCheck.Value = 0
  OccupationCheck.Value = 0
  YearCheck.Value = 0
  RegistrationCheck.Value = 0
  AllRecordCheck.Value = 0
  YearFromCheck.Value = 0
  YearUptoCheck.Value = 0
   
   
'------------------------------------ CLEAR LISTVIEW ------------------------------
 
  ListView1.ListItems.Clear
    
End Sub
 
 
'------------------------------------CHECKBOX ACTIVATE AND DEACTIVATE -----------------------------------
 
Private Sub NameCheck_Click()
 If NameCheck.Value = 1 Then
  NameText.Enabled = True
  NameText.BackColor = vbWhite
  Exit Sub
   
  Else
   
   If NameCheck.Value = 0 Then
  NameText.Enabled = False
  NameText.BackColor = &H80000000
  Exit Sub
 End If
End If
 
End Sub
 
Private Sub SurnameCheck_Click()
 If SurnameCheck.Value = 1 Then
  SurnameText.Enabled = True
  SurnameText.BackColor = vbWhite
  Exit Sub
   
  Else
   
  If SurnameCheck.Value = 0 Then
  SurnameText.Enabled = True
  SurnameText.BackColor = &H80000000
  Exit Sub
   
  End If
End If
End Sub
 
Private Sub AddressCheck_Click()
  If AddressCheck.Value = 1 Then
  AddressText.Enabled = True
  AddressText.BackColor = vbWhite
  Exit Sub
   
  Else
   
  If AddressCheck.Value = 0 Then
  AddressText.Enabled = True
  AddressText.BackColor = &H80000000
  Exit Sub
   
  End If
End If
End Sub
 
Private Sub DistrictCheck_Click()
   If DistrictCheck.Value = 1 Then
  DistrictText.Enabled = True
  DistrictText.BackColor = vbWhite
  Exit Sub
   
  Else
   
  If DistrictCheck.Value = 0 Then
  DistrictText.Enabled = True
  DistrictText.BackColor = &H80000000
  Exit Sub
   
  End If
End If
End Sub
 
Private Sub StateCheck_Click()
  If StateCheck.Value = 1 Then
  StateText.Enabled = True
  StateText.BackColor = vbWhite
  Exit Sub
   
  Else
   
  If StateCheck.Value = 0 Then
  StateText.Enabled = True
  StateText.BackColor = &H80000000
  Exit Sub
   
  End If
End If
End Sub
 
Private Sub OccupationCheck_Click()
  If OccupationCheck.Value = 1 Then
  OccupationText.Enabled = True
  OccupationText.BackColor = vbWhite
  Exit Sub
   
  Else
   
  If OccupationCheck.Value = 0 Then
  OccupationText.Enabled = True
  OccupationText.BackColor = &H80000000
  Exit Sub
   
  End If
End If
End Sub
 
Private Sub YearCheck_Click()
  If YearCheck.Value = 1 Then
  ComboMyYear.Enabled = True
  ComboMyYear.BackColor = vbWhite
  populatecombo
  Exit Sub
   
  Else
   
  If YearCheck.Value = 0 Then
  ComboMyYear.Enabled = True
  ComboMyYear.BackColor = &H80000000
  ComboMyYear.Clear
  Exit Sub
   
  End If
End If
End Sub
 
Private Sub RegistrationCheck_Click()
  If RegistrationCheck.Value = 1 Then
  RegistrationText.Enabled = True
  RegistrationText.BackColor = vbWhite
  Exit Sub
   
  Else
   
  If RegistrationCheck.Value = 0 Then
  RegistrationText.Enabled = True
  RegistrationText.BackColor = &H80000000
  Exit Sub
   
   End If
End If
End Sub
 
 
Private Sub YearFromCheck_Click()
  If YearFromCheck.Value = 1 Then
  ComboFrom.Enabled = True
  ComboFrom.BackColor = vbWhite
  populatecomboFrom
  Exit Sub
   
  Else
   
  If YearFromCheck.Value = 0 Then
  ComboFrom.Enabled = True
  ComboFrom.BackColor = &H80000000
  ComboFrom.Clear
  Exit Sub
   
   End If
End If
End Sub
 
Private Sub YearUptoCheck_Click()
  If YearUptoCheck.Value = 1 Then
  ComboUpto.Enabled = True
  ComboUpto.BackColor = vbWhite
  populatecomboUpto
  Exit Sub
   
  Else
   
  If YearUptoCheck.Value = 0 Then
  ComboUpto.Enabled = True
  ComboUpto.BackColor = &H80000000
  ComboUpto.Clear
  Exit Sub
   
   End If
End If
End Sub