How to RunSQL for update a record in ON CLICK event?

[osman7king]

I want to run an execute SQL statement which update a field in a table after clicking on "save" button

here the code I use:

Private Sub save_Click()
On Error GoTo Err_save_Click
Dim v_balance As Double
Dim v_final_balance As Double

    DoCmd.RunCommand acCmdSaveRecord
        
    v_balance = DoCmd.OpenQuery("SELECT closing_balance FROM balanceT")
      
    v_final_balance = v_balance - Forms![cash].[cash_out_usd] + Forms![cash].[cash_in_usd]

here I should Run my update statement which is: "UPDATE balanceT SET closing_balance = v_final_balance "

But How should I do that?

THANKS.

[Seth Schrock]

This is the way that I do it:

Dim strSQL As String
Dim db as DAO.Database

Set db = CurrentDb
strSQL =  "UPDATE balanceT SET closing_balance = " & v_final_balance
'Note: you will need a WHERE clause or else all the 
'records in the table will get updated

db.Execute strSQL, dbFailOnError

Set db = Nothing

If you don't want to get the warning that you are changing x number of records, you can add DoCmd.SetWarnin gs False right before the db.Execute line, but make sure to set it back to true at the end of the procedure.

Just curious, have you tested line 8 of your code? If it does work, I wasn't aware of the fact that you could do it that way.

[zmbd]

Seth:
Just curious, have you tested line 8 of your code? If it does work, I wasn't aware of the fact that you could do it that way.

It shouldn't work that way.
If OP will do a debug compile he should receive a "Compile Error: Expected Function or Variable" error and the ".openquery " will be selected.

[Seth Schrock]

I didn't see how it could work. Perhaps a DLookup() would be the simplest method to get the value the OP is looking for.

[zmbd]

Seth Schrock
I didn't see how it could work. Perhaps a DLookup() would be the simplest method to get the value the OP is looking for.

New topic new thread };-) ... and yes from what is posted, that would appear to be a good guess.

[osman7king]

Oky,
It works now

I used DLookup function as you suggest:

Private Sub save_Click()
On Error GoTo Err_save_Click
Dim v_balance As Double
Dim v_final_balance As Double

Dim strSQL As String
Dim db As DAO.Database

Set db = CurrentDb

DoCmd.RunCommand acCmdSaveRecord

DoCmd.OpenQuery "Q_get_balance"



v_balance = DLookup("colsing_balance", "Q_get_balance")
    
    
v_final_balance = v_balance - Forms![cash].[cash_out_usd] + Forms![cash].[cash_in_usd]
    
    
strSQL = "UPDATE balanceT SET colsing_balance = " & v_final_balance

     
db.Execute strSQL, dbFailOnError
     
Set db = Nothing


Exit_save_Click:
    Exit Sub

Err_save_Click:
    MsgBox Err.Description
    Resume Exit_save_Click
    
End Sub

Thanks for you efforts.
but How can I refresh my form after saving because the text boxes stay holding the values I entered.
I want to empty some text boxes after saving.

THANKS.

[zmbd]

Please limit to One Question Per Thread.
You can re-link back to this thread in the new one should you need to.