frmvcustomers.frm

This file came from Planet-Source-Code.com...the home millions of lines of source code You can view

Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim strCol As Variant

Private Sub cmbSearch_Click()
cmdFind_Click
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub cmdFind_Click()

Dim rsFind As Recordset
Dim strSQl As String
Dim SQL As String
Dim LItem As ListItem

'if there is nothing to search for then exit
If txtSearchText = "" Then
    Exit Sub
End If

ListView1.ListItems.clear

Set rsFind = New ADODB.Recordset

       SQL = "SELECT * FROM Customers"
       SQL = SQL & " WHERE CustomerID LIKE '*" & txtSearchText & "*'"



'make the search
        strSQl = "SELECT * FROM Customers WHERE "
        strSQl = strSQl & cmbSearch & " Like " & "'%" & txtSearchText & "%'"

        'SQL = strSQl & " WHERE language LIKE '*" & Text1.Text & "*'"
        'strSQl = strSQl & SQL
        Debug.Print strSQl
        Debug.Print SQL
        
'show the found records
    rsFind.Open strSQl, cnPatients, adOpenDynamic, adLockPessimistic
    
    
    Debug.Print rsFind.RecordCount
    Debug.Print rsFind.Fields.Count
    
    If Not (rsFind.BOF And rsFind.EOF) Then
        While rsFind.EOF = False
        Set LItem = ListView1.ListItems.add(, , rsFind(0))
        
        If rsFind(1) <> "" Then
            LItem.SubItems(1) = rsFind(1)
        End If
        
        If rsFind(2) <> "" Then
            LItem.SubItems(2) = rsFind(2)
        End If
        
        If rsFind(3) <> "" Then
            LItem.SubItems(3) = rsFind(3)
        End If
        
        If rsFind(4) <> "" Then
            LItem.SubItems(4) = rsFind(4)
        End If
        
        If rsFind(5) <> "" Then
            LItem.SubItems(5) = rsFind(5)
        End If
        
        If rsFind(6) <> "" Then
            LItem.SubItems(6) = rsFind(6)
        End If
        
        If rsFind(7) <> "" Then
            LItem.SubItems(7) = rsFind(7)
        End If
        
        If rsFind(8) <> "" Then
            LItem.SubItems(8) = rsFind(8)
        End If
        
        If rsFind(9) <> "" Then
            LItem.SubItems(9) = rsFind(9)
        End If
        
        If rsFind(10) <> "" Then
            LItem.SubItems(10) = rsFind(10)
        End If
        
        If rsFind(11) <> "" Then
            LItem.SubItems(11) = rsFind(11)
        End If
        
        If rsFind(12) <> "" Then
            LItem.SubItems(12) = rsFind(12)
        End If
       
        If rsFind(13) <> "" Then
            LItem.SubItems(13) = rsFind(13)
        End If

        If rsFind(14) <> "" Then
            LItem.SubItems(14) = rsFind(14)
        End If
        
        rsFind.MoveNext
        Wend
    End If
 
 
 'show number of records found
    Me.Caption = CStr(rsFind.RecordCount) & " records found"
    
 'close the recordset
    rsFind.Close
    
    
End Sub

Private Sub cmdSearch_Click()
Dim LItem As ListItem

FindItem = InputBox("Enter Customers ID", "Find Customers Details")

If Not FindItem = "" Then
Set LItem = ListView1.FindItem(FindItem, lvwText, lvwSubItem)
If LItem Is Nothing Then
    NotFound = True
End If
If NotFound Then
    MsgBox "Item not found", vbInformation, "Search Result"
Else
    LItem.EnsureVisible
    LItem.Selected = True
End If
End If


End Sub


Private Sub Command1_Click()
txtSearchText = ""
Form_Load
End Sub

Private Sub Form_Activate()
Me.WindowState = vbMaximized
txtSearchText.SetFocus
End Sub

Private Sub Form_Load()



Dim LItem As ListItem
Dim i As Integer


Dim rsPatientID As Recordset
Set rsPatientID = New ADODB.Recordset
Dim rsPatient As Recordset
Set rsPatient = New ADODB.Recordset

rsPatientID.Open "select * from Customers", cnPatients, adOpenDynamic, adLockPessimistic

rsPatient.Open "select * from Customers", cnPatients, adOpenDynamic, adLockPessimistic


For i = 0 To rsPatientID.Fields.Count - 1 Step 1
    cmbSearch.AddItem rsPatientID(i).name, i
Next i
rsPatientID.Close

ListView1.ListItems.clear

    If Not (rsPatient.BOF And rsPatient.EOF) Then
        While rsPatient.EOF = False
        Set LItem = ListView1.ListItems.add(, , rsPatient(0))
        
        If rsPatient(1) <> "" Then
            LItem.SubItems(1) = rsPatient(1)
        End If
        
        If rsPatient(2) <> "" Then
            LItem.SubItems(2) = rsPatient(2)
        End If
        
        If rsPatient(3) <> "" Then
            LItem.SubItems(3) = rsPatient(3)
        End If
        
        If rsPatient(4) <> "" Then
            LItem.SubItems(4) = rsPatient(4)
        End If
        
        If rsPatient(5) <> "" Then
            LItem.SubItems(5) = rsPatient(5)
        End If
        
        If rsPatient(6) <> "" Then
            LItem.SubItems(6) = rsPatient(6)
        End If
        
        If rsPatient(7) <> "" Then
            LItem.SubItems(7) = rsPatient(7)
        End If
        
        If rsPatient(8) <> "" Then
            LItem.SubItems(8) = rsPatient(8)
        End If
        
        If rsPatient(9) <> "" Then
            LItem.SubItems(9) = rsPatient(9)
        End If
        
        If rsPatient(10) <> "" Then
            LItem.SubItems(10) = rsPatient(10)
        End If
        
        If rsPatient(11) <> "" Then
            LItem.SubItems(11) = rsPatient(11)
        End If
        
        If rsPatient(12) <> "" Then
            LItem.SubItems(12) = rsPatient(12)
        End If
       
        If rsPatient(13) <> "" Then
            LItem.SubItems(13) = rsPatient(13)
        End If

        If rsPatient(14) <> "" Then
            LItem.SubItems(14) = rsPatient(14)
        End If
        
    
rsPatient.MoveNext
Wend
End If
rsPatient.Close
cmbSearch.Text = cmbSearch.List(0)


End Sub


Private Sub ListView1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)

If strCol <> ColumnHeader Then
    ListView1.SortOrder = lvwAscending
    ListView1.SortKey = ColumnHeader.Index - 1
    strCol = ColumnHeader
Else
    ListView1.SortOrder = lvwDescending
    ListView1.SortKey = ColumnHeader.Index - 1
    strCol = ""
End If


End Sub

Private Sub txtSearchText_Change()
cmdFind_Click
End Sub