Evaluating checkbox results

' this code checks to total value of the boolean results decimal equivalent.  Changefield represents the record name prefix plus the decimal equivalent value.
 
ChangeField = "lblWMActivity" & WorkerFactorDecimal
strSQL = "UPDATE tblActivities " & _
            "SET activitystatus = 'x' " & _
            "WHERE activityname = '" & ChangeField & "';"
DoCmd.RunSQL strSQL
 
' Check each character in WorkerFactorBoolean for check mark(1). if found, change tag value to X for later inclusion in message
 
For i = 1 To Len(WorkerFactorBoolean)
    ChangeField = "lblWActivity" & i
    BackwardMid = Len(WorkerFactorBoolean) + 1 - i
    Result = Mid(WorkerFactorBoolean, BackwardMid, 1)
    If Result = "1" Then
        strSQL = "UPDATE tblActivities " & _
                    "SET activitystatus = 'x' " & _
                    "WHERE activityname = '" & ChangeField & "';"
             
        DoCmd.RunSQL strSQL
    End If
Next i

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:

For I = 1 to Len(ChoiceBoolean)
 rs.MoveFirst
 Do while not rs.EOF
  if tblDecimalValue > ChoiceDecVal then
   Showit = "N"
  Else
   For Z = 1 to len(ChoiceBoolean - (I - 1))
    if Showit <> "" then exit do
    if mid(ChoiceBoolean,len(choiceboolean + (1 - Z),I)) =
    mid(tblBoolean,len(choiceboolean + (1 - Z),I)) _
    and if instr(tblBoolean, "1", I) >= 1 then
     showit = "Y"
     exit for
    end if
   next Z
 end if
 rs.MoveNext
 Loop
Next I

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:

Public Sub trying4()
Dim rs As DAO.Recordset
 
' Set statements
Set rs = DBEngine(0)(0).OpenRecordset("tblActivities")
ChoiceBoolean = WorkerFactorBoolean
 
' Reset all ActivityStatus values to ""
rs.MoveFirst
Do While Not rs.EOF
    With rs
    .Edit
    !ActivityStatus = ""
    .Update
    End With
rs.MoveNext
Loop
rs.MoveFirst
 
 
For i = 1 To Len(ChoiceBoolean)
    rs.MoveFirst
    Do While Not rs.EOF
    tblboolean = rs!ActivityShowBoolean
    tblConvertNumber = rs!ActivityShowBoolean
    tblDecimalValue = 0
    Showit = rs!ActivityStatus
        ' Determine the total decimal value tblActivities.ActivityShowBoolean
        For W = 1 To Len(tblboolean)
            If tblConvertNumber = "" Then Exit For
            If W = 1 Then
                tblDecimalValue = (tblDecimalValue) + (Right(tblConvertNumber, 1) * 1)
            Else
                tblDecimalValue = (tblDecimalValue) + (Right(tblConvertNumber, 1) * (2 ^ (W - 1)))
            End If
            tblConvertNumber = Left(tblConvertNumber, Len(tblConvertNumber) - 1)
        Next W
        
        If tblDecimalValue > WorkerFactorDecimal Then
            With rs
            .Edit
            !ActivityStatus = "n"
            .Update
            End With
        Else
            InterimChoiceBoolean = Len(ChoiceBoolean) - 1
            For z = 1 To Len(ChoiceBoolean)
                If Showit <> "" Then Exit For
                ' Set Variables
                NumtoRemove = Len(WorkerFactorBoolean) + 1 - z
                
                InterimChoiceBoolean = Mid(ChoiceBoolean, NumtoRemove, i)
                InterimtblBoolean = Mid(tblboolean, NumtoRemove, i)
                OneInInterimtblBoolean = InStr(InterimtblBoolean, "1")
                
                If InterimChoiceBoolean = InterimtblBoolean And OneInInterimtblBoolean >= 1 Then
                    With rs
                    .Edit
                    !ActivityStatus = "x"
                    .Update
                    End With
                    Exit For
                End If
            Next z
        End If
    rs.MoveNext
    Loop
Next i
DoCmd.SetWarnings True
Screen.ActiveForm.Refresh
rs.Close
End Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:

Public Sub TableResults()
Dim rs As DAO.Recordset
Dim tblBoolean, NumberofOnes, tblOneLocation, Showit
 
' Set statements
Set rs = DBEngine(0)(0).OpenRecordset("tblActivities")
 
' Reset all ActivityStatus values to ""
rs.MoveFirst
Do While Not rs.EOF
    With rs
    .Edit
    !ActivityStatus = ""
    .Update
    End With
rs.MoveNext
Loop
rs.MoveFirst
 
Do While Not rs.EOF
    ' Set Variables
    tblBoolean = rs!ActivityShowBoolean
    Showit = rs!ActivityStatus
        
    ' Count the Number of ones in the tblrecord
    NumberofOnes = 0
    For i = 1 To Len(tblBoolean)
        If Mid(tblBoolean, i, 1) = "1" Then NumberofOnes = NumberofOnes + 1
    Next i
        
    If NumberofOnes > 0 And Showit = "" Then
        tblOneLocation = 0
        For i = 1 To NumberofOnes
            tblOneLocation = InStr(tblOneLocation + 1, tblBoolean, "1")
            If Mid(WorkerFactorBoolean, tblOneLocation, 1) = "0" Then
                Showit = "n"
            Else
                If Showit = "" Then Showit = "x"
            End If
        Next i
            With rs
            .Edit
            !ActivityStatus = Showit
            .Update
            End With
    End If
NextRecord:
rs.MoveNext
Loop
DoCmd.SetWarnings True
Screen.ActiveForm.Refresh
rs.Close
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53: