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