Reset field value for specific fields for all records in a table

[robsisk]

Greeting all,
I have a db where I am wanting to reset or clear the values for specific fields for all records in a table. For example, I have a vendor table that tracks the amount of marketing dollars each vendor has committed. At the end of the year, I want the DB user to be able to hit a button and reset that value to $0.00. in the vedor table. I have several fields like this and would like to automate this task with vb code or a macro. I don't know much vb code but I can copy and paste like a mad man. Can anyone help

 

[sfreeman@co.mer]

Like this?....

Assuming your table is named tblMain, and the field you want to update is called 'Dollars', then on your form and in the code behind the update button:


'*************************
Private Sub Command0_Click()
Dim strSQL As String

strSQL = "UPDATE tblMain SET tblMain.Dollars = 0;"

DoCmd.RunSQL strSQL

End Sub
'*************************

HTH

 

[robsisk]

Sam,
Thanks for responding. I tried your code by putting a command button on the form itself and renaming the table and field as follows:

Private Sub Command167_Click()
Dim strSQL As String

strSQL = "UPDATE Vendor Table SET Vendor Table.AMOUNT COMMITTED = 0;"

DoCmd.RunSQL strSQL

End Sub

I get the an error that reads "run-time error 3144" "syntax error in update statement"

If I attempt to debug it takes me right tooCmd.RunSQL strSQL

 

[robsisk]

Never Mind!
I found a different solution to the problem. I simply used an update query and it changed all fields to whatever value i wanted. Sometimes the easiest solutions are not always evident.