automatically insert data to another table by completing checkboxes (1 Viewer)

[deamond]

Hello everyone.. I'm not sure whether I have to post this under the 'Table' or 'Modules and VBA' section. But I decide to post it here instead.

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.

For instance, 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 all of 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-

 

[deamond]

somebody please help me :')
if there's something vague, please don't bother to ask me here...

 

[vbaInet]

somebody please help me :')
if there's something vague, please don't hesitate to ask me here...

Your main problem is your table structure. The number of training may increase or decrease and in such circumstances you can't keep adding/removing fields.

What you need is a proper structure in place and I would suggest these two models:

http://www.databaseanswers.org/data_models/tracking_staff_training/index.htm
http://www.databaseanswers.org/data_models/events_and_staff_training/index.htm

 

[vbaInet]

And welcome to the forum by the way

 

[TimW]

HI your checkcompletion function IMHO is flawed.
I dont think its good to concatenate all the conditions to look for a string of 1's
I would loop through the checkboxes and if anyone of them is false the result is false. If all true then the result is true. Also I would also check if the CSA_Licence has already got a record, otherwise you will continue to have duplicate records..

HTH (just a little bit)

T

 

[deamond]

HI your checkcompletion function IMHO is flawed.
I dont think its good to concatenate all the conditions to look for a string of 1's
I would loop through the checkboxes and if anyone of them is false the result is false. If all true then the result is true. Also I would also check if the CSA_Licence has already got a record, otherwise you will continue to have duplicate records..

HTH (just a little bit)

T

thank you for your suggestion..
regarding to what you said, should I create some conditions in each checkbox?
I get your point about avoiding the duplicate records, but I guess I'm still stuck in the CheckCompletion issue.

 

[TimW]

This is how I would do it...

Private Sub Basic_Inspection_Click()
    CheckCompletion
End sub
Private Sub Certifying_Staff _Click()
    CheckCompletion
End sub
..etc

Sub CheckCompletion () ' or function...
   Dim blnComplete As Boolean

   blnComplete = False
   if me.basic_inspection = true AND me.Certifying_staff = true AND..... then
       blnComplete = True
   ' other code ...
  End if
End sub

There are probably neater answers to this, there are certainly alternatives.

I hope this helps

T

 

[vbaInet]

For instance, 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 all of the checkboxes on Development are fully checked.

The problem here is now how to do it, but how your table is structured like I have already highlighted. The models I linked to in my post shows you how to structure your tables properly. You should have a junction table between your training and employees table in which you can insert your 10 or more training schedule records. Counting the records per employee will be a breeze and you will not require any code.