Do Loop not checking all records

[SockMonkey]

I believe there is a simple fix to this but I have been looking at it to long to see where it is.

Here is the Scenario:
1. I have a main form with a combo box that is used to select a salesperson
2. On the main form there are also three individual sub forms. Subfrm1, Subfrm2 and Subfrm3
3. Once the user selects the Salesperson in the combo
Subfrm1 displays continuous forms of the salesperson current sales records
4. When the user goes into one of those records It synchronizes the records on Subfrm2 & Subfrm 3 by the [StatusNo].
Subfrm2 shows the schedule for payment by the client and Subfrm3 shows the commission splits for all the salespersons involved on the deal (anywhere from 1 to 3 people)

The Problem:
When the Total Sales Price or Commission %Rate is updated on Subfrm1 it re-calculates the Company Commission Dollar Amount.
It then pass this updated value over to Subform3

Then I am using a DO LOOP statement to cycle through all the records in Subfrm3 to recalculate the salespersons commission based on there [Salesperson Percentage] * [Company Commission Dollar]. It is only making the change for one salesperson but not any of the additional ones on that sale.

Private Sub TotalRentSalePrice_AfterUpdate()
Me.TotalCommission = (Me.TotalRentSalePrice * Me.TotalCommPerc)

Dim formname As String, SyncCriteria As String, ControlName3 As String
Dim f3 As Form
Dim rst As DAO.Recordset, dbs As DAO.Database

formname = Me.Parent.Name
ControlName3 = "fsubDealCommissionsBrokers"

Set f3 = Me.Parent.Controls(ControlName3).Form
SyncCriteria = BuildCriteria("StatusNo", dbLong, Me!StatusNo)

f3.Filter = SyncCriteria
f3.FilterOn = True
f3.temp = Me!StatusNo
f3!TotalTSOcomm = Me.TotalTSOCommission


Set dbs = currentDB()
Set rst = dbs.OpenRecordset("qsfrmCommissionStatusDealBrokers",dbOpenDynaset)

With rst
If .RecordCount > 0 Then
.MoveLast
.MoveFirst
Do Until .EOF
If f3= Me.StatusNo Then
f3!Commission = f3!CommPerc * f3!TotalTSOcomm
End If
.MoveNext
Loop
End If
.Close
End With
End Sub

Just need to figure out how to get this to cycle through all the records so that it updates the commission for each salesperson.

Thanks in advance for your help

Rob

 

[TerpFan2001]

SockMonkey:

If you are familiar with SQL you can use an UPDATE statement to do what you are trying to do. Create a procedure and then pass to it the "CompanyCommission" and "StatusNo".


Public Procedure UpdateSplits(lStatusNumber as Long, dCompanyCommission as Double)

Dim sSQL As String

sSQL = "UPDATE tblTableYouWantToUpdate SET [Commission] = [SalesPersonPercentage] * " & dCompanyCommission & " WHERE [StatusNo] = " & lStatusNumber

CurrentDb.Execute sSQL

End Sub


When ever Total Sales Price or Commission %Rate is updated, pass the new CompanyCommission and StatusNo to the function and then requery your form.

UpdateSplits Me.StatusNo, Me.TotalTSOCommission

Me.Requery


This technique uses a lot less code and should run faster than looping through a record set.
Hope this is helpful.

 

[SockMonkey]

Thanks very much!
A fantastic solution.

Only change I had to make was the very last statement.

Instead of Me.requery had to
f3.requery.

It works perfectly now.