How to correctly reference the primary key in a WHERE clause

[easyrider]

What is the best way to reference the primary key in a WHERE clause? I want to update a specific field in a Record that is currently shown on the Form.

[orange]

If the form is bound, you could update the control on the form.
Perhaps there's more to your post.

[easyrider]

I've seen countless examples that shows the WHERE clause having a literal value in it such as WHERE SomeField = 'x'. What I'm wondering is how you reference the ID field of the record so that the SQL UPDATE statement only changes that record.

For example, if I have a table of employee salaries and a form to display this information, I would like to put a button on the form that (1) unlocks the Salary text box which gives the user the option to change the data. Then when they leave that control (On Exit) the following command runs:

DoCmd.RunSQL UPDATE Salary_T SET Salary = NewSalary WHERE ........................

How do I write the code to have the WHERE clause change only the salary in the current record?

[moke123]

If you have a field on your form named EmployeeID it would be something like

... Where EmployeeID = " & me.EmployeeID

You can use the Me keyword when in a forms module rather than Forms("YourFormName").EmployeeID. Me refers the the Form.

If referencing the new salay textbox it may be like below.

dim strSql as string

strSql = "UPDATE Salary_T SET Salary = " & Me.NewSalary  & " Where EmployeeID = " & me.EmployeeID

Debug.Print strSql

Currentdb.Execute strSql,dbFailOnError

you may have to add delimiters depending on datatype

[easyrider]

Thanks, that worked!

[ssanfu]

I've seen countless examples that shows the WHERE clause having a literal value in it such as WHERE SomeField = 'x'. What I'm wondering is how you reference the ID field of the record so that the SQL UPDATE statement only changes that record.

For example, if I have a table of employee salaries and a form to display this information, I would like to put a button on the form that (1) unlocks the Salary text box which gives the user the option to change the data. Then when they leave that control (On Exit) the following command runs:

DoCmd.RunSQL UPDATE Salary_T SET Salary = NewSalary WHERE ........................

How do I write the code to have the WHERE clause change only the salary in the current record?

Why do you not just edit the Salary text box? Why use the code?


The button would unlock the Salary text box, the user enters the NewSalary amount, the control exit event locks the Salary text box again.

Just curious.......

[easyrider]

Why do you not just edit the Salary text box? Why use the code?


The button would unlock the Salary text box, the user enters the NewSalary amount, the control exit event locks the Salary text box again.

Just curious.......

Steve, thanks for the suggestion however my question was a simplified version of a more complex solution that I was looking for in regards to the WHERE clause syntax. I try to keep things short & sweet when posting questions as opposed to going into long, detailed explanations and pasting long lines of code.

I was amazed at how many examples there are out there that only show the WHERE clause with a literal value.

[Welshgasman]

I was amazed at how many examples there are out there that only show the WHERE clause with a literal value.

Well it is only one step further to use a variable?
Of course then you need to allow for dates or strings, or strings with ' in them.