Using CheckBoxes to Update Table 1

[kristi1023]

Hi,

I have 20 unbound checkboxes on a subform and I'd like to update a separate table with employee id and skill id. I'm using the following code on_click event of a button:

If Me.Check1 = True Then
[SkillID] = 1
DoCmd.SetWarnings False
DoCmd.RunSQL "insert into tblskillassessment employeeid,skillid) values (employeeid, [skillid])"
DoCmd.SetWarnings True

Else

DoCmd.SetWarnings False
DoCmd.RunSQL "delete from tblskillassessment where (((tblSkillAssessment.SkillID)=1) AND ((tblSkillAssessment.EmployeeID)=[Forms]![frmEmployeeSkills1]![EmployeeID]))"
DoCmd.SetWarnings True
End If
-----------------------------------------
I run the above code for each checkbox and update the where (((tblSkillAssessment.SkillID)=x)based upon the checkbox .
It works, but it's not 100% reliable; I'm getting duplicate records and the error message: Record is Deleted.

Is there a better way of adding and deleting records based upon a checkbox being checked or unchecked?

THANKS!

 

[VicRauch]

It looks like you are going to add a record for each box checked. If this is so, then delete all the records for this employee number, then go back and add the records for only the boxes that are checked.

 

[kristi1023]

VicRauch, thank you for your reply. Yes, I am adding a new record for each box that is checked. Will your recommendation improve the reliability? I run a delete query within the autoexec module that deletes duplicates, but I need a procedure that won't cause duplicates in the first place. Any ideas?

 

[VicRauch]

The technique I suggested is the one I use when there is a possibility of complicated changes. In this case, if you delete all records for this employee number, then add just those that have a check mark, you end up with what (I understand) you want. A record for each check mark, and no record where there is no check mark. I found a long time ago, this is simple, straight forward, and works. Getting myself tied up in confusing logic is NOT worth it. I have found that the confusing logic is usually NOT reliabile and simple logic is.

 

[PHV]

DoCmd.RunSQL "INSERT INTO tblskillassessment (employeeid,skillid) VALUES ([Forms]![frmEmployeeSkills1]![EmployeeID], 1)"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[VicRauch]

PHV,
I don't understand what your line of code will do. Kristi has 20 Skill IDs she is dealing with, your line of code looks like it is only addressing Skill ID 1. What she needs is a loop that examines each check mark, and if it is true, then output this statement for that particular Skill ID.
Maybe you could clarify what you mean by this code?

 

[PHV]

The code I posted was an amendment to the original post to show how to address the desired employeeid.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kristi1023]

VicRauch, thanks again. I think I do need some sort of loop to examine checkboxes, but I don't know how to do it.

PHV, will correcting the syntax, as you've suggested, solve my problem of duplicate records?

Thanks!

 

[VicRauch]

Kristi,

This following code is a sample on how to write the loop to control adding the records you need when the check box has been checked.
To explain the "me.fields("chk" & right("00" & x, 2))":
"me" is for the form this code is found in.
"right("00" & x, 2)" will take the loop number from x, add two zeros in front of the number, then take the two (2) right hand characters to use as the field name when added to the right side of "chk" This will give you all the field names of "chk01", "chk02", "chk03", ... to "chk20". Of course, this assumes you have named these 20 check boxes "chk01", "chk02", etc.

If you need more help than this, just ask.

dim x as integer

for x = 1 to 20
  if me.fields("chk" & right("00" & x, 2)) = true then
'    run code to add record
  end if
next x

 

[kristi1023]

VicRauch,
I think I do need more help. I understand how your code is looping to see which boxes are checked, but how is the value for skill id then being stored in the table? In other words if Check 1 = true, then employeeid =x and skillid =1, if Check 2=true, then employeeid=x and skillid=2, etc. Thanks for your time.

 

[PHV]

For x = 1 To 20
If Me.Controls("chk" & x) = True Then
DoCmd.RunSQL "INSERT INTO tblskillassessment (employeeid, skillid) VALUES ([Forms]![frmEmployeeSkills1]![EmployeeID], x)"
End If
Next x

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[VicRauch]

Kristi,
The DoCmd.RunSQL code that PHV posted is what you need to add the needed records to your table. Two changes are needed as I see it. The Forms and the "x" within the SQL statement need to be changed so as to get the value of the EmployeeID and the "x" when the SQL statement is being build. As the code stands, the "x" will be part of the SQL statement, not the value of "x" and the EmployeeID will be inserted during the running of the SQL statement. I just rather do the SQL building at one time.
New line:
DoCmd.RunSQL "INSERT INTO tblskillassessment (employeeid, skillid) _
VALUES(" & [Forms]![frmEmployeeSkills1]![EmployyeeID] & ", " & x & ");"

Also:
I took the time to explain the use of the two digit number added to the end of the "chk" for your 20 check boxes. I did not explain why I use two digits. There are times when these controls (or in the future) are listed in your code, or whereever, and they are much easier to check for correctness, update when needed within code that is not in a loop, etc, that if they are all uniform (the same size in length). If they are listed without being uniform in length, they would come out as:
chk1
chk10
chk11
chk12
...
chk2
chk3
I realize that to some people this is no big deal. I have found over the years that anything I can do (within reason) to make my code more uniform, it will be easier to deal with down the line.

PHV:
Unless you have real good reason, please don't change code that I have posted. Thanks.

 

[PHV]

Oops, sorry for the typo for the skillid value :~/
As for the two digits issue, ie why I changed the code posted (my emphasis):

If Me.Check[!]1[/!] = True Then

if me.[!]fields[/!]("chk" ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kristi1023]

Thank you both immensely for your help. Here's the code that I'm using and it works more reliably than my old code:

Dim x As Integer

For x = 1 To 22
DoCmd.SetWarnings False
If Me.Controls("check" & Right("00" & x, 2)) = True Then
DoCmd.RunSQL "INSERT INTO tblskillassessment (employeeid, skillid)" _
& "VALUES(" & [Forms]![frmEmployeeSkills1]![EmployeeID] & ", " & x & ");"

Else
DoCmd.RunSQL "DELETE from tblskillassessment where (((tblSkillAssessment.EmployeeID)=[Forms]![frmEmployeeSkills1]![EmployeeID])" _
& "AND ((tblSkillAssessment.SkillID)= " & x & "))"
DoCmd.SetWarnings True
End If
Next x
-------------------------
I have another related question: How can I prevent duplicates? In the scenario where employee X has skill 1 and 2, and a week later he acquires skill 3. When skill 3 is added to the table, skills 1 & 2 are also added. Thus, duplicates of skills 1 & 2. Is there a way to prevent skills 1 & 2 being added the second time? Is there a better way than deleting dups with a query? Thanks!

 

[VicRauch]

Kristi,

I still think the deletion query is the best answer, but another way would be to use DLookup to see if a record exists for this EmployeeID and SkillID. If it exists, skip the add, if it does not exist, do the add. If speed is any consideration at all, the delete query would be your quickest method, as DLookup is slow by comparison.
To check if the record exists:

Dim RetVal as Variant
RetVal = DLookup("EmployeeID", "tblskillassessment", "EmployeeID=" & _
      [Forms]![frmEmployeeSkills1]![EmployeeID] & _
      " And SkillID=" & x )
If IsNull(RetVal) Then     [green]'No record found[/green]
  [green]'Code to add new record[/green]
End If

 

[kristi1023]

Thanks, VicRauch!

I will try both methods and see which works best!

:->