Updating a table with records read from same table?

[Gasman]

Hi all,

I found out today that if a employee gets a commission payment, then so does the manager and another employee
I already have the functionality to add the commission payments per employee, so rather than have to reenter them again, I *thought* I could
Read all payments not yet processed.
Create new records where only the employee ID changes for the manager and the amount also changes for the second employee.

This way when I process the payments, they will all be correctly gathered under each employee?

However my first attempt seemed to create a never ending loop as the records I added became part of the recordset that I was reading for the correct records.? When I started this, there were only six records to process, so I should have added 2 extra records for each of those records, but I stopped at 58

I then googled and discovered dbOpenSnapshot, which I again *thought* would just read the records involved and not grow with my adding new records, but it is still happening.

So how can I go about this, or is there a better method to achieve what I am trying to do please.?

rst is the recordset that I need to read and copy values from
rstComm is the records I need to add.?

TIA

Private Sub cmdJCCommission_Click()
On Error GoTo ErrHandler
Dim db As Database
Dim rst As Recordset, rstComm As Recordset
Dim strSQL As String

Set db = CurrentDb()

strSQL = " SELECT tblCommission.CommissionDate, tblCommission.Reason, tblCommission.Amount, tblEmployee_1.EmployeeID, tblCommission.Processed"
strSQL = strSQL & " FROM tblEmployee AS tblEmployee_1 INNER JOIN (tblCommission INNER JOIN tblEmployee ON tblCommission.EmployeeID = tblEmployee.EmployeeID) ON tblEmployee_1.EmployeeID = tblEmployee.ReportsTo"
strSQL = strSQL & " WHERE (((tblEmployee_1.EmployeeID)=15) AND ((tblCommission.Processed) Is Null))"

' Open recordsets ready
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
Set rstComm = db.OpenRecordset("tblCommission", dbOpenTable)

SetStatusBar ("Adding Commissions ....")
If rst.EOF Then
    MsgBox "No commission records found, check processed date"
    GoTo ExitSub
Else
    rst.MoveFirst
    Do While Not rst.EOF
        ' First JC commission as values are same
        rstComm.AddNew
        rstComm!EmployeeID = 15
        rstComm!CommissionDate = rst!CommissionDate
        rstComm!Reason = rst!Reason
        rstComm!Amount = rst!Amount
        rstComm.Update
        ' Now Adam Lewis commission
        rstComm.AddNew
        rstComm!EmployeeID = 18
        rstComm!CommissionDate = rst!CommissionDate
        rstComm!Reason = rst!Reason
        rstComm!Amount = 1
        rstComm.Update
        rst.MoveNext
    Loop
End If

SetStatusBar ("Commissions created")
rstComm.Close
rst.close

ExitSub:
    Set rstComm = Nothing
    Set rst = Nothing
    Set db = Nothing
  

    
Err_Exit:
    Exit Sub
    
ErrHandler:
    MsgBox "Error " & Err.Number & " " & Err.Description
    Resume ExitSub

 

[sneuberg]

You don't want the added records to show up in the rst recordset so I think you need to add something so the new records do not satisfy the where clause of the rst recordset query. For example since (tblCommission.Processed) Is Null is a condition it would be convenient if you could just change that for the added records e.g.,

rstComm!Processed = "something not null"

but if there's no condition like that you can change then you could add a field to the table just to act as marker for this.

 

[Gasman]

Thanks for the reply Sneuberg,

I cannot use the Processed field as I use that when the records are processed.
I was thinking of making a table with the required records and then run through that.

The added field could be set for that particular team in some way.

I have also discovered in another team, that each person gets the same, so rather than enter the same data 3 or 4 times, I am looking to process that in much the same way I process the default hours, but am trying to think how I can handle this other situation at the same time.

I am trying to avoid multiple processes that have only a slight difference in them.

 

[Gasman]

Actually managed to do it with queries in the end.