0

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.

MJH
  • 2,301
  • 7
  • 18
  • 20
user2597356
  • 3
  • 1
  • 3
    Sounds like ugly `database-normalization`. There should be a table for employees, a table for workshops and a table that connects employees and workshops (n to m relationship) http://stackoverflow.com/questions/3397349/meaning-of-nm-and-1n-in-database-design. – BitAccesser Jun 09 '16 at 23:02

1 Answers1

2

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).

BitAccesser
  • 719
  • 4
  • 14
  • You can use a subform. [Adding a Checklist to MS Access](https://www.youtube.com/watch?v=cQQSXofvQh0) –  Jun 12 '16 at 12:29