Insert Recordset with conditional statement

[mjd973]

Hi everyone,



I've been using access for several years and am working on a build to assign specific auditors to employees each month. Everything is complete except for one caveat which is each employee must have 4 audits complete once each month, but each must be done by a different auditor (i.e. an auditor can perform only one evaluation per employee). The trouble is I have not been able to find anyone who knows how to do this at work or any boards I have used online so far.

Below is the ADO statement that I built:

Sub AssignInvoicesToPersonnel()

Dim rstStrat As DAO.Recordset, rstInvoices As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM tblInvoices WHERE ReviewerID Is Null;"

Set rstInvoices = CurrentDb.OpenRecordset(strSQL)

If rstInvoices.RecordCount > 0 Then
rstInvoices.MoveFirst

strSQL = "SELECT * FROM tblStrat ORDER BY Rnd([Crew_ID]);"

Set rstStrat = CurrentDb.OpenRecordset(strSQL)
If rstStrat.RecordCount > 0 Then
rstStrat.MoveFirst
While Not rstInvoices.EOF

rstInvoices.Edit
rstInvoices!ReviewerID = rstStrat!CREW_ID
rstInvoices.Update
rstInvoices.MoveNext

On Error Resume Next
rstStrat.MoveNext
On Error GoTo 0
If rstStrat.EOF Then
restStrat.MoveFirst
End If

Wend

Else
MsgBox "ERROR: There are no reviewers to assign to evals"
End If

rstStrat.Close
Set rstStrat = Nothing
Else
MsgBox "There are ....
End If

rstIncoices.Close
Set rstInvoices = Nothing

End Sub"


For the statement above, my "tblInvoices" has a field titled "Employee_Name" and what I want to do is add a statement that says when updating the next Employee Name field with a Reviewer Id, if that same Reviewer Id has already been assigned to a previous evalulation for the same Employee Name, then to skip to another Reviewer Id. Does that make sense?

Thank you in advance for any help!!!

[pbaldy]

You open a recordset on reviewers, right? At the point of assigning, use a DCount() to test for the reviewer/employee combination in the last month. If greater than 0, move to the next and test again.

[June7]

Calculating data based on value in other records of same table is tricky. You need to do a search of the table to determine if the Reviewer ID is already associated with a record that meets criteria. Can probably be done with a domain aggregate like DLookup.

Why are you updating tblInvoices with reviewerID? This is an audit of invoices? Employees are associated with invoices? So reviewer cannot review invoice with employee that reviewer has already reviewed that month? What is tblStrat data?

[orange]

Below is the ADO statement that I built:

What makes you think you have written ADO ?

You have specifically Dimmed DAO.Recordsets

The trouble is I have not been able to find anyone who knows how to do this at work or any boards I have used online so far.

It isn't clear, as June7's questions show, that your terminology is consistent. Perhaps you could clarify for the reader the meaning of
Crew_ID
Reviewer
Evals
Invoice
and how these things are related.

Telling/Asking the reader in plain English is often a great way to focus on the issue/opportunity to be resolved. Once reader's understand your situation, often many options will evolve.

Adding vba code of HOW you have done something to a confusing textual description is masking the underlying issue.