Update Value to Table - Access TechHelp


	Back to Access TechHelp Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Update Value to Table

James Hopkins

3 years ago

Hey Guys, I have created an Payment Form to Add Payments for Invoices and Credit to Customers' Credit Limit. I have wrote a VBA Code it the "Save & Process Payment" button to Update the "Customer Credit Limit" in the Customer Table. But I am wondering if there is a way to ADD to "Credit Amount" to the "Customer Credit Limit".

Here is what I have so far:

Private Sub BtnSaveandProcessPayment_Click()

    If ApplyTo = "Client Credit" Then
        DoCmd.RunSQL "UPDATE ClientT SET ClientT.ClientCreditAmount = CreditAmount WHERE ClientT.ClientID=" & ClientID
    End If
    DoCmd.Save
    DoCmd.Close acForm, Me.Name, acSaveYes
    DoCmd.OpenForm "PaymentsDashboardF", acNormal

End Sub

	Adam Schwanz @Reply 3 years ago
Your sql statement is wrong here, can you see what it is? = CreditAmount WHERE

	Adam Schwanz @Reply 3 years ago
Hint concatenation

	James Hopkins OP @Reply 3 years ago
DoCmd.RunSQL "UPDATE ClientT SET ClientT.ClientCreditAmount = 'CreditAmount + ClientT.ClientCreditAmount' WHERE ClientT.ClientID=" & ClientID

Adam Schwanz @Reply

3 years ago

Closer, it's hard to explain but easy once you get the hang of it. Remember every time you want to substitute a word or variable with a value (in this case CreditAmount) you have to put it outside of the quotations. Every double quote is either a "open the statement" or a "close the statement" other then if using it with double double quotes and strings. So you get to the equal sign and know next is the variable that you want to insert the value of, so we close the statement with a double quote, then concatenate the field (& CreditAmount) then we concatenate it back into the statement with a "open statement" quotation. " & CreditAmount & "

=" & CreditAmount & " WHERE

James Hopkins OP @Reply

3 years ago

I also been playing with a VBA Code:

Private Sub SaveAndProcessPayment_Click()

    ' Get the Credit Amount from the Payment Form
    Dim CreditAmount As Double
    CreditAmount = CDbl(Me.CreditAmount.Value)

    ' Get the Client ID from the Payment form
    Dim ClientID As Integer
    ClientID = CInt(Me.ClientID.Value)

    ' Update the Client Table with the new Credit Limit Amount
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT * FROM ClientT WHERE ClientID = " & ClientID)

    If rs.RecordCount > 0 Then
        rs.Edit
        rs.Fields("ClientCreditLimit").Value = rs.Fields("ClientCreditLimit").Value + CreditAmount
        rs.Update
        MsgBox "Client Credit Limit Amount has been Updated Successfully."
    Else
        MsgBox "Client not found."
    End If

    rs.Close
    Set rs = Nothing
    Set db = Nothing

    ' Clear the Credit Amount TextBox
    Me.CreditAmount.Value = ""

End Sub

	Adam Schwanz @Reply 3 years ago
You could use a recordset but a sql statement is probably easier for one person with one field. `Currentdb.execute "Update ClientT Set ClientCreditAmount=" & CreditAmount + NZ(DLOOKUP("ClientCreditAmount","ClientT","ClientID=" & ClientID),0) & " WHERE ClientID=" & ClientID`

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access TechHelp.

Next Unseen

New Feature: Comment Live View