How to check and uncheck all check boxes in a given record as a group?

[pheidlauf]

Hello. I'm working on creating a database (upgrading from an old manual excel table system to an Access system to improve user-friendliness and organization) that will help track, update, and review employees' training certifications are up to date.

Each table for the different areas of the plant (tool room, welding, etc.) is formatted similarly. The actual data is organized with a date field, and a row of check boxes for each competency that must be renewed. There are almost 20 separate sets of these tables, each with it's own unique set of competencies. The Primary Key is each employee's clock in number.

I want to write a Macro (or query, or anything that will work) to check every box within a given table if the "All Proficient" check box is marked. In other languages, I would just write a while loop to check each box until no more boxes are found, but I have no idea how to tackle this efficiently in SQL, VBA, or a macro.

I wrote one macro that works for one section, but it listed every single check box by name, and took a very long time to create. Is there a way to group check boxes such that I could check or uncheck all of them at a given event? I've attached two snapshots of my work. Let me know if you need more information to help.

I'm relatively new to the world of MS Access, but do have a decent amount of programming experience in other languages (Java, Python, C). However I know only the most basic SQL syntax thus far. I'm teaching myself MS Access as I go. Thanks in advance for any help.

 

[CJ_London]

It looks like you need to normalise your data to be able to proceed successfully.

I suggest your tables should be along the following lines:

Employee
EmployeeID autonumber
EmployeeName text
DepartmentID Long

Department
DepartmentID autonumber
DepartmentName text

Competences
CompetencyID autonumber
CompetencyName text
DepartmentID long

CompetencesCompleted
CCID autonumber
CompetencyID long
EmployeeID long
DateCompleted date

By linking on the relevant ID's you can see which employees have completed which competencies and by left joining to the competency table you can see which competencies have not been completed

 

[billmeye]

I agree with CJ. If you still end up with many check boxes and want to accomplish what your asking, then add this code to a button On Click event of your data entry form:

Dim ctl As Control
For Each ctl In Me.Form
        If ctl.ControlType = 106 And ctl.Name <> "Not Proficient" Then
        ctl.Value = -1
        End If
Next ctl
Set ctl = Nothing

If you want to do the same in a query you would have to create a function, pass the PKID and you could do something similar.

 

[pheidlauf]

The only reason I haven't combined all of my competencies as you mentioned is that there are 15 unique sets of competencies (each with 10-20 check boxes) that are independent of each other. I'm also utilizing a linked master table (provided) that groups each employees information with the department ID, name, and clock number all into one table. Would you advise that I pull information out of the master table into separate tables for the sake of normalization?

My main focus with this question was actually just the macro and grouping of check boxes, but thank you for your advice on the whole project. When I finally get to the stage of inputting information from Excel, I don't want to have to check 10 check boxes manually for each of 300 employees. I'm trying to find a way to make the data input as efficient as possible. I will start working on some better normalization in the meantime.

 

[pheidlauf]

Dim ctl As Control
For Each ctl In Me.Form
        If ctl.ControlType = 106 And ctl.Name <> "Not Proficient" Then
        ctl.Value = -1
        End If
Next ctl
Set ctl = Nothing

Billmeye, could you possibly explain the Me.Form syntax meaning? What is the "Me" object?

 

[billmeye]

Me. or Me! are used to refer to the forms properties and the form controls properties in VBA. Me.Form means the following is in relation to the current form. If I wrote Me.Products it would be referring to the control named Products.

 

[pheidlauf]

billmeye, I'm getting a "Invalid use of Me keyword" compile error with the code I have pasted below. I'm trying to write it as a function so I can just have each of the 15 forms call one function rather than writing 15 of the same. I'm running the code from the form, but getting this error, do you have any idea why?

Function Toggle()
    Dim ctl As Control
    
    For Each ctl In Me.Form
            If ctl.ControlType = 106 And ctl.Name Is Not "All Proficient" And ctl.Name Is Not "Not Proficient" Then
            ctl.Value = -1
            End If
    Next ctl
    Set ctl = Nothing
End Function

 

[billmeye]

Me.Form can only be used on the form itself. If you want to create a generic function you will need to pass the form name to the function:

Function Toggle(GetForm As Form)
    Dim ctl As Control
    
    For Each ctl In GetForm
            If ctl.ControlType = 106 And ctl.Name <> "All Proficient" And ctl.Name <> "Not Proficient" Then
            ctl.Value = -1
            End If
    Next ctl
    Set ctl = Nothing
End Function

So, to use it, place this code behind your button:

Call Toggle(Me.Form)

 

[spikepl]

I would strongly suggest that you follow the structure given by CJ London. Your checkbox-approach is Excel-tainted thinking. The consequence is pure hell in terms of doing anything with your data: maintaining, adding, modifying, searching.

Also, you have spread the same type of data over different tables which is a normalization error (google database normalization). The consequence of this is that all queries, forms and reports have to be repeated for each table, which is plain silly The same type of data belongs in the same container, but just needs to be tagged, to discern it.

 

[pheidlauf]

So as I've started implementing the normalized database, I've come across a couple of unique instances that I'm not sure how to handle (without ruining normalization). Is there a way for me to create a competency (Orientation or such) that a person from any department must complete. The "easiest" (stupid) solution of creating 15 different Orientation entries seems to be the repetitive work that Access gurus avoid. How would you suggest handling that situation? Similarly, some people work in multiple departments. Is there an easy solution to that one as well?

I'm starting to understand normalization, but I just don't see all of the solutions quite yet. Thanks for the help so far.

 

[spikepl]

I reckon you have two choices.

1. The one outlined by CJ London. This keeps all competencies independent. Whether similar or not, each relevant competency has to be created for each department

2. Create a many-to-many relationship, so that any competence can be assigned to one or more departments.

tblCompetenciesDepartments
----------------------------
DepartmentID (FK)
CompetencyID (FK)

this one can get tricky, because any change of course affects all the departments to which the given competency is assigned.

 

[pheidlauf]

It looks like you need to normalise your data to be able to proceed successfully.

I suggest your tables should be along the following lines:

....

Competences
CompetencyID autonumber
CompetencyName text
DepartmentID long

...

By linking on the relevant ID's you can see which employees have completed which competencies and by left joining to the competency table you can see which competencies have not been completed

CJ (or anyone), what is the purpose of tagging the Competencies table with the DepartmentID? With the current organization I have (almost exactly what you suggested), it is serving as a foreign key. However, I can't understand the need for it there, as it does not sort or organize the information in any way I can see.

The only remaining normalization issue I'm working on are handling employees who are in multiple departments and competencies that are common between multiple departments.

Your help has been fantastic.

Edit:
Also, in my Competencies table, I am using a number (not AutoNumber) as my primary key. It's a 4-digit code that I'm creating manually for each competency where the first two digits correspond to a department and the last two digits are just numbers differentiating the different competencies. Is anything innately wrong with that system? I know that you aren't supposed to put data as your primary key, but I don't think I am breaking that rule. Is AutoNumber better?

 

[CJ_London]

The reason is so you can link competencies to departments

So an employee is in this department which requires those compentencies.

It also means that by using a left join between compentencies and compentenciescompleted you can list all the competencies together with those that have been completed - or not completed.