Searching for info and displaying field name

[Gash13]

Is there a way to query a table for specific information and then just have it display the field name only, where it was located.

I can do the query part.

 

[MichaelRed]

Hmmmmmmmmmmmm ... mmmmmmmmmmmmm ... mmmmmmmmmmmmm

Yes?


BUT - BUT - BUT ... - BUT ... ????

I can conceieve of it but i could NOT suggest that such a procedure be applied to any reasonable table (or recordset).

Perhaps (probably/hpefully) someone will improve on this (A LOT).

For a given recordset, the corresponding *def object has a list of field names and ordinal positions, so one could -from the appropiate *def object- construct either a series of individual field queries for the 'token', or a simple loop to test each field of each record for the presence of hte token. I either instance, the field (mame and ordinal position) could be returned to the instantiating facillity.

The only 'wrinkle' in the process would be in dealing with the type casting of the value to find to the type of hte field being tested.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

Well, since no one else appears intrerested (in expending LOTS of cpu cycles), the following does get the info -at least on occassion-. Beware, it is just barely cobbled together and nearly (really comletely) un-tested, so I would expect a real app to need to add many more error traps, and possibly even redically alter them as well as the field data type coercion. Still, for the NOT faint of heart, it would show AN approach.

Public Function basFind(RecSet As String, Token As Variant) As String

    'To Return the FIELD name from the RecSet where the Token was found
    'Returns "Not In This RecordSet" if not found.
    'Returns the FIRST field name, and does not continue searching.
    'Returns "No Such Recordset" if RecSet is not a table or query in
    'the current database

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim tdf As DAO.TableDef

    Dim fldList() As String
    Dim strSQL As String
    Dim sqlFrom As String
    Dim sqlWhere As String
    Dim Idx As Integer
    Dim fld As Field
    Dim NotQryDef As Boolean

    On Error GoTo ErrHandler

    Set dbs = CurrentDb

    'Attempt to open as querydef
    Set qdf = dbs.QueryDefs(RecSet)
    'If there is an error(3265),
    'TRAP it so we know 'RecSet' is NOT a query(def)
    'Else, get the field list
    If (NotQryDef = False) Then
        ReDim fldList(qdf.Fields.Count - 1)
        Do While Idx < qdf.Fields.Count
            fldList(Idx) = qdf.Fields(Idx).Name
            Idx = Idx + 1
        Loop    'Idx
    End If

    'If it is a querydef, it SHOOULD NOT be a TableDef
    If (NotQryDef = True) Then
        Set tdf = dbs.TableDefs(RecSet)
        'If there is an error, Houston, We Have a Problem
        ReDim fldList(tdf.Fields.Count - 1)
        Do While Idx < tdf.Fields.Count
            fldList(Idx) = tdf.Fields(Idx).Name
            Idx = Idx + 1
        Loop    'Idx
    End If

    'Here, we Know that [RecSet] does exist, and
    'fldList is the list of fields in RecSet
    sqlFrom = &quot; From &quot; & RecSet & &quot; &quot;
    sqlWhere = &quot; = &quot; & Chr(34) & Token & Chr(34)
    Idx = 0
    Do While Idx < UBound(fldList)
        strSQL = &quot;Select CStr([&quot; & fldList(Idx) & &quot;]) as MyField &quot; & sqlFrom
        strSQL = strSQL & &quot; Where CStr([&quot; & fldList(Idx) & &quot;]) &quot; & sqlWhere
        strSQL = strSQL & &quot;;&quot;

        Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

        If (Not (rst.BOF = True And rst.EOF = True)) Then
            'Found
            basFind = fldList(Idx)
            Exit Do
        End If

        Idx = Idx + 1

    Loop

    GoTo NormExit

ErrHandler:
    Select Case Err
        Case Is = 94
            'Invalid use of Null
            Resume Next

        Case Is = 3078
            basFind = &quot;No Such Recordset&quot;

        Case Is = 3265
            'Item not found in this collection
            NotQryDef = True
            Resume Next

        End Select

NormExit:

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Gash13]

I think this will work. It works great when I manually put the "token" in. "=basfind("table","studentname&quot" I'm having a problem getting a form to put the token in. On the form I have a dropdowm box with the list of names (It pulls them from another table). I called the dropdown "token". In a text box I put
"=basfind("table",[token])" it just returned the first column heading. --which is incorrect.

Also.... I need it to continue searching the table instead of stopping after it finds the first one since the student could be in more than one field. (Its a relatively small table anyway)
I am very new at this. I started learning Access a week ago. I greatly appreciate all the help you have been.

 

[Gash13]

Also I notice it gets hung up on empty fields. Anyway to fix that?

Once again, thank you...