sql - 更新 SQL 错误：您输入的表达式引用了已关闭或不存在的对象

Question

我正在使用下面的代码来更新我的数据库中的一个字段。

Dim db             As Database
Dim LSQL           As String
Dim Lrs            As DAO.Recordset

Set db = CurrentDb()

LUpdate = "Update tblNumber"
LUpdate = LUpdate & " set LastNbr = " & Lrs("Last_Nbr") + 1
LUpdate = LUpdate & " where Year = '" & strYear& "'"

CurrentDb.Execute LUpdate, dbFailOnError

我收到的错误：

您输入的表达式引用了一个已关闭或不存在的对象

标签： sqlvbams-access

Answer 1

You haven't initialized the recordset variable Lrs.

So using Lrs("Last_Nbr") will throw this error.

If you want to increase the value in the table by one, simply do

Update tblNumber
Set LastNbr = Last_Nbr + 1
Where [Year] = ...

See also here:
How to debug dynamic SQL in VBA