0

I am using the below code to update a field in my Database. 

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

The error I am receiving:

The expression you entered refers to an object that is closed or doesn't exist

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
ahmed
  • 45
  • 7
  • 3
    Not sure if you can use the Year name in that way. Probably is a reserved keyword and you need to add square brackets around it _[Year]_. Finally are you sure that the column Year contains string values in your database table? You are using a string to search for the values in the Year column and this could not produce the result expected. Try to remove the apostrophes around _strYear_ – Steve Nov 17 '19 at 15:07
  • 2
    **YEAR** is a reserved word. Take a look at this reference: (Learn about Access reserved words and symbols)[https://support.office.com/en-gb/article/learn-about-access-reserved-words-and-symbols-ae9d9ada-3255-4b12-91a9-f855bdd9c5a2] – JayV Nov 17 '19 at 15:19
  • 1
    VBA and VB.NET are ***not*** the same. Please tag questions correctly and *read the tag information* before selecting. The `access` tag does ***not*** refer to the Access databank software... – Cindy Meister Nov 17 '19 at 16:51
  • (https://stackoverflow.com/questions/58832269/getting-error-3340-query-is-corrupt-while-executing-queries-docmd-runsql) – andarvi Nov 18 '19 at 11:48
  • @andarvi: This question has nothing to do with the problem you linked. – Andre Nov 18 '19 at 16:56

1 Answers1

0

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

Andre
  • 26,751
  • 7
  • 36
  • 80