automatically insert data to another table by completing checkboxes

[deamond]

Hello everyone..

I'm on my way on creating a simple Database for a company. This database contains several tables, one of the tables will record information about the training that had been completed by each employee. There are about ten sections of training that should be completed.

Regarding to the database, I have two tables called 'Development' and 'CSA_Lisence'. 'Development' is the table that record the information about training which containing ten checkboxes (which represent ten sections of training) and CSA_Lisence will be automatically requeried when the checkboxes on Development are fully checked.

Here's the step I've been worked on :
First, I made a function called 'CheckCompletion' to ensure whether all the checkboxes are checked :

Public Function CheckCompletion() As Boolean
 
    Dim blnComplete As Boolean
    Dim strCompletionSummary As String
 
    strCompletionSummary = Basic_Inspection & Certifying_Staff & Safety_Management_System & Regulation_Part_145 & Part_M & EWIS & Fuel_Tank_Safety_Level_2 & Dangerous_Goods & Human_Factor & Basic_Supervisory_Training
 
    If strCompletionSummary = "1111111111" Then
        blnComplete = True
    Else
        blnComplete = False
    End If
 
End Function

Second, I made a function called 'UpdateEmployee' to handles if all boxes are checked :

Public Function UpdateEmployee()
 
    Dim emp_numb As Long
    Dim emp_name As Long
    Dim strsql As String
 
    emp_numb = [Forms]![development].[employee_number].Value
    emp_name = [Forms]![development].[employee_name].Value
 
   If CheckCompletion Then
    strsql = "INSERT INTO CSA_Lisence (employee_number, employee_name) VALUES (" & emp_numb & " , " & emp_name & " )"
    CurrentDb.Execute strsql
   End If
End Function
 

then, i put this code on every checkbox's after update event (example only) :

Private Sub Basic_Inspection_AfterUpdate()
Call UpdateEmployee
End Sub

The problem is, nothing happened with the tables. However, when I managed to remove the 'If checkCompletion' condition, it worked and the 'CSA_Lisence' is requeried, but I will have ten multiple records with same contents (I just need one record per employee). I guess there's something wrong or missed in my code. Or i need to remove something? Any help would be appreciated. Thank you!

-deamond-

[jforbes]

The first thing I see is that CheckCompletion will never return anything other than False as the output is never set. You could try this:

Public Function CheckCompletion() As Boolean 
 
    Dim strCompletionSummary As String
 
    strCompletionSummary = Basic_Inspection & Certifying_Staff & Safety_Management_System & Regulation_Part_145 & Part_M & EWIS & Fuel_Tank_Safety_Level_2 & Dangerous_Goods & Human_Factor & Basic_Supervisory_Training
 
    CheckCompletion = (strCompletionSummary = "1111111111") 
 
End Function
 

Second, I would put a check in before inserting the Record to see if the Record already exists so that the Duplicate Record Situation doesn't happen:

Public Function UpdateEmployee()
 
     Dim emp_numb As Long
     Dim emp_name As Long
     Dim strsql As String
 
     emp_numb = [Forms]![development].[employee_number].Value
     emp_name = [Forms]![development].[employee_name].Value
 
    If CheckCompletion Then
        strsql  = "employee_number=" & emp_numb
        If DCount("employee_number", "CSA_Lisence", strsql) = 0 Then
            strsql = "INSERT INTO CSA_Lisence (employee_number, employee_name) VALUES (" & emp_numb & " , " & emp_name & " )"
            CurrentDb.Execute strsql
        End If
    End If
 End Function

[jforbes]

Also, for grins, you might try this instead:

Public Function CheckCompletion() As Boolean 
    CheckCompletion = True
    CheckCompletion = CheckCompletion AND Basic_Inspection
    CheckCompletion = CheckCompletion AND Certifying_Staff
    CheckCompletion = CheckCompletion AND Safety_Management_System
    CheckCompletion = CheckCompletion AND Regulation_Part_145 
    CheckCompletion = CheckCompletion AND Part_M
    CheckCompletion = CheckCompletion AND EWIS
    CheckCompletion = CheckCompletion AND Fuel_Tank_Safety_Level_2
    CheckCompletion = CheckCompletion AND Dangerous_Goods
    CheckCompletion = CheckCompletion AND Human_Factor
    CheckCompletion = CheckCompletion AND Basic_Supervisory_Training 
End Function

[deamond]

thank you for your answer, jforbes.
it's still hasn't worked for me, I guess I'm still missing something.
Anyway, regarding to the latest code you sent yesterday, where should I put the condition whether the checkboxes is checked or not? I mean, should I just put the code just like that or add some line instead? thank you...

[jforbes]

OK, So what is not working? Are you getting an error? Have you debugged your code to see what it is doing?

You don't have to use what is posted in Post #3 and it might be easier to get your code working first. It was just an example of another way of testing multiple Boolean values that I thought might be easier for you to read. To use it, it would be a direct replacement for you CheckCompletion function.