424 Object Required runtime error generated within vba module

[Shawn]

Hello. I am hoping that a second set of eyes can help me to resolve an error I am encountering.



I have attached two files; the first is the code of the module I executing, and the second is the interpreted SQL statement that is failing.

Line 79 within the module is where the failure is occurring, with error message 424 Object Required.

For the life of me, I cannot see which object may be required.

As background for this, the code defines two recordsets against the same data source.

The first record set is being used to identify records I wish to process, and the second recordset, within the loop, is extracting the data I need to evaluate in order to update the records in the first recordset.

This module is not accessing any fields from any other objects (like forms or reports), but is "simply" accessing data from the table within the database.

Thank you in advance.

Shawn

[June7]

Why don't you just post this info as text within question?

I don't want to count 79 lines - which line is number 79?

Are [Emp ID] and Locn text fields?

Strongly advise not to use spaces nor punctuation/special characters in naming convention.

[Welshgasman]

Run each in turn to see what if anything is produced?

FWIW Line 79 in Notepad++ is

Set EEStartandEndRS = CATSDailydb.OpenRecordset(SQLString, dbOpenDynaset)

I would have used CurrentDB() or set a db object to CurrentDB() ?

Edit: OK, I see you have further up and have used that in a previous SQL statement, so it is not that.

[Shawn]

Thank you June 7. I thought it might be easier to see the line numbers in a separate rather than in the body of the question.

Yes, [Empl ID] and Locn are text fields.

Line 79 is: Set EEStartandEndRS = CATSDailydb.OpenRecordset(SQLString, dbOpenDynaset)

Execution is failing on the first pass through the data, and the second attachment contains the data, formatted as I would expect, at the point of failure.

Shawn

[Welshgasman]

Do you actually need all those left brackets with the Criteria each time?

SELECT [tbl - CATS Activity].[Empl Id],
       [tbl - CATS Activity].[Locn],
       [tbl - CATS Activity].[Balance Date],
       Min([tbl - CATS Activity].[Start Time Adj]) as Min_Start_Time_Adj,
       Max([tbl - CATS Activity].[End Time Adj]) As Max_End_Time_Adj
  FROM [tbl - CATS Activity]
 WHERE ((([tbl - CATS Activity].[Empl Id])="1000981") and
        (([tbl - CATS Activity].[Locn])="4025") and
        (([tbl - CATS Activity].[Balance Date])=#6/22/2021#) and
        (([tbl - CATS Activity].[Worked/Lost])="Work/Pay"))
GROUP BY [tbl - CATS Activity].[Empl Id], [tbl - CATS Activity].[Locn], [tbl - CATS Activity].[Balance Date];

Have you run each in turn to see if they work?

[June7]

I opened text file and did not see line numbers. Forgot, should have used Notepad++, still better to post code in question and identify the line triggering error - don't make work for readers any more than necessary.

The extra parentheses aren't really necessary but Access throws them in anyway and I expect OP copy/pasted from query SQLView.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Shawn]

That volume of brackets is a technique I picked up from the SQL generated by the Query by Example query builder feature of Access.

They are not all needed. Originally, I did not have the brackets around the variable names (ie. i originally had [tbl - CATS Activity].[Emp Id]="1000981") and when that failed, I added the additional brackets, thinking that if the query builder added them, then maybe I should too.

It might be presuming too much, but it seems to me by that question, you are not seeing anything that immediately jumps out as being incorrect. It might be odd, but not incorrect.

I am wondering if in all of this, there might be some sort of contention between the recordsets with the manner in which I attempt to open them. Does that make sense?

[Shawn]

Thank you June7.

Your suspicion is correct. That is what I did after the initial failure.

I will see about reducing the volume of data in the tables before compressing and attaching.

[June7]

( ) are parentheses
[ ] are brackets
{ } are braces

( ) are used to structure SQL statements, Access is really picky about them when joining multiple tables, they are important when mixing AND and OR operators, otherwise, Access throws in more than are needed in WHERE clause

[ ] are needed for object names that use spaces, punctuation/special characters, or are reserved words

[Shawn]

Attached is a zipped version of the database.

I have restricted the data to instances for 4 different Empl Id values.

There are a number of linked tables/datasets in this that I expect are not included in the compression process, but none of them are used in the module I am attempting to execute.

I expect to be making use of them when my runtime error is resolved.

The single module Assess_EE_Trady_and_HE contains the source code for the function.

Thank you,

Shawn

[davegri]

The original 424 error is because the database is never dimmed:

Function mdl_Assess_EE_Tardy_and_HE()
On Error GoTo Function_error


DoCmd.Hourglass True


Dim CATSActivityRS As Recordset
Dim EEStartandEndRS As Recordset
'''''
Dim CatsDailydb As DAO.Database
'''''
Dim Start_Time As String
Dim End_Time As String


Dim TardyCount As Long
Dim HECount As Long
Dim ErrCount As Long

[ssanfu]

Also change

Dim CATSActivityRS As DAO.Recordset
Dim EEStartandEndRS As DAO.Recordset

---------------------------------------------------

You have declared

Dim ErrorFlag As Boolean

but in code you have

   If Error_Flag = False Then

note the underscore...

--------------------------------------------------------------

      If EEStartandEndRS![Max_End_Time_Adj] < CATSActivityRSRS![End Time] Then

Too many RS???

---------------------------------------------------------------

Do you have

Option Compare Database
Option Explicit

as the top two lines of EVERY Module? If not, you should!!

----------------------------------------------------------------

Delete this line

    CATSDailydb.Close

You did not open the dB, so you do not close it.

    CATSActivityRS.Close
    EEStartandEndRS.Close
    CATSDailydb.Close  'delete

    Set CATSActivityRS = Nothing   'ADD
    Set EEStartandEndRS = Nothing   'ADD
    Set CATSDailydb.Close = Nothing   'ADD


DoCmd.Hourglass False
    MsgBox Err.Description
    Exit Function

[Shawn]

Well that was silly, wasn't it.

Thank you for catching that.

Shawn

[Shawn]

Thank you for the suggestions.

And thank you to all who took time to review this, and offer suggestions.

All will be put in my back pocket.

Shawn

[Welshgasman]

The original 424 error is because the database is never dimmed:

Function mdl_Assess_EE_Tardy_and_HE()
On Error GoTo Function_error


DoCmd.Hourglass True


Dim CATSActivityRS As Recordset
Dim EEStartandEndRS As Recordset
'''''
Dim CatsDailydb As DAO.Database
'''''
Dim Start_Time As String
Dim End_Time As String


Dim TardyCount As Long
Dim HECount As Long
Dim ErrCount As Long

So why did it not fail on line 36?
Set CATSDailydb = CurrentDb()