Docmd.RunSQL Question

[GregSmith]

I am trying to update a table called employees.
The field I am trying to update in the table is called Notes.
It get's the information from a text box on the form called
Me.[Notes]

Here is my code:
DoCmd.RunSQL "UPDATE Employees SET [" & Notes & "] = Me.[Notes];"

And idea's on how to get it running?

 

[Rich]

Look up the Where clause, also it's not a good idea to have identical table and field names

 

[dcx693]

That syntax doesn't look quite right either. Are you trying to set a field called Notes in the Employees table equal to the value entered in the field called Notes on the current form?

If so try:
DoCmd.RunSQL "UPDATE Employees SET [Notes]=" & Me.[Notes] & ";"

 

[GregSmith]

Here is my new code:

DoCmd.RunSQL "UPDATE Employees SET [Notes]=" & Me.[NotesField] & " WHERE employees.name = ME.[EmployeeName];"

Employees is the table name
Notes is the field inside the table
Me.[NotesField] a field on the form that you fill in.
Me.[EmployeeName] is the ID # of the name listed in the table.

I still cannot get it to update correctly.
Any more ideas??

 

[dcx693]

Try this:
DoCmd.RunSQL "UPDATE Employees SET [Notes]=" & Me.[NotesField] & " WHERE employees.name=" & Me.[EmployeeName]& ";"
Note: When you reference a field from a form, it needs to be outside the quotations marks.

Note also: the above code will only work if both the Me.[NotesField] and Me.[EmployeeName] are numeric fields. SQL expressions referring to numbers are in the form [field]=number as in [ID]=8. SQL expression referring to text are n this form: [field]='text' as in [Name]='John'.

So, if the first field in your update query is a text field, you need to use this syntax:
DoCmd.RunSQL "UPDATE Employees SET [Notes]='" & Me.[NotesField] & "' WHERE employees.name=" & Me.[EmployeeName]& ";"

 

[GregSmith]

I ended up back at the beginning again.

Here is my code:

DoCmd.RunSQL "UPDATE Employees SET [Notes] = '" & Me.[NoteField] & "';"

This updates ALL the fields in the table with the information.

How do I add the WHERE command to look at the ID field of the table and match what is listed in me.[employeename] on the form?

The ID field and me.[employeename] are numbers and not strings.

Also, with the above code, I get this message:

Write Conflict:
The record has been changed by another user ....
Save Record, Copy to Clipboard, Drop Changes

How can I get it to not display this message?

 

[pono1]

Greg,

See Dcx693's first UPDATE query sample in the previous post for the syntax when using a number as the criterion in a WHERE clause.

In the following portion of his sample...

	WHERE employees.name

"employees" is the table name while "name" is a field name. You will have to swap in your table name and field name if they are different from the sample's.

But that message you're getting -- the Write Conflict -- suggests you are fighting with your own form over the same record when running the UPDATE query.

Perhaps a simple save command will do what you want instead...

	Docmd.RunCommand acCmdSaveRecord

Regards,
Tim
PS. There seems to be a gargantuan penguin loose somewhere in Brooklyn.