Recordset to update date/time Issue

[excellenthelp]

Hello Accessers,



I am trying to have the code perform these things but unable to do so. While the code would still work, it will not move to the next user with either no date or the earliest date.

Goal:
-Go through the query record by record according to the date/time field
-Find the record with no date or earliest date (in that order)
-change its date to today's date

Code:

Private Sub Command56_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 = CurrentDb
                
                stNextUser = ""
            
                Set RS = MyDB.OpenRecordset("select * from qryTEST")
                lngRSCount = RS.RecordCount
                If lngRSCount <> 0 Then
                    RS.MoveFirst
                    
                    stNextUser = Trim(RS.Fields("Initials").Value)
                    
                    RS.Edit
                    RS.Fields("LastAssignment").Value = Now()
                    RS.Update
                    RS.Close
                    MyDB.Close
                    
                    DoCmd.GoToRecord , , acNewRec
            
                    For i = 0 To .ListCount
                            
                        If .ItemData(i) = stNextUser Then
                            .Value = stNextUser
                        End If
                    Next i
                End If
            Else
               Me.AMID.SetFocus
            End If
        End With
End Sub

Any help would be appreciated.

[orange]

Please show sql for qryTest.

Also, t looks like you're closing the database in the middle of processing.
MyDB.Close

[excellenthelp]

Hi orange,

Here's the sql for qryTest:

SELECT tblAIA_STAFFList.ID, tblAIA_STAFFList.LastAssignment, tblAIA_STAFFList.AllowAutoAssign, tblAIA_STAFFList.InitialsFROM tblAIA_STAFFList
WHERE (((tblAIA_STAFFList.AllowAutoAssign)=Yes));

As for MyDB.Close, it is odd because it does work on the previous database before I created a new split database and migrated everything there and I did major tweaking to make the tables, forms, reports and queries to work. This is one more thing I am working on migrating at the moment.

[orange]

You are asking about auto-assign. Do you process a list, or do you intend to go through a list one by one?
Do you have a group of managers that you want to update as a group?
Can you describe in plain English exactly WHAT you want to do?

[excellenthelp]

Plain English:

I want to auto-assign a task to a manager from a list of managers.

-If the manager is not assigned, assign the task to that manager.
-If all of the managers are assigned, assign the task to the manager with the earliest date.

Hope this is simple enough.

[orange]

What exactly is the manager with the earliest date?

So is it one task to be assigned? Or a list of Tasks to be assigned to various managers?

If a list of tasks, and a list of managers
-sort the manager list in whatever order you need (whatever earliest date means)
-assign Task1 to first manager
-assign Task2 to second manager
-continue loop to assign task to next manager until all tasks have been assigned.

if 1 task and a list of managers, find first manager (on some basis) that is currently unassigned, then
-assign the task to that manager

[excellenthelp]

One task per manager.

-----------------
Manager with the earliest date means:

Scenario 1
Manager A | March 2014
Manager B | February 2014
Manager C | July 2014

The task to be assigned will be assigned to Manager B.


Scenario 2
Manager A |
Manager B | August 2014
Manager C | January 2014

The task to be assigned will be assigned to Manager A.
---------------------

The code would need to be able to perform both scenarios at any time.

Hope this helps picture the intended operation of the code.

[excellenthelp]

Ah- I found the culprit. It was because the date field was not sorted when it needs to be ascending in the 'Assignment' table. It works now.

However, I would like to update the active form's textbox with today's date while assigning. It would update the Manager textbox but not the date in the 'Task' table. How do I update that?

The current code that works is below:

Private Sub Command56_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 qryTEST")
                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
                End If
                
                    DoCmd.GoToRecord , , acNewRec
            
                    For i = 0 To .ListCount
                            
                        If .ItemData(i) = stNextUser Then
                            .Value = stNextUser
                        End If
                    Next i
            Else
               Me.AMID.SetFocus
            End If
        End With
End Sub