Find a value in an unknown field 1

[synapsevampire]

Using an MS Access 2002 database.

I wish to determine which field contains certain values, so I need to be able to search all of the appropriate field types for a specific value.

This is simple enough in SQL Server, Oracle, etc., by using the system tables.

How might I do this within Access?

So if the user has entered XXZXX in a name field, how can I readily determine which field was populated by this action?

TIA,

-k

 

[Remou]

If you are refering to a form, you can check the SourceTable property. A few notes:

Set rs = frm.RecordsetClone
For Each fld In rs.Fields
    If InStr(tdflist, rs(fld.Name).SourceTable) = 0 Then
        Set tdf = db.TableDefs(rs(fld.Name).SourceTable)
        tdflist = tdflist & "," & rs(fld.Name).SourceTable
    End If
Next

 

[synapsevampire]

I was referrring to just querying the database to learn if a string exists in any field within any table, and return the field and table names.

I'm not interested in creating forms unless it's unavoidable.

Your code looks like it searches the fields in a form.

-k

 

[Remou]

You can look through the tables and fields collections.

Sub FindInFields()
'Needs reference to Microsoft DAO x.x Object Library
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As Object
Dim fld As Object

Set db = CurrentDb
strFind = "Smith"
For Each tdf In db.TableDefs
    'Exclude system tables and linked tables
    If Left(tdf.Name, 4) <> "MSys" And tdf.Connect = "" Then
        Set rs = db.OpenRecordset("Select * From [" & tdf.Name & "]")
        For Each fld In rs.Fields
            If fld.Type = dbText Then
                rs.FindFirst "[" & fld.Name & "] Like '*" & strFind & "*'"
                If Not rs.NoMatch Then
                    Do While Not rs.NoMatch
                        Debug.Print tdf.Name; "   "; fld.Name
                        rs.FindNext "[" & fld.Name & "] Like '*" & strFind & "*'"
                    Loop
                End If
            End If
        Next
    End If
Next
End Sub

This has come up before, it may be possible to find other examples.

 

[synapsevampire]

This looks like what I need, thanks.

I'm not much for Access coding, how do I create the "reference to Microsoft DAO x.x Object Library"?

-k

 

[PHV]

In the VBE menu Tools -> References ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886