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.SetWarnings 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.