DLookup A Field in Table and set a value to true ?

[d9pierce1]

Hi all,


I am trying when I click button on my form, to look up a different table and set the IsPaid check box to true and not sure how to make that happen
I have been trying different things but nothing seems to tick that checkbox.\

TransBillID is in form and table to referencehes my field TransB
I am seeking to set the IsPaid field to =True Where the TransBillID in tblTransBillLedger matches TransBillID on my form

Private Sub CmdPostPayment_Click()


DLookup("TransBillID", "tblTransBillLedger", "TransBillID=" & forms!frmTransPayment!TransBillID)
    'Set field IsPaid to = True in tblTransBillLedger
    IsPaid = True
End Sub

Not sure how to actually write this to make this happen?
Thanks for assistance
Dave

[June7]

Me.IsPaid = Not IsNull(DLookup("TransBillID", "tblTransBillLedger", "TransBillID=" & forms!frmTransPayment!TransBillID))

[d9pierce1]

Hi June,
Actually that does the oposit of what I am looking for, it un-ticks my IsPaid in my form and puts the value of the frmTransPayment IsPaid value.

Here is full code on the button...

Private Sub CmdPostPayment_Click()
    Dim strsql As String
    Dim db As DAO.Database
    Set db = CurrentDb
    
    If Me.IsPayrollDeduct = False Then
        strsql = "INSERT INTO tblAccountLedger (TransactionID, TransTypeID, TransCode, TransDate, AccountID, DescriptionID, TransMethodID, Debit) " & _
            "VALUES (" & TransactionID & ", " & CboTransType & ", '" & TransCode & "', #" & TransDate & "#, " & CboFromAccount & ", " & DescriptionID & ", " & CboTransMethod & ", " & TransAmount & ")"
        Debug.Print strsql
        db.Execute strsql, dbFailOnError


        strsql = "INSERT INTO tblAccountLedger (TransactionID, TransTypeID, TransCode, TransDate, AccountID, DescriptionID, TransMethodID, Credit) " & _
            "VALUES (" & TransactionID & ", " & CboTransType & ", '" & TransCode & "', #" & TransDate & "#, " & CboToAccount & ", " & DescriptionID & ", " & CboTransMethod & ", " & TransAmount & ")"
        Debug.Print strsql
        db.Execute strsql, dbFailOnError


Else
        strsql = "INSERT INTO tblAccountLedger (TransactionID, TransTypeID, TransCode, TransDate, AccountID, DescriptionID, TransMethodID, Credit) " & _
            "VALUES (" & TransactionID & ", " & CboTransType & ", '" & TransCode & "', #" & TransDate & "#, " & CboToAccount & ", " & DescriptionID & ", " & CboTransMethod & ", " & TransAmount & ")"
        Debug.Print strsql
        db.Execute strsql, dbFailOnError
End If
        Me.IsPaid = True
        Me.IsPaid = Not IsNull(DLookup("TransBillID", "tblTransBillLedger", "TransBillID=" & Forms!frmTransPayment!TransBillID))
    Me.RecordLock = True
    Call Form_Current


End Sub

[June7]

No, it is not putting the value of the frmTransPayment IsPaid value, it is putting the result of IsNull() function. A DLookup returns a value if there is a match and Null if there is not.

So what do you want the DLookup to do? Can't just have it hang out loose in code. It is a function returning a result and that must be received and dealt with by something. I wrap it in IsNull() and that result sets IsPaid.

You stated "I am seeking to set the IsPaid field to =True Where the TransBillID in tblTransBillLedger matches TransBillID on my form". That's what the expression I gave should do. If there is no match then IsPaid is set to False.

[d9pierce1]

Hi June,
What i wanted to do is set the IsPaid field in the DLookUp to true in that table where the record matches the TransBillID,
So, If I click the button on my form, it will set the value to true in the chkbox in the table "tblTransBillLedger" Where ID's match

Currently it is setting the value in my form to the value in the dlookup IsPaid....

Hope this makes sense, dlookup may not be the correct, maybe an update qry?
Not real sure but when i click that button it needs to set that IsPaid field in tblTransBillLedger to = true

[June7]

Then yes, you need to do an SQL UPDATE action, not DLookup. DLookup retrieves data, doesn't change data.

[d9pierce1]

thanks June 7

[d9pierce1]

Would it look like this?
DoCmd.RunSql UPDATE tblTransactionPayment INNER JOIN tblTransBillLedger ON tblTransactionPayment.IsPaid = tblTransBillLedger.IsPaid SET tblTransBillLedger.IsPaid = [tblTransactionPayment].[IsPaid];
UpDate, Above does not work at all....
Or something like this?

DoCmd.RunSql UPDATE ("tblTransBillLedger" SET IsPaid = True WHERE "TransBillID=" & Forms!frmTransPayment!TransBillID)

I have been looking over examples but have not come to one that examples this that i can comprhend...

[June7]

I prefer CurrentDb.Execute. And literal text must go between quote marks. Why would need a table JOIN? Don't you just want to update record(s) = TransBillID from the form? I thought you want to update field in tblTransBillLedger? UPDATE action requires a SET clause.

CurrentDb.Execute "UPDATE tblTransBillLedger SET IsPaid = True WHERE TransBillID=" & Forms!frmTransPayment!TransBillID

[d9pierce1]

Hi June7
Oh My Heavens, that worked like a charm....
Thank you so much
Dave