Quick help with Update code

[ddikeht]

I am trying to update a table with values from a Form. Here is the code that I am using on the command button;

CurrentDb.Execute "Update Customer_Notes set Customer_Notes.Customer_Number = '" & Me.CustomerNumber.Value & "'"

CurrentDb.Execute "Update Customer_Notes set Customer_Notes.Comments= '" & Me.Comments.Value & "'"

The values are coming from the CustomerNumber and Comments controls of the Form. It works, but instead of just updating the record that corresponds to the Customer number, it is updating all of the records on the table with the new value, which is not good. Do I need to add a 'where' statement? If so, could anyone give me a clue on how to add it. This is the last piece, and I will be very happy to get it completed.

Thanks in advance for the assistance, the forum has been very usefull!

 

[RuralGuy]

Why are you not using a bound form?

 

[ddikeht]

I tried using a bound form, but the database will be accessed over the network and because of its size, the performance was unacceptable.

 

[RuralGuy]

Using unbound forms requires quite a bit of extra coding to make up for what Access is doing for you already with a bound form. Here's a link to some performance suggestions. I assume you have split the db and everyone has their own copy of the FrontEnd. Are you limiting your recordsets wherever possible? And YES you need a WHERE clause so that Access will update only the record you want.

 

[ddikeht]

I tried this
CurrentDb.Execute "Update Customer_Notes set Customer_Notes.Customer_Number = '" & Me.CustomerNumber.Value & "' WHERE Customer_Notes.Customer_Number = '" & Me.CustomerNumber & "';"

But it keeps giving me a data mismatch error. Grrr

 

[RuralGuy]

What WHERE clause did you use to set the Customer_Number?

 

[ddikeht]

Rural,

Yes DB is split. Finally figured it out, and thanks for your help. It should have been

CurrentDb.Execute "Update Customer_Notes set Customer_Notes.Customer_Number = '" & Me.CustomerNumber.Value & "' WHERE Customer_Notes.Customer_Number = " & Me.CustomerNumber & ";"

CurrentDb.Execute "Update Customer_Notes set Customer_Notes.Comments= '" & Me.Comments.Value & "' WHERE Customer_Notes.Customer_Number = " & Me.CustomerNumber & ";"

 

[RuralGuy]

CurrentDb.Execute "Update Customer_Notes set Customer_Notes.Customer_Number = '" & Me.CustomerNumber.Value & "' WHERE Customer_Notes.Customer_Number = " & Me.CustomerNumber & ";"

This SQL seems like a circular reference. I don't see how it can work properly. You do know you can set more than one value with an Update query right?