Change value in table when form field changes

[cardgunner]

This is my first bit of code I have written. So I apologize in advance.

In my order form if the amount gets changed then ask if you would like to update the producttbl with that amount.

So I have the following

Private Sub txtamount_Change()
    Dim Message, Buttons, Choice
      Message = "Do you want to Update the product group with this new price?" & Chr(10) & Chr(10)
      Buttons = vbYesNo
      Choice = MsgBox(Message, Buttons)
      
      If Choice = vbNo Then
      Exit Sub
    
    Else:

    Dim db As Database
    Set db = CurrentDb()
  
    db.Execute "UPDATE producttbl on producttbl.productname=Forms![Form1]!cboname set  producttble.amount=Forms![Form1]!txtamount "
     
    End If

End Sub

Table=producttble
fields=productname, amount

form=Form1
fields=cboname,txtamount

most of this I copied from sample code on unrelated db.

when I change the field and select yes I get a "syntax error in update statement"

Edit:

Sorry, the table of form1 is the ordertbl. The amount in that table will get updated when the form is closed or refreshed.

I'm looking to update the main producttbl if the user wants that new price to be the correct one.

 

[RuralGuy]

If your form was bound (had your table/query as recordsource) then all of this takes place automatically and no questions needed.

 

[cardgunner]

Rural,

Thanks for the reply. I think I answered it in my edit. You are right. When I change the value in the amoun t it does change the value in the underlying table, ordertbl.

The value for txtamount is supplied from a on_update from cboname. So cboname is a list box. They select what they want and it fills in txtamount with a the value from producttbl. That works!. However if the price changes, I would like to update producttbl from that form1 with the new price when thay overwrite the amount in the field.

I have a hard time creating a good explanation. I hope this helps.

 

[RuralGuy]

Are the two tables related and can you join them in a query so that both table's fields are available in the RecordSource of the form?

 

[cardgunner]

yes the two tables are related on ProductID. Yes I can join them in a query and have the recordsource of form1 be qryproduct_order.

I'm not sure what this will do but I hope you understand that the when an order is placed for say an apple and the user selects "apple" a value of that apple comes up as "2" in the txtamount. That is fine. Great. Those values are from the producttbl.

Now the user decides to sell it to the customer for either more or less. So he changes the value.
That values gets stored in the ordertbl as an order. Perfect.

At the time he changes the amount the owner would like to know if the user wants to update the product table with that new amount. Perhaps the price changed.

 

[cardgunner]

So I'm trying to figure what the code would be to have the on_change of txtamount update producttbl.amount with this new value

What is wrong with this?

What is wrong with the sql statement

db.Execute "UPDATE producttbl on producttbl.productname=Forms![Form1]!cboname set producttble.amount=Forms![Form1]!txtamount "

 

[RuralGuy]

If you have the two tables joind in the RecordSource of the form then an Update query is not necessary. You can set the value in the producttbl directly with [YourProductField] = Me.txtAmount
...using your field and control names of course. The OnChange event occurs for *every* keystroke in a control. Better to use the AfterUpdate event which only occurs once when the user changes that value of the control.

 

[cardgunner]

I think I get it. I'm rather a newbie to code.
so I redid the event to be

Private Sub txtamount_AfterUpdate()
    
    Dim Message, Buttons, Choice
      Message = "Do you want to Update the product group with this new price?" & Chr(10) & Chr(10)
      Buttons = vbYesNo
      Choice = MsgBox(Message, Buttons)
      
      If Choice = vbNo Then
      Exit Sub
    
    Else:

    Me.txtamount.Value = [producttbl].amount
    
    End If


End Sub

But I get an error( of course)! Run-Time error '424' Object required.

I'm sure it has something to do with
Me.txtamount.Value = [producttbl].amount

I tried it four or five different ways but nothing worked.

Like I said I'm new to code although I'm finding challenging.

What should that be?

 

[boblarson]

1. you do not need the colon : on the Else

2. The objects MESSAGE, BUTTONS, and CHOICE are not declared and that is your likely problem (EDIT: oops didn't see you sort of did declare them).

Dim sMessage As String
Dim lButtons As Long
Dim lChoice As Long

    
    Dim Message, Buttons, Choice
      sMessage = "Do you want to Update the product group with this new price?" 
      lButtons = vbYesNo
      lChoice = MsgBox(Message, Buttons)
      
      If Choice = vbNo Then
          Exit Sub
     Else
         Me.txtamount = Me.txtamount.OldValue    
    End If

EDITS - Also you were referencing a table which doesn't make sense in this context (which was probably your problem)

 

[RuralGuy]

Try:

Private Sub txtamount_AfterUpdate()
   
   If MsgBox("Do you want to Update the product group with this new price?", _
      vbExclamation + vbYesNo) = vbYes Then
      [producttbl].[amount] = Me.txtamount
   End If

End Sub

...IF the field in the [producttbl] is named amount and the table is joined in the RecordSource of the form and you have included the [amount] field in the query. You'll notice I shortened your code some.

 

[boblarson]

And actually, it may be easier to ask in the control's BEFORE UPDATE event as you can just cancel the change by using

Cancel = True

if they decide not to change.

 

[RuralGuy]

Bob,
I think the user wants the change in one area but maybe not permanently in the producttbl. In this case I believe the AfterUpdate event is a better choice.

 

[boblarson]

Bob,
I think the user wants the change in one area but maybe not permanently in the producttbl. In this case I believe the AfterUpdate event is a better choice.

If it is a bound field then it will change the underlying table anyway. If they want a different value then it will have to be either an unbound text box or a totally separate or field they are updating. If that is the case, then it may, or may not be possible to update the other table, even if in the record source of the form as it would have to be an updateable query. Also, if it was in the recordsource, wouldn't the applicable code be normally:

Me!amount

instead of

[producttbl].[amount]

 

[boblarson]

Oh, and if it was in the other table and they wanted to update it, then it would probably make sense (if it can't be in the form's recordsource) to go with their original SQL update, but written correctly.

Dim sSQL As String
sSQL = "UPDATE producttbl SET producttbl.amount =" & Me.txtAmount &  _
       " WHERE (((producttbl.productname)='" & Forms!Form1.cboname & "'));"

db.Execute sSQL, dbFailOnError

 

[RuralGuy]

I have not determined the names of the fields in each table or I would have been more accurate with my code. The OP implied they could join the two table in the RecordSource so I was proceeding on that basis. You are correct about the update query but as I said, I was moving down a different path at the time.

 

[cardgunner]

Rural Guy,

I tried your code

Private Sub txtamount_AfterUpdate()
    
   If MsgBox("Do you want to Update the product group with this new price?", _
      vbExclamation + vbYesNo) = vbYes Then
      [producttbl].[amount] = Me.txtamount
   End If

End Sub

however I get a

run-time error '2465':

Microsoft access can't find the field 'l' referred to in your expression.

this is in yellow when i debug
[producttbl].[amount] = Me.txtamount

I added producttbl to my record source query and I added that field to me form( Better safe then sorry)

I'll try messing around in the morning with the code as well as trying Bob's update query. If I can do this without an update query then that would be better right?

 

[RuralGuy]

Bind the [producttbl].[amount] field to an invisible control and then change the code to:
Me.InvisibleControlName = Me.txtamount
...as for this method vs an Update query, they would both be effective and I have no opinion as to which would be better.

 

[cardgunner]

Good Morning Rural,

That worked. Thank you very much.

Now I'm going to see if I can clean it up a little. You have been great. Thanks.

 

[RuralGuy]

Glad I could help. Enjoy the rest of the project.