Private Sub cmddisplay_Click()
Dim NVar As Long
Dim vstring As String
Dim vfield1 As String
Dim vword As String
Dim db As Connection
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source=" & strdatasource
Set AdRecordset1 = New Recordset
AdRecordset1.Open "select Book,BookTitle,Chapter,TextData,Verse from BibleTable", db, adOpenStatic, adLockOptimistic
TxtWord.Enabled = False
List1.Clear
'Convert the value of TxtWord control to uppercase or lowercase letters/characters
vword = UCase(Left(Trim(TxtWord.Text), 1)) + (LCase(Right(Trim(TxtWord.Text), (Len(Trim(TxtWord.Text)) - 1))))
'Search operation
With AdRecordset1
NVar = 0
.MoveFirst
.Move (2)
Do While Not .EOF
If InStr(.Fields(3).Value, LCase(Trim(TxtWord.Text))) Or _
InStr(.Fields(3).Value, UCase(Trim(TxtWord.Text))) Or _
InStr(.Fields(3).Value, vword) Then
'Title value
vfield1 = .Fields(1).Value & Space(16 - Len(.Fields(1).Value))
'value of List Item
vstring = Format(Str(Val(.AbsolutePosition - 1)), "00000") + " " + Format(.Fields(0).Value, "00") + " " +
vfield1 + " " + Trim(.Fields(2).Value) + " " + Trim(.Fields(4).Value)
List1.AddItem vstring
NVar = NVar + 1
If NVar > 5000 Then
lbinfo1.Caption = " More then " + Str(NVar) + " items found. Select ..."
TxtWord.Enabled = True
Cmddisplay.Enabled = False
Exit Sub
End If
End If
.MoveNext
Loop
End With
'Display the number of the items found
If NVar > 0 Then
lbinfo1.Caption = " " + Str(NVar) + " items found. Select ..."
Else
lbinfo1.Caption = "no item found"
End If
TxtWord.Enabled = True
Cmddisplay.Enabled = False
Exit Sub
End Sub
|