CODEWITHSUNDEEP
ms-accessvba
user2597356
user2597356

Reputation: 3

Is there a way to conditionally list field names on an access form?

I have a large database that monitors employees attendance at training events. Each record is an employee, and there are (along with basic information) a hundred or so fields that represent training events over the last 10 years. These fields are all yes/no checkboxes and so are checked if the employee attended that workshop.

I have a form that allows viewing information for individual employees easily, but I want a dynamic list on this form that shows what events the currently selected employee attended.

So I want a list to see which fields are designated as true for the specified record.

Is there any way to do this? Been searching all day and can't find a solution. Thanks.

Upvotes: 0

Views: 150

Answers (1)

BitAccesser
BitAccesser

Reputation: 719

Maybe somthing like this, assuming that all boolean fields are relevant and field name is workshop name:

Public Function getWorkshops(ByVal strEmployee As String) as String
' Declare vars
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim lngFieldsCount as Long
Dim n as Long
Dim strWorkshops as string

Set db = CurrentDb() '
lngFieldsCount = db.TableDefs("myTable").Fields.Count ' Count number of fields to iterate through
Set rs = db.OpenRecordset("SELECT * FROM myTable WHERE employeeName LIKE '" & strEmployee & "';",DbOpenSnapshot)
Do While not rs.Eof
  For n = 0 to lngFieldsCount -1 ' iterate through all fields
    If TypeOf rs.Fields(n) is dbBoolean Then ' check if field is boolean
      If rs.Fields(n) Then ' check if boolean is true
        strWorkshops = strWorkshops & rs.Fields(n).Name & vbCrLf ' add field names to string, separated by linebreaks
      End If
    End If
  Next n
  rs.MoveNext
Loop
getWorkshops = strWorkshops 'Set result of function
'Clean up
rs.Close
Set rs = Nothing
Set db = Nothing
End Function

This returns the name of all true fields in a string, separated with linebreaks (not tested).

Upvotes: 2

Related Questions