searching and displaying Field name

[Gash13]

I need to be able to search a small table and return the field names that the data is found in. (I know this is highly unusual-- but I still need to do it for an existing datatbase) The script below was given to me in another post.--Which I'm greatful for.(At least I know it can be done) It was not tested and more of a concept on how to do it. It actually works well except for two problems. #1. I need it to search the whole table, the specific data could be in more than one cell and I need them all displayed.--the code below exits once the data is found the first time. #2 The code below skips to the next Field (column) if it encounters an empty cell. I need it to continue to check the whole column.

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


Any ideas on how to tweak it?

 

[MangroBongacello]

If I got it right...

#1. I think deleting Exit do will solve it.

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

#2. The query

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;

returns no records if no data found (rst.BOF = True And rst.EOF = True)

So it is OK to move to the next field. If you only have to retreive the field names, where data appears.

Mangro

 

[Gash13]

Yep, you were right. I removed &quot;Exit Do&quot; and changed &quot;basFind = fldList(Idx)&quot; to &quot;basFind = basFind & &quot; &quot; & fldList(Idx)&quot; Works great...

Also, once I got rid of the null values in the cells it Goes through the whole table.

Thanks for the Help..