How do I make a button that runs an update query in Access 2016

Question

I have an update query with the following SQL

UPDATE UserListTest 
SET UserListTest.UserName = [forms]![EmployeeAdd]![NewFullName], 
UserListTest.AccessCode = [Forms]![EmployeeAdd]![NewCode]
WHERE (((UserListTest.AccountNumber)=[forms]![EmployeeAdd]![NewUserAccount]) 
AND ((UserListTest.UserNumber)=[forms]![EmployeeAdd]![NewUserNumber]));

The code should update the table "UserListTest" and replace the values in userName and AccessCode with values from the form, at the records where AccountNumber = NewUserAccount and UserNumber = NewUserNumber.

I currently have a button in a form that is set to run the query, but whenever it is pressed, it only pulls up the query with the previous entries information. The only way that I have been able to get it to update the records in the table is by pressing the button, then going to design view in the query, and pressing the run button in the top left. I've tried hitting refresh, but that just appears to clear the fields in the update query.

I want to be able to make a button that does all of that automatically, without me having to go the design view every time.

Any help is appreciated.

What is your current code of the button? – Andre May 16 '17 at 22:23 — Andre, May 16 '17 at 22:23

score 1 · Answer 1 · answered May 17 '17 at 02:03

You can do this, for instance, this way:

CurrentDb.Execute "UPDATE UserListTest " & _
    "SET UserName = '" & Me.NewFullName & "', AccessCode = '" & Me.NewCode & "' " & _
    "WHERE AccountNumber=" & Me.NewUserAccount & " AND UserNumber=" & Me.NewUserNumber, dbFailOnError

I assumed, that the button located in EmployeeAdd form, UserName and AccessCode columns are text, AccountNumber and UserNumber - Number data type. Otherwise add/remove single quotes.

If the query is saved, you can just run it:

CurrentDb.Execute "qryMyQueryName", dbFailOnError

How do I make a button that runs an update query in Access 2016

1 Answers1