Referencing a specific table field in a VBA routine (1 Viewer)

[KevinWilcox]

Hi, I have found some code, appended, written by Dev Ashish many years ago that allows me to search all tables for a string match. This is great but I now need to return the value of one field from the results. Longhand, what I want to do is specify that if the result comes from Table X, return the value of field Y.
I've been able to figure out how to do the 'if it comes from table X' bit, but I'm stuck on 'return the value of field Y'. I've tried all manner of things to refer to it, e.g. fieldname(0), columnnames(0) but none of them work. Can anyone tell me what that line needs to be?


Define a new class call 'SearchResults' and put these members in it.

' ***
Public TableName As String
Public ColumnNames As New VBA.Collection
Public ResultRows As New VBA.Collection
' ***

Put these 2 procs in a standard module and run 'TestSearchAllTables'. The
actual work is done in SearchAllTables routines. You can modify the SQL
in it so that only one column is searched against if you need to figure
out which particular column contained the string instead of the table.

' ***
Sub TestSearchAllTables()
Dim results As VBA.Collection
Dim result As SearchResults
Dim i As Integer, j As Integer, k As Integer

Set results = SearchAllTables("An")
If results.Count > 0 Then
For i = 1 To results.Count
Set result = results.item(i)
With result
Debug.Print "***************"
Debug.Print "Result found in: " & .TableName
Debug.Print "***************"
For j = 1 To .ColumnNames.Count
Debug.Print .ColumnNames.item(j),
Next
Debug.Print
Debug.Print "---------------------"
For j = 1 To .ResultRows.Count
For k = 0 To .ColumnNames.Count - 1
Debug.Print .ResultRows.item(j)(k),
Next
Next
Debug.Print
End With
Next
Else
Debug.Print "No records found"
End If
End Sub

Function SearchAllTables(criteria As String) As VBA.Collection
Dim rs As dao.Recordset
Dim tdf As dao.TableDef
Dim db As dao.Database
Dim fld As dao.Field
Dim sql As String, i As Integer, j As Integer
Dim doInclude As Boolean
Dim results As VBA.Collection
Dim item As SearchResults, items() As String
On Error GoTo ErrHandler

Set db = CurrentDb
Set results = New VBA.Collection

For Each tdf In db.TableDefs

doInclude = (Not CBool(tdf.Attributes And _
dbSystemObject)) And _
(Not CBool(tdf.Attributes And dbHiddenObject))
If (doInclude) Then
sql = "select * from [" & tdf.Name & _
"] where "
For Each fld In tdf.Fields
sql = sql & "[" & fld.Name & "] like '*" & _
criteria & "*' or "
Next
sql = Left$(sql, Len(sql) - 3)
Set rs = db.OpenRecordset(sql)

If (rs.RecordCount > 0) Then
Set item = New SearchResults

item.TableName = tdf.Name
rs.MoveFirst
ReDim items(0 To rs.Fields.Count - 1)
For i = 0 To rs.RecordCount - 1
For j = 0 To rs.Fields.Count - 1
items(j) = rs.Fields(j).Value & vbNullString
Next
item.ResultRows.Add items
rs.MoveNext
Next
For j = 0 To rs.Fields.Count - 1
item.ColumnNames.Add rs.Fields(j).Name
Next
results.Add item:=item, Key:=tdf.Name
End If
rs.Close
End If
Next

Set SearchAllTables = results

Set tdf = Nothing
Set fld = Nothing
Set rs = Nothing
Set db = Nothing

Exit Function
ErrHandler:
With Err
MsgBox "Error: " & .Number & vbCrLf & _
.Description, vbOKOnly Or vbCritical, "SearchAllTables"
End With
Set tdf = Nothing
Set fld = Nothing
Set rs = Nothing
Set db = Nothing
End Function

' ***

-- Dev

 

[theDBguy]

Hi. You might consider posting a sample db for testing. Just a thought...

 

[KevinWilcox]

I'd be embarrassed to do so; I'm a rank amateur, trying to do something quick'n'dirty and both the structure and coding is scrappy, and this is the very last problem that needs solving!

 

