Obtaining field names from a table

[tamcgly33]

I need to grab field names from a table. The table is all yes/no fields with the exception of the key field. I need all the field names that have a value of "yes".

[Jim Doherty]

@tamcgly33
Consider the absolute meaning of your question because in the manner presented there can be only one logical answer and it is this:- you can only have ONE field in a table that has a 'field name' value of "yes"

If you are saying that you want to loop through a table definition and list only those field names that are of the boolean datatype Yes/No then it has a completely different meaning.

[tamcgly33]

@Jim Doherty
Thank you for getting back to me. Let me lay out exactly what I am doing and why I need this ability.

I have a form that brings up data in three different tables. One table is not relevant in the discussion. The relationship between to the tables of interest is a one to one relationship. Upon a save record function on the form, I need to grab the selected(check box) fields and concatenate the field names on the boolean table into a single field that is hidden on the corresponding table.

[Jim Doherty]

@tamcgly33
You mention a form now...so I take it you are working in a form that services a table and you are wanting to loop through the controls collection of the form at the current record displayed and return a concatenated list of only those control field names where the DATA value contained in the control is 'Yes' is that it?

[tamcgly33]

@Jim Doherty
That is exactly correct!

[Jim Doherty]

@tamcgly33

if I am reading you right then do the following:

1) Place an unbound textbox on your form and name it txtValues

Paste the following code in the OnCurrent code event for the form between the Sub and End sub lines


On Error Resume Next

Dim ctl As Access.Control
Dim myfield As String
myfield = ""
For Each ctl In Me.Controls
    Select Case ctl.ControlType
        Case acCheckBox
            If (ctl.Value = True) Then
                myfield = myfield & ctl.ControlSource & ";"
            'concatenate each yes value only and strip trailing semi colon
            Me!txtValues = Left(myfield, Len(myfield) - 1)
            End If
    End Select
Next ctl

3) Save and open your form you should see a concatenated string of those controlsource names for each checkbox where there exists only the datavalue "Yes"

You can of course use this concatenated string to set a Valuelist for any listbox you might want to populate but I dont know what you have there or the defined method for presentation of these return values