'make a new project
'add a form: name = frmFind
'add a Textbox & a Checkbox
'add a Commandbutton
'add a Listbox
'make the neccesary adjustments for the database
'add the next code
'press F5 to run


Option Explicit
Dim db As Database

Sub Form_Load()

    Set db=OpenDataBase("c:\program files\devstudio\vb\biblio.mdb")
    Text1.Text = ""

End Sub

Sub Command1_Click()

    If Text1.Text = "" Then Exit Sub
    Call ZoekFindFirst

End Sub

Public Sub ZoekFindFirst()

    Dim rs As Recordset
    Dim strSQL As String
    Dim strDummy As String
    Dim strZoek As String
    Dim blnFirst As Boolean
    Dim intX as Integer
    
    strZoek = frmFind.Text1.Text
    
    'open the table
    strSQL = "SELECT * FROM Titles"
        
    'make the SQL WHERE 
    'only TextFields to be searched!
    Set rs = db.OpenRecordset(strSQL)
    blnFirst = True
    For intX = 0 To rs.Fields.Count - 1
        If rs.Fields(intX).Type = dbText Then
            If frmFind.Check1.Value = 1 Then
                If blnFirst Then
                    strDummy = strDummy & " WHERE (" & rs.Fields(intX).Name & " = '" & strZoek & "')"
                    blnFirst = False
                Else
                    strDummy = strDummy & " OR (" & rs.Fields(intX).Name & " = '" & strZoek & "')"
                End If
            Else
                If blnFirst Then
                    strDummy = strDummy & " WHERE (" & rs.Fields(intX).Name & " LIKE '*" & strZoek & "*')"
                    blnFirst = False
                Else
                    strDummy = strDummy & " OR (" & rs.Fields(intX).Name & " LIKE '*" & strZoek & "*')"
                End If
            End If
        End If
    Next intX
            
    strSQL = strSQL & strDummy
    'show the found records
    Set rs = db.OpenRecordset(strSQL)
    If Not (rs.BOF And rs.EOF) Then
        Do While Not rs.EOF
            List1.AddItem rs.Fields(0).Value 
            rs.MoveNext
        Loop
    Else
        MsgBox "No records found!", vbExclamation
    End If
    rs.Close
    
End Sub
Return