Solved Run-time error '91' (Object variable or With block variable not set)

[alan2013]

I'm starting to try out some VBA that I haven't used before, for processing some records. But I'm getting a Run-time error '91' (Object variable or With block variable not set) error message. I've tried to resolve the problem by searching around on the www and by altering the code in various ways, but I'm not getting anywhere with it.

I've attached the very simple (one Table; one Query; one Form; one Module) database I'm currently experimenting with, in case anyone can take a look and spot where I'm going wrong. Might it be a problem in the References ? I'm using :
Visual Basic for Applications
Microsoft Access 16.0 Object Library
Microsoft DAO 3.6 Object Library
OLE Automation
Microsoft ActiveX Data Objects 6.0 Library
Microsoft Outlook 14.0 Object Library
Microsoft Scripting Runtime

Any guidance greatly appreciated. Thanks


EDIT : Meant to post in 'Modules & VBA'

 

[Gasman]

Should just show the code within code tags?
Not even sure where to start? You have a form with a run button on it, but no event for it?

Give us a clue as where to go looking, else you are going to discourage people from trying to help.

Trying to open ajbGlobal and get

 

[Gasman]

If this is the code?

Public Sub procProcess1()
On Error GoTo ErrorHandler
Dim wrkCurrent As DAO.Workspace
Set wrkCurrent = DBEngine.Workspaces(0)
Dim MyDatabase As DAO.Database
Set MyDatabase = CurrentDb
DAO.DBEngine.SetOption dbMaxLocksPerFile, 15000

Dim rstRecordsToProcess As DAO.Recordset
Set rstRecordsToProcess = MyDatabase.OpenRecordset("SELECT * FROM qryRecordsToProcess")
wrkCurrent.BeginTrans
rstRecordsToProcess.MoveFirst
Do Until rstRecordsToProcess.EOF
    rstRecordsToProcess!Processed = True
    rstRecordsToProcess.Edit
    rstRecordsToProcess.Update
    rstRecordsToProcess.MoveNext
Loop
wrkCurrent.CommitTrans
rstRecordsToProcess.Close
MyDatabase.Close
wrkCurrent.Close
Set rstRecordsToProcess = Nothing
Set MyDatabase = Nothing
Set wrkCurrent = Nothing
Exit Sub
ErrorHandler:
    MsgBox "Description : " & rstRecordsToProcess!Description & ", Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
    Exit Sub
End Sub

You need to edit first before amending a field in a recordset?
You are jumping in at the deep end. I have never used Transactions Not sure it is need here, as you only commit at the end of the file?
The experts here will know better.

So report back on what line the error occurs please.

 

[Gasman]

Appears to be on the Select
If you run the query manually (should always do that to make sure it works first) it prompts for Field3 which does not exist?

SELECT tblRecords.Record_ID, tblRecords.Description, tblRecords.Process, tblRecords.Field3 AS Expr1
FROM tblRecords
WHERE (((tblRecords.Description) Like "*to be processed") AND ((tblRecords.Process)=True));

 

[Gasman]

Here is what works IF you correct your query.
As it stands now, you will get another error. See if you can work hat one out.
Also ALWAYS have Option Explicit at the top of every module. I have added it this time.

Option Compare Database
Option Explicit

Public Sub procProcess1()
    On Error GoTo ErrorHandler
    Dim wrkCurrent As DAO.Workspace
    Dim MyDatabase As DAO.Database
    Dim rstRecordsToProcess As DAO.Recordset
    DAO.DBEngine.SetOption dbMaxLocksPerFile, 15000
   
    Set MyDatabase = CurrentDb
    Set wrkCurrent = DBEngine.Workspaces(0)
    Set rstRecordsToProcess = MyDatabase.OpenRecordset("SELECT * FROM qryRecordsToProcess")
   
    wrkCurrent.BeginTrans
    rstRecordsToProcess.MoveFirst ' Not sure this is needed as you would be at first record if any exist?
    Do Until rstRecordsToProcess.EOF
        rstRecordsToProcess.Edit
        rstRecordsToProcess!Processed = True 'Now moved to after edit
        rstRecordsToProcess.Update
        rstRecordsToProcess.MoveNext
    Loop
   
    wrkCurrent.CommitTrans

    rstRecordsToProcess.Close
    MyDatabase.Close
    wrkCurrent.Close
    Set rstRecordsToProcess = Nothing
    Set MyDatabase = Nothing
    Set wrkCurrent = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "Description : " & rstRecordsToProcess!Description & ", Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
    Exit Sub
End Sub

Doing what you are attempting, I'd just have the query sql in that open recordset, and do away with that query?

 

[alan2013]

Here is what works IF you correct your query.
As it stands now, you will get another error. See if you can work hat one out.
Also ALWAYS have Option Explicit at the top of every module. I have added it this time.

Option Compare Database
Option Explicit

Public Sub procProcess1()
    On Error GoTo ErrorHandler
    Dim wrkCurrent As DAO.Workspace
    Dim MyDatabase As DAO.Database
    Dim rstRecordsToProcess As DAO.Recordset
    DAO.DBEngine.SetOption dbMaxLocksPerFile, 15000
  
    Set MyDatabase = CurrentDb
    Set wrkCurrent = DBEngine.Workspaces(0)
    Set rstRecordsToProcess = MyDatabase.OpenRecordset("SELECT * FROM qryRecordsToProcess")
  
    wrkCurrent.BeginTrans
    rstRecordsToProcess.MoveFirst ' Not sure this is needed as you would be at first record if any exist?
    Do Until rstRecordsToProcess.EOF
        rstRecordsToProcess.Edit
        rstRecordsToProcess!Processed = True 'Now moved to after edit
        rstRecordsToProcess.Update
        rstRecordsToProcess.MoveNext
    Loop
  
    wrkCurrent.CommitTrans

    rstRecordsToProcess.Close
    MyDatabase.Close
    wrkCurrent.Close
    Set rstRecordsToProcess = Nothing
    Set MyDatabase = Nothing
    Set wrkCurrent = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "Description : " & rstRecordsToProcess!Description & ", Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
    Exit Sub
End Sub

Doing what you are attempting, I'd just have the query sql in that open recordset, and do away with that query?

Thanks, Gasman

 

[MajP]

Public Sub procProcess1()
  Dim strSql As String
  strSql = "Update tblRecords Set Processed = true where Record_ID in (Select Record_ID from qryRecordsToProcess)"
  CurrentDb.Execute strSql
end sub

Or simply

Public Sub procProcess1()
 currentdb.execute "Update tblRecords Set Processed = true where Record_ID in (Select Record_ID from qryRecordsToProcess)"
end sub