Before I forget, the expression that you're using to build the SQL string
for sqlPaid has one too many left parentheses in it. Same comment is true
for the sqlPartial build.
Second, your code needs to assign rst to a recordset. You say that the
subform is already based on a query using the InvoiceMain table. Can that
subform be the source of the records that you want to "mark as paid"? Or
does the subform contain a different set of records? How we would write the
code will depend upon whether you can use the subform's recordset or must
use a new recordset.
Third, your code will not fully loop through the recordset (whatever it may
be), because, after the rst.FindNext step, you don't go back through the
"If..Then" block to mark the record as paid.
If you can use the subform's recordset (which, it appears to me, based on
its recordsource query, contains the desired records), let me suggest a
modified set of code:
Private Sub PaymentAmount_AfterUpdate()
Dim rst As DAO.Recordset
Dim InvTot As Integer
Dim PmtAmt As Integer
Dim RemAmt As Integer
Dim InvNo As Integer
Dim sqlPaid As String
Dim sqlPartial As String
PmtAmt = Me.PaymentAmount
Set rst = Me.PmtsSubform.Form.RecordsetClone
rst.MoveFirst
Do While rst.EOF = False
InvNo = Me.InvoiceID
InvTot = DSum("[ExtPrice]", "invoicedetails", "Invoiceid =" & InvNo)
If InvTot < PmtAmt Then
rst.Edit
rst!Paid = True
rst.Update
RemAmt = PmtAmt - InvTot
rst.MoveNext
ElseIf InvTot = PmtAmt Then
rst.Edit
rst!Paid = True
rst.Update
Exit Do
ElseIf InvTot > PmtAmt Then
rst.Edit
rst!amtremaining = RemAmt
rst.Update
Exit Do
End If
End If
Set rst = Nothing
End Sub
--
Ken Snell
<MS ACCESS MVP>
Angi said:
I'll answer your questions first. The recordsource for the PmtForm is
the Payments table (no query). The records I need to modify are in the
InvoiceMain table. I do have a subform, PmtsSubform, with a
recordsource of :
SELECT InvoiceMain.InvoiceID, InvoiceMain.OrderDate,
InvoiceMain.AmtRemaining, InvoiceMain.Paid,
DSum("[ExtPrice]","invoicedetails","Invoiceid =" &
invoicemain.InvoiceID)+DSum("[freightamt]","invoicemain","Invoiceid ="
& invoicemain.InvoiceID) AS Total, InvoiceMain.CoID FROM InvoiceMain
INNER JOIN InvoiceDetails ON
InvoiceMain.InvoiceID=InvoiceDetails.InvoiceID WHERE
(((InvoiceMain.Paid)=No) And ((InvoiceMain.CoID)=Forms!Payments!coid))
ORDER BY InvoiceMain.OrderDate;
Here's my code, so far:
Private Sub PaymentAmount_AfterUpdate()
Dim rst As DAO.Recordset
Dim InvTot As Integer
Dim PmtAmt As Integer
Dim RemAmt As Integer
Dim InvNo As Integer
Dim sqlPaid As String
Dim sqlPartial As String
PmtAmt = Me.PaymentAmount
rst.MoveFirst
rst.FindFirst "Paid = false"
If rst.NoMatch = False Then
InvNo = InvoiceID
sqlPaid = "Update invoicemain Set invoicemain.paid= yes WHERE
(((invoicemain.invoiceid)=" & InvNo & ")"
InvTot = DSum("[ExtPrice]", "invoicedetails", "Invoiceid =" &
InvNo)
If InvTot < PmtAmt Then
CurrentDb.Execute sqlPaid
RemAmt = PmtAmt - InvTot
rst.FindNext
ElseIf InvTot = PmtAmt Then
CurrentDb.Execute sqlPaid
RemAmt = PmtAmt - InvTot
ElseIf InvTot > PmtAmt Then
RemAmt = InvTot - PmtAmt
sqlPartial = "Update invoicemain Set
invoicemain.amtremaining= " & RemAmt & " WHERE
(((invoicemain.invoiceid)=" & InvNo & ")"
CurrentDb.Execute sqlPartial
End If
End If
End Sub
