update field with calculated result

[cjbnash]

Hi there everyone, hope you are well.

I am trying to get my head around something, I am the first to admit I am not the best with VBA or Access SQL so please forgive me if this is a stupid question.

I am trying to update a field with the result of what is in the field plus the contents of a unbound form field.

So if crm_order_det.Qty contained the number 5 and the form field (labelled qty) contained 7 I would like the field crm_order_det.Qty to end up with 12.

the code I am using

sql = " Update crm_order_det SET [Qty] = (" & _
        frm!Qty + crm_order_det.Qty & ") where crm_order_det.part = '" & frm![SearchResults] & _
        "' and order_id = " & frm![order_id] & ";"

DoCmd.RunSQL sql

However I get the following error

Run-time error '424': Object required.

Could anyone please help as I have been struggling with this for the last 24hrs.

Thanks in advance!

 

[plog]

What's your actual SQL? Once you have that, paste it into a query and see what happens.

 

[Rx_]

Just a suggestion when using math in a query, it can help troubleshooting.
I typically create variables to acquire the numbers, then perform the math outside the query. Adding numbers in SQL can sometimes run into data type conversion issues.
for example: frm!Qty + crm_order_det.Qty if one was a Null, what happens?
The SQL statement would be much cleaner if it were to update specific single numbers without the math involved in the SQL statement itself.

Private Sub cboWellStatus_Change()
       Dim strUpdateSQL As String
       Dim CurrentWellID As Integer
10     On Error GoTo errTrap
        CurrentWellID = [Forms]![Home_2]![lst_id_wells]
20        If ID_WellsStatus1_BeforeChange <> ID_WellsStatus1_After Change Then ' both variables from form level
30            strUpdateSQL = "UPDATE Wells SET Wells.R_OverRideRuleCheck = -1 WHERE (((Wells.ID_Wells)=" & CurrentWellID & " ));" ' CurrentWellID is a variable
40            CurrentDb.Execute strUpdateSQL, dbSeeChanges

Then, a debug.print of the strUpdateSQL (in my case) lets me past the SQL code in a Query as plog suggested.
You can put a break point on the docmd runsql line for yours
Run the code to the break point
In the immeidate window type in Debug.Print strUpdateSQL and return
And then copy that result - into a new query window (or post it to us)

 

[Galaxiom]

Show the line where you set the object variable frm to an actual form.