Update a record

M

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
 
J

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
 
M

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

Jeanette Cunningham said:
Try this

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


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Marianne said:
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


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top