search all field in a record

[kgcrowther]

I'm trying to set up a query to search every field in a record. There are many field!

Can I write a module that does this? I don't really know any VB, but I would think somehting like:

Dim F as Field; RS and RecordSet
Dim Str as String

For Each F in RS
find Str in F
End For

Exit

Does anyone know what I could do. Any help is great!

Kenneth

 

[charityg]

What are you going to do when you find the record(s)? It can be done, but it will be easier to direct you after I know where you plan to go with it.

~Charity

 

[kgcrowther]

The database contains many resumes. The way it's been set up is that for every possible section of a resume there has been a field created. There's probably about 80 fields. A skill like AutoCAD could be under one of the experience fields, schooling fields, classes taken fields, or notes fields.

A very simple way to simply search the resumes would just be to search for all the records that has any field which contains a certain string.

I hope this is possible because I don't want to reorganize the database or write huge queries if it can be avoided.

Any help is great. Thanks.

Kenneth

 

[charityg]

I don't claim that this is the best way to do it, but this is how I would set it up. (I'm ignoring the fact that your table design is flawed and hoping that you will learn your lesson)

this incorporates a "Search form" with a textbox for criteria entry and a command button that filters the form containing the data.

Private Sub cmdFilter_Click()

Dim db As Database
Dim rst As Recordset
Dim strFilter As String
Dim i As Integer


If IsNull(txtSearch) Then
    MsgBox "Please enter criteria"
    Exit Sub

Else

    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblName")

    strFilter = ""
    rst.MoveFirst

    Do Until rst.EOF
        For i = 0 To rst.Fields.Count - 1
            If rst.Fields(i) Like "*" & Me!txtSearch & "*" Then
                strFilter = strFilter & "[ID]=" & rst!ID & " or "
                Exit For
            End If
        Next i
        rst.MoveNext
    Loop

    If strFilter <> "" Then
        strFilter = Left(strFilter, Len(strFilter) - 4)
        Debug.Print strFilter
        DoCmd.OpenForm "issues", , , strFilter
    Else
        MsgBox "Criteria not Found!"
    End If


End If
End Sub

Basically what will happen is that the code will loop through all fields for a record until it finds a match. If it finds a match, it will add the record ID to the criteria string then move on to the next record.

I'm using a Like comparitive, if you want exact matching change this to
If rst.Fields(i) =& Me!txtSearch Then

Anyway, good luck, and let me know if you have any questions!

~Charity

[This message has been edited by charityg (edited 06-18-2001).]

 

[kgcrowther]

Thanks again Charity. You always seem to have just the right answer. I appreciate your help.

This was exactly what I needed so that I won't have to go through and reorganize all the data. I have learned my lesson and all the databases I've created recently have been completely relational, I promise.

Kenneth