Help with Loop

  • Thread starter Thread starter Stephen Lynch
  • Start date Start date
S

Stephen Lynch

I need to do this:

1. Loop though each record and if a match is found end the loop Else
2. Run though the loop again and if a partial match is found then open a
form.

I have to loop through the entire set of records FIRST because there are
full matches and partial matches in each row. A full match can be
automatically posted but the partials need to have a form opened so that I
can manually edit the payment amount because it was a mistake or
overpayment.

2 questions:

1. If I update a flagfield in the recordset, does it have any effect or do I
need to reestablish the recordset, if so how? (some people have multiple
loans with same payment amt, one I post it I want to forget it)
2. Do I just copy my loop code to run the send loop with a few modifications
or can I refer back to it?

Thanks


******************************************************************************
Here is what I need:

Openrecordset
Do while Not rs.EOF
If Match found
Run Append Query
Update recordset query to flag field so that we ignore it on the next pass
throught
End If
rs.MoveNext
Loop
If no match found
Loop through for partical match
if found open form
End


Here is what I have, code works except for the above 2 questions.

Set rs = CurrentDb.OpenRecordset(StrSQL, dbOpenDynaset)
Do While Not rs.EOF
strEmployeeIDContFile = rs.Fields("EmployeeID")
strEmployeeContAmt = rs.Fields("LoanAmt")
strPaymentDueDate = GetDateRight(strPaymentDueDate)
strOverage = ContributionAmt - strEmployeeContAmt
'Test to see if loan amount matchs employee loan
If strEmployeeIDContFile = strEmployeeID And strEmployeeContAmt =
ContributionAmt Then
'Update the payments and the date paid to the tblLoanPayments Table
DoCmd.RunSQL "UPDATE tblLoanPayments SET tblLoanPayments.Paid = 1,
tblLoanPayments.ActualPymtDate = #" & Me.Text52 & "#,
tblLoanPayments.ActualPymtAmt = " & strEmployeeContAmt & " " & vbCrLf & _
"WHERE (((tblLoanPayments.LoanID)=" & strLoanID & ") AND
((tblLoanPayments.PaymentDueDate)=(#" & strPaymentDueDate & "#)));"
'Change the status to posted
DoCmd.RunSQL "UPDATE tblLoanPostingTEMP SET
tblLoanPostingTEMP.Status = 'POSTED', tblLoanPostingTEMP.PaymentinFile = " &
strEmployeeContAmt & " WHERE ((tblLoanPostingTEMP.LoanID)=" & strLoanID &
");"
DoCmd.RunSQL "UPDATE tblLoanContributionsTEMP SET
tblLoanContributionsTEMP.AlreadyRan = '1' WHERE
(((tblLoanContributionsTEMP.EmployeeID)=" & strEmployeeID & ") AND
((tblLoanContributionsTEMP.LoanAmt)=" & strEmployeeContAmt & "));"

End If
rs.MoveNext
Loop

HERE IS MY PROBLEM, DO I ADD ANOTHER LOOP FOR THE PARTIAL MATCH?
 
Seems to me you needn't do the second loop unless you want to, or unless the
changes affect other records for some reason. After the first long If
statement, just insert an ElseIf:

If strEmployeeIDContFile = strEmployeeID And _
strEmployeeContAmt = ContributionAmt Then
'Update the payments and the date paid to the tblLoanPayments Table
 
Back
Top