Update query to save the ID of the last record in a field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created the following update query to run from a command button.

UPDATE tblLastEmpID SET tblLastEmpID.LastEmployeeID = (SELECT Max(Employees.EmployeeID) FROM Employees);

The purpose of the query is to take the last EmployeeID from my Employees table and store it in the table "tblLastEmpID". This field will be bound to a control on another form so the user can view it and then update it by clicking the command button.

When I run the query the following error message appears - Can any one help

"Operation must use an updateable query"
 
Hi,


You can't use MAX in an updateable query, in Jet. You have two simple
alternatives,

- use a temp table instead of the virtual table (SELECT MAX( ... )
.....)

- use: SET ... = DMAX("employeeID", "Employees")



Hoping it may help,
Vanderghast, Access MVP



Neal said:
I have created the following update query to run from a command button.

UPDATE tblLastEmpID SET tblLastEmpID.LastEmployeeID = (SELECT
Click to expand...
Max(Employees.EmployeeID) FROM Employees);
The purpose of the query is to take the last EmployeeID from my Employees
Click to expand...
table and store it in the table "tblLastEmpID". This field will be bound to
a control on another form so the user can view it and then update it by
clicking the command button.
 
Michel Walsh said:
Hi,


You can't use MAX in an updateable query, in Jet. You have two simple
alternatives,

- use a temp table instead of the virtual table (SELECT MAX( ... )
....)

- use: SET ... = DMAX("employeeID", "Employees")
Click to expand...

Let me guess: you can't use SUM either. Right?
 
Back
Top