views:

52

answers:

1

In my database I have certain tables that have confidential information. Each of these tables contains an empty field called "ThisTableIsConfidential". I have a function that correctly displays a list of the tables that have this field in the Immediate window. Now I want to display the list on a form and I can't figure out how to do it. I thought to put the function in a query and display that in a listbox but the query didn't work. Any ideas?

This is the function (I cobbled it together from a few different online sources):

Function GetConfidentialTable()
 Dim db As Database, tbl As TableDef, fld As Field, currentTable As String
   Set db = CurrentDb

   For Each tbl In db.TableDefs
        If (tbl.Attributes = 0) Then  

        currentTable = tbl.Name

        If FieldExists(currentTable, "ThisTableIsConfidential") = True Then
            Debug.Print currentTable
        End If
     End If

   Next tbl
End Function
+1  A: 

You can set the listbox row source type to Value List and then use your function to return a list:

Function GetConfidentialTable()
 Dim db As Database, tbl As TableDef, fld As Field, currentTable As String
   Set db = CurrentDb

   For Each tbl In db.TableDefs
        If (tbl.Attributes = 0) Then  

        currentTable = tbl.Name

        If FieldExists(currentTable, "ThisTableIsConfidential") = True Then
            sList = sList & ";" & currentTable
        End If
     End If

   Next tbl

   GetConfidentialTable = Mid(sList,2)
End Function
Remou
Tara
@Tara If that is what suits you, but I do not think it is a good idea for the longer term. It was not necessary to change the code or to use a textbox. I think you must have missed my comment that you must set the row source type to Value list (property sheet for the listbox)
Remou
I did that and it didn't work.
Tara
Had you made any other changes to the listbox, such as setting the number of columns? I have tested the above, and it works on a new list box with the single change of setting the Row Source Type. This is by means an unusual thing to do, and the approach is standard.
Remou
@Remou's code produces a proper string to assign as the value list of a listbox formatted with a single column.
David-W-Fenton