But I've made an attempt at some code. Of course, it's not working, so I'm turning to the experts for some help.
First: The Situtation
I have a form used to enter issues/problems our customers call about our service. These issues then need to be reviewed by a staff member. In an attempt to even out the load, each new issue is assigned to a different staff member on a rotating basis (the first one goes to Sally, the next one goes to John, the third goes to Roger, and - if there were only three staff members - the next one would go to Sally again and so on).
So on this form, I have a button which I am attempting to code so that it will look up the name of the staff person who was assigned to the previous issue. Then go to a query which lists which staff persons can be assigned to review an issue and find the next available staff person which is applied to the corresponding field in my open form for the current record.
Second: The Code I've Come Up With (Don't laugh too hard!)
Private Sub cmdAssign_Click()
Dim cnnCurrent As ADODB.Connection
Dim rstIssues As ADODB.Recordset
Dim rstStaff As ADODB.Recordset
Dim intStaff As Integer
Set cnnCurrent = CurrentProject.Connection
rstIssues.Open "SELECT IssueID, DateAssigned, TimeAssigned, AssignedTo FROM tblIssues WHERE {AssignedTo Is Not Null) ORDER BY tblIssues.DateAssigned DESC , tblIssues.TimeAssigned DESC;", cnnCurrent, adOpenStatic, adLockReadOnly
rstIssues.MoveFirst
intStaff = rstIssues.Fields("AssignedTo")
rstIssues.Close
rstStaff.Open "SELECT StaffID FROM qryAssignedTo ORDER BY qryAssignedTo.StaffID;", cnnCurrent, adOpenStatic, adLockReadOnly
rstStaff.Seek (intStaff)
rstStaff.MoveNext
If rstStaff.EOF Then
rstStaff.MoveFirst
intStaff = rstStaff.Fields("StaffID")
Else
intStaff = rstStaff.Fields("StaffID")
End If
rstStaff.Close
cnnCurrent.Close
Me.AssignedTo = intStaff
End Sub
Third: Request
Any help/suggestions you can offer would be greatly appreciated!
-Kyrie
First: The Situtation
I have a form used to enter issues/problems our customers call about our service. These issues then need to be reviewed by a staff member. In an attempt to even out the load, each new issue is assigned to a different staff member on a rotating basis (the first one goes to Sally, the next one goes to John, the third goes to Roger, and - if there were only three staff members - the next one would go to Sally again and so on).
So on this form, I have a button which I am attempting to code so that it will look up the name of the staff person who was assigned to the previous issue. Then go to a query which lists which staff persons can be assigned to review an issue and find the next available staff person which is applied to the corresponding field in my open form for the current record.
Second: The Code I've Come Up With (Don't laugh too hard!)
Private Sub cmdAssign_Click()
Dim cnnCurrent As ADODB.Connection
Dim rstIssues As ADODB.Recordset
Dim rstStaff As ADODB.Recordset
Dim intStaff As Integer
Set cnnCurrent = CurrentProject.Connection
rstIssues.Open "SELECT IssueID, DateAssigned, TimeAssigned, AssignedTo FROM tblIssues WHERE {AssignedTo Is Not Null) ORDER BY tblIssues.DateAssigned DESC , tblIssues.TimeAssigned DESC;", cnnCurrent, adOpenStatic, adLockReadOnly
rstIssues.MoveFirst
intStaff = rstIssues.Fields("AssignedTo")
rstIssues.Close
rstStaff.Open "SELECT StaffID FROM qryAssignedTo ORDER BY qryAssignedTo.StaffID;", cnnCurrent, adOpenStatic, adLockReadOnly
rstStaff.Seek (intStaff)
rstStaff.MoveNext
If rstStaff.EOF Then
rstStaff.MoveFirst
intStaff = rstStaff.Fields("StaffID")
Else
intStaff = rstStaff.Fields("StaffID")
End If
rstStaff.Close
cnnCurrent.Close
Me.AssignedTo = intStaff
End Sub
Third: Request
Any help/suggestions you can offer would be greatly appreciated!
-Kyrie
