Update Record on Form Button Click

[Guest]

I have a form, EnterViewRequests, that is based on a query. All the query
does is sort the records to be displayed in the form, and uses only one table
- "Requests".

On EnterViewRequests, I have a button labeled "Delete Access". When the form
is opened, all the fields are locked. Clicking the "Delete Access" button
unlocks a few of the fields to allow data entry.

What I want to have happen is, when I click the "Delete Access" button, I
want the employee ID currently displayed on the form to have their status
changed from "Active" to "Inactive" in the "Employees" table. How do I modify
the button's VBA to do this, especially when the Employees table is not
linked in any way to the form or the query and table on which the form is
based?

Many thanks in advance,
Gwen H

 

[Jeff L]

You said you have an employeeID displayed on your form so your query
would be:

DoCmd.RunSql "Update Employees " & _
"Set Status = 'Inactive' " & _
"Where EmployeeId = " & Me.EmployeeID

Hope that helps!

 

[Guest]

You can either write the SQL to update the correct record in the employee
table or write an update query that does the same thing, then execute the
query in the Click event of the button.

Dim strSQL As String

strSQL = "UPDATE EmployeeTable SET EmployeeTable.Status = 'InActive'
WHERE EmployeeTable.EmployeeId = '" & Me.txtEmpID & "';"
DoCmd.Execute(strSQL), dbFailOnError

Note: This is untested air code, but it should give you the idea.

 

[Guest]

Thank you! That did the trick with no problems whatsoever.