Update a record

[Marianne]

Happy Tuesday to all!

I want to update the field [Paid], which is a checkbox, in the InvoiceDetail
table to True if the amount of the payment received is equal to the ExtAmt.

The code works except for the part where [Paid] is updated to True(the box
becomes checked).

Dim lngInvID As Long
Dim lngLineNum As Long
Dim curPymtsByLine As Currency
Dim curExtAmtByLine As Currency
Dim blnPaid As Boolean
lngInvID = Me.PymtInvID
lngLineNum = Me.InvLineNum
curPymtsByLine = DSum("[Pymt]", "PaymentDetail", "[PymtInvID]= " &
lngInvID & " AND [InvLineNum] = " & lngLineNum)
curExtAmtByLine = DSum("[ExtAmt]", "InvoiceDetail", "[DetInvID]= " &
lngInvID & " AND [InvLineNum] = " & lngLineNum)
blnPaid = DLookup("[Paid]", "InvoiceDetail", "[DetInvID]= " & lngInvID &
" AND [InvLineNum] = " & lngLineNum)

If curPymtsByLine = curExtAmtByLine Then
blnPaid = True
Else
blnPaid = False
End If

Me.cboSelectPaymentsList.Requery

 

[Jeanette Cunningham]

Try this

If curPymtsByLine = curExtAmtByLine Then
Me.Paid = True
Else
Me.Paid = False
End If


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Marianne]

Jeanette-Thanks for your suggestion. I did find another way to accomplish
this as follows:

If curPymtsByLine = curExtAmtByLine Then
DoCmd.RunSQL ("Update [InvoiceDetail] set [Paid] = true where
[DetInvID]= " & lngInvID & " AND [InvLineNum] = " & lngLineNum)
End If

Try this

If curPymtsByLine = curExtAmtByLine Then
Me.Paid = True
Else
Me.Paid = False
End If


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Happy Tuesday to all!

I want to update the field [Paid], which is a checkbox, in the
InvoiceDetail
table to True if the amount of the payment received is equal to the
ExtAmt.

The code works except for the part where [Paid] is updated to True(the box
becomes checked).

Dim lngInvID As Long
Dim lngLineNum As Long
Dim curPymtsByLine As Currency
Dim curExtAmtByLine As Currency
Dim blnPaid As Boolean
lngInvID = Me.PymtInvID
lngLineNum = Me.InvLineNum
curPymtsByLine = DSum("[Pymt]", "PaymentDetail", "[PymtInvID]= " &
lngInvID & " AND [InvLineNum] = " & lngLineNum)
curExtAmtByLine = DSum("[ExtAmt]", "InvoiceDetail", "[DetInvID]= " &
lngInvID & " AND [InvLineNum] = " & lngLineNum)
blnPaid = DLookup("[Paid]", "InvoiceDetail", "[DetInvID]= " & lngInvID
&
" AND [InvLineNum] = " & lngLineNum)

If curPymtsByLine = curExtAmtByLine Then
blnPaid = True
Else
blnPaid = False
End If

Me.cboSelectPaymentsList.Requery

.