Update records with checkboxes checked VBA

[excellenthelp]

Hi,



I am working on writing a code to update records that has checkboxes checked.

================================================== ========================

Desired Scenario:
Assign a task to a manager with earliest assignment date who are 'Allowed to Auto Assign To'. (AllowAutoAssign checkbox field in the table)

For example:
Adam | AllowAutoAssign = True | Earliest Assignment = 5/1/00
BoB | AllowAutoAssign = False | Earliest Assignment = 2/1/00
Carl | Allow AutoAssign = True | Earliest Assignment = 3/1/00

I click on a button to assign a task and the code would run and update Carl's record.

================================================== ========================

I do not want to update BoB's even though he has the earliest assignment date because AllowAutoAssign is false.

How do I write a such code??? Here is the code I have below that works but will not check for those checkboxes.

Private Sub btnAssign_Click()    Dim i As Long
          
        With Screen.ActiveForm.[AMID]
            Dim LResponse As Integer
            
            LResponse = MsgBox("Do you wish to auto-assign to an Accreditation Manager?", vbYesNo, "Continue")
            
            If LResponse = vbYes Then
                Dim stNextUser As String
                Dim MyDB As Object
                Dim RS As Recordset
                Set MyDB = DBEngine.Workspaces(0).Databases(0)
                
                stNextUser = ""
                
                Set RS = MyDB.OpenRecordset("select * from qryAIA_AutoAssignListWorkloadTrackingLog")
                lngRSCount = RS.RecordCount
                
                If lngRSCount <> 0 Then
                    With RS
                        .MoveFirst
                        stNextUser = Trim(RS.Fields("ID").Value)
                        .Edit
                        .Fields("LastAssignment").Value = Now()
                        .Update
                        .Close
                    End With
                    MyDB.Close
                    Else
                    MyDB.Close
                    End If
                End If
                
                    DoCmd.GoToRecord , , acNewRec
            
                    For i = 0 To .ListCount
                            
                        If .ItemData(i) = stNextUser Then
                            .Value = stNextUser
                        End If
                    Next i
                    
                Me.AMID.SetFocus
                Me.AMID.Text = ""
                Me.ACAID.SetFocus
                frmSupport_AutoAssignWorkloadTrackingLog_Subform.Requery
                
                MsgBox "Task assigned successfully."
            Else
                MsgBox "No task or manager to assign."
                Me.AMID.SetFocus
            End If
        End With
End Sub

[pbaldy]

You appear to be using a query, so presumably the criteria could be added there. You could also modify the SQL to use the TOP predicate, a WHERE clause to filter out the False records and an ORDER BY clause that sorted by date.

[excellenthelp]

pbaldy,

Thank you for your respond. I am not sure how to do this...

Here is an image of my form with subform that I am using:

[NTC]

It is a difficult concept to implement. Having the pool or record set of Allowed is straight forward in that AllowAssign =-1. But matching that record manager's ID to a differing pool / records set of tasks (projects, whatever) is the hard part. In essence you must:
a. pick 1 manager (i.e. maximum ID value or some suitable differentiating criteria)
b. write manager ID into Project (assign) and then clear the AllowAssign to 0
c. Count the record set of AllowAssign and if >0 then repeat A,B,C

A. is a select query B. is an Update Query and then a second Update Query to clear the allow assign to 0 C. is either an Aggregate Query or a DCount of a select query. So it is not implemented purely in VBA - but rather some VBA which fires the various queries as logic dictates.

Difficult to be more precise without digging into the actual structure of the data fields.