[theDBguy]

I'd be embarrassed to do so; I'm a rank amateur, trying to do something quick'n'dirty and both the structure and coding is scrappy, and this is the very last problem that needs solving!

I see. I just thought it would be easier to assist with something to try out.

 

[KevinWilcox]

The question, boiled down, is how do I reference a specific table field in VBA? If I use something like fieldname(0) I get an error as it's looking for a function.

 

[theDBguy]

The question, boiled down, is how do I reference a specific table field in VBA? If I use something like fieldname(0) I get an error as it's looking for a function.

In VBA, you can create a TableDef object and reference all its members, including the Fields collection.

Are you trying to retrieve the data stored in a field? If so, you can also use domain functions, like DLookup().

 

[KevinWilcox]

In VBA, you can create a TableDef object and reference all its members, including the Fields collection.

Are you trying to retrieve the data stored in a field? If so, you can also use domain functions, like DLookup().

Probably best if I describe this. Bear in mind that I am simplifying here, to avoid it becoming too long winded. I have 3 tables with an hierarchical, one-to-many relationship. They relate through foreign keys etc. Each table contains at least one text field. The first table captures topics so imagine tbl_Topics with just two fields, Topic_ID (PK) and topic_desc. A typical topic description could be "provide an audiology service". The next table captures issues, hence tbl_Issues and a one-to-many relationship to tbl_Topics; picture 3 fields; Issue_ID (PK); Topic_ID (FK); Issue_desc. A typical issue description could be "no audiologists available for hire". The third table captures notes related to the issues, again one-to-many, so picture tbl_Notes with three fields, Note_ID (PK), Issue_ID (FK); Note_desc. A typical note could be "talked to the audiologists next door who may be able to help". Imagine that, over time, I will have numerous Topics, Issues and Notes that mention audiology but which are not necessarily connected. In six months time there may be a topic called "open a microsuction service" and, buried in the notes may be a note saying "local audiologist says they can do it". It has to be designed for this because the nature of what we do doesn't lend itself to neat pidgeon holing or categorisation.

I want to be able to do a string search of all tables using, in this instance "*aud*" as the string, to identify any record with this in a text field. But while that example is fairly specific, it could be a search for a single word that someone remembers was used in that topic, a drug name or surgical procedure, or even the name of a doctor or nurse.

The routine I posted finds all the matches, but returns the content of all the fields in the relevant tables. What I want is to narrow it down so that, if there is a match in, for instance, tbl_Topics, it returns the Topic_ID and passes that to a declared variable (which I would then turn into a filter string through concatenation or somesuch). I've amended the code by adding an 'IF' sequence to get it to only return the matches from tbl_Topics, but can't figure out how to now get it to give me the Topic_ID. I'm sure this is perfectly easy, I just don't know how to write that line. I think this is probably a syntax issue and would really appreciate help, as I'm now resorting to repeated, unsuccessful guesses at how to reference the field from the returned collection. In Dev's intro he said this can be done, but probably assumed any user would know how.

 

[MajP]

As @theDBguy said you can use a Dlookup to return a value from the table.

Public Function GetTopicID(strSearch as string) as Long
  GetTopicID = nz(Dlookup("TopicID","TblTopics","Description like '*" & strSearch & "*'"),0)
end function

This function will return 0 if nothing is found . If you pass in "aud" it will return the first topic like "aud". If you want to see all topics with AUD then you would have to build a form to show that then refine you search from there.

 

[MajP]

So you could build a form where you search for AUD.
It would show all topics with AUD. Under each topic would be the related Issues and Notes.

If you want to search all topics, issues, and notes then you will have to come up with a user interface that you think suports this.

You could have a main Form
Subform 1 would show all Topics with AUD
Subform 2 could show all Issues with AUD
Subform 3 could show all Notes with AUD.

It all depends on the GUI that you want.

 

[KevinWilcox]

If you want to see all topics with AUD then you would have to build a form to show that then refine you search from there.

The code does actually return all matches from every table. The code is free-standing, if you paste it in as outlined you'll see the results via the immediate window.

I know I could do this via queries and forms etc but Dev's is such an elegant solution I'd rather use that, if anyone can tell me how to refer to the field.

 

[GPGeorge]

I have a long-standing Access development principle:

Whatever does work is useful.
Whatever one thinks SHOULD work, but doesn't, is not useful.

 

[June7]

Note for future: please post code between CODE tags to retain indentation and readability.

I got code to run and provide the immediate window output.

Not really understanding what you want. The output includes record ID so the code is referencing field.

If there are multiple matches then there are multiple IDs, possibly from multiple tables. Could have these data matches save to a temp table or add to a combobox list instead of just kicking out to immediate window.

Or do you want to build a filter criteria with IN() operator that could be concatenated into an SQL statement or used to filter form/report? I can do that easily for a single table and set a global variable but since you want to search multiple tables, where would you like this output to go? Also, there is possibility IN() array list result could be too long.

Did another test and this time match should be on 3 records instead of just 1. Output only includes first record. Need to add lines just after opening recordset so RecordCount will be correct:
rs.MoveLast
rs.MoveFirst

Debug.Print line needs to be moved up into For loop.

 

[KevinWilcox]

Note for future: please post code between CODE tags to retain indentation and readability

Yes, apologies for that, it's quite a while since I did any coding and had forgotten that etiquette. Hopefully I've got it right below.

You'll have seen in the immediate window that, for every match, it gives you the field names (columns) and values for every field. All I want to do is narrow that down to just one particular field, the first one, which happens to be the PK of the respective table. I'm not looking for the first or last record or in any way to reduce the number of matches found - I need all of them! If you look at my line (which doesn't work) 'myID = rs.Fields(0)', it's whatever is correct instead of rs.fields(0) that I'm trying to solve. I expect it's something utterly simple and obvious. I've tried all sorts of things, like FieldName(0), ColumnName etc etc. But anything with (0) fails as it's expecting a function between the brackets.

Private Sub cmd_Search_Click()
Dim results As VBA.Collection
Dim result As SearchResults
Dim i As Integer, j As Integer, k As Integer
Dim myID As String
Dim fld As dao.Field
 
Set results = SearchAllTables(Me.txt_Search)
If results.Count > 0 Then
For i = 1 To results.Count
Set result = results.item(i)
With result
If .TableName = "tbl_items" Then
Debug.Print "***************"
Debug.Print "Result found in: " & .TableName
Debug.Print "***************"
For j = 1 To .ColumnNames.Count
Debug.Print .ColumnNames.item(j),
Next
Debug.Print
Debug.Print "---------------------"
For j = 1 To .ResultRows.Count
For k = 0 To .ColumnNames.Count - 1
Debug.Print .ResultRows.item(j)(k),
'myID = rs.Fields(0) this is where I'm stuck. I need to specify for it to return the value of Item_ID in tbl_Items
Debug.Print myID ' and pass it to this variable
Next
Next
Debug.Print
End If
End With
 
Next
Else
Debug.Print "No records found"
End If
End Sub

 

[June7]

Your code is not indented. Is it like this in your file? Makes it harder to follow.

There is no recordset declared or set so certainly can't reference a recordset field.

    If k = 0 Then
        myID = .ResultRows.item(j)(k)
        Debug.Print myID
    End If
Next
Debug.Print
Next
End If

 

[Gasman]

At least indent the procedure?

I cannot get past

Dim result As SearchResults

as user defined type not defined

Sub TestSearchAllTables()
    Dim results As VBA.Collection
    Dim result As SearchResults
    Dim i As Integer, j As Integer, k As Integer

    Set results = SearchAllTables("An")
    If results.Count > 0 Then
        For i = 1 To results.Count
            Set result = results.item(i)
            With result
                Debug.Print "***************"
                Debug.Print "Result found in: " & .TableName
                Debug.Print "***************"
                For j = 1 To .ColumnNames.Count
                    Debug.Print .ColumnNames.item(j),
                Next
                Debug.Print
                Debug.Print "---------------------"
                For j = 1 To .ResultRows.Count
                    For k = 0 To .ColumnNames.Count - 1
                        Debug.Print .ResultRows.item(j)(k),
                    Next
                Next
                Debug.Print
            End With
        Next
    Else
        Debug.Print "No records found"
    End If
End Sub

Function SearchAllTables(criteria As String) As VBA.Collection
    Dim rs As DAO.Recordset
    Dim tdf As DAO.TableDef
    Dim db As DAO.Database
    Dim fld As DAO.Field
    Dim sql As String, i As Integer, j As Integer
    Dim doInclude As Boolean
    Dim results As VBA.Collection
    Dim item As SearchResults, items() As String
    On Error GoTo ErrHandler

    Set db = CurrentDb
    Set results = New VBA.Collection

    For Each tdf In db.TableDefs

        doInclude = (Not CBool(tdf.Attributes And _
                               dbSystemObject)) And _
                               (Not CBool(tdf.Attributes And dbHiddenObject))
        If (doInclude) Then
            sql = "select * from [" & tdf.Name & _
                  "] where "
            For Each fld In tdf.Fields
                sql = sql & "[" & fld.Name & "] like '*" & _
                      criteria & "*' or "
            Next
            sql = Left$(sql, Len(sql) - 3)
            Set rs = db.OpenRecordset(sql)

            If (rs.RecordCount > 0) Then
                Set item = New SearchResults

                item.TableName = tdf.Name
                rs.MoveFirst
                ReDim items(0 To rs.Fields.Count - 1)
                For i = 0 To rs.RecordCount - 1
                    For j = 0 To rs.Fields.Count - 1
                        items(j) = rs.Fields(j).Value & vbNullString
                    Next
                    item.ResultRows.Add items
                    rs.MoveNext
                Next
                For j = 0 To rs.Fields.Count - 1
                    item.ColumnNames.Add rs.Fields(j).Name
                Next
                results.Add item:=item, Key:=tdf.Name
            End If
            rs.Close
        End If
    Next

    Set SearchAllTables = results

    Set tdf = Nothing
    Set fld = Nothing
    Set rs = Nothing
    Set db = Nothing

    Exit Function
ErrHandler:
    With Err
        MsgBox "Error: " & .Number & vbCrLf & _
               .Description, vbOKOnly Or vbCritical, "SearchAllTables"
    End With
    Set tdf = Nothing
    Set fld = Nothing
    Set rs = Nothing
    Set db = Nothing
End Function

' ***

 

[KevinWilcox]

If k = 0 Then myID = .ResultRows.item(j)(k)

Perfect, much appreciated. I wouldn't have got there myself. Yes, it wasn't indented, just copied and pasted off the web this morning. I do indent normally, honest! For the benefit of others, I didn't need the subsequent three lines, just the first one.

 

[KevinWilcox]

I cannot get past

All I can say is that I've used code written by Dev Ashish on and off over the years. His code is always clever stuff (to me anyway) which I don't necessarily fully understand, but it always works and that's good enough for me.

And I will indent, promise.

 

[Gasman]

All I can say is that I've used code written by Dev Ashish on and off over the years. His code is always clever stuff (to me anyway) which I don't necessarily fully understand, but it always works and that's good enough for me.

And I will indent, promise.

No doubt, but without the relevant reference (I believe that is the issue?) then it will not work?
So what reference do you have to enable to use it?

 

[KevinWilcox]

without the relevant reference (I believe that is the issue?) then it will not work?

I'm afraid that's too programmy for me, I'm just a code end user, the equivalent of a car driver who knows just enough to do a DIY service every 12 months. But if I had to guess I'd say it's through creating the class called 'SearchResults'? (Back at the beginning of the thread).

There is one remaining thing that's still got me foxed, which I only spotted when I indented; by including that one line;
If k = 0 Then myID = .ResultRows.item(j)(k)
I've ended up with an If without an end if and it only works if I leave that out. I don't remember ever seeing this before. However, it works, which is what matters.

 

[Gasman]

If the code is all on line, you can omit the End If.
In this case, it is on line.

Ah Ok, missed that one line in your initial post