Object Required (RunTimeError = 424)

imfarhan

Board Regular
Joined
Jan 29, 2010
Messages
100
Sub chk()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Query3", dbOpenDynaset)

If rs.RecordCount = 0 Or rs.Fields(Format("maxof_EndDateTime", YYYYMMDD").Value) <> Format(Now(), "YYYYMMDD") Then
'03/12/09 add the new check if date is not equal to today's date
'Not completed yet
rs.Close
Set rs = Nothing
TestInfoCom = NothingToDo
Exit Function

' Else: MsgBox ("aaaa")

End If
End Sub
I've slightly change the condition , this function should be failed whten the REcordCount = 0 its perfectly fine but now I would like to add even if the value exist but its not equal to today's date then it should be failed as well, I'm getting following error when I run

Run-time error '424'
Object Required

I appreciate if any one can help
Many thanks
FArhan
 

james_lankford

Well-known Member
Joined
Jan 11, 2009
Messages
1,171
you shouldn't use If rs.RecordCount = 0
the RecordCount property can't be trusted
under some circumstances it comes up as -1 even when there are records
You should use the EOF and BOF properties instead

but your problem is the way you have his laid out
rs.Fields(Format("maxof_EndDateTime", YYYYMMDD").Value)

it should be
Format( rs.Fields("maxof_EndDateTime").Value, "YYYYMMDD" )

but I don't think you even need to format. Can't you just say

if rs.Fields("maxof_EndDateTime").Value <> Date()
 

imfarhan

Board Regular
Joined
Jan 29, 2010
Messages
100
Thanks James for swift response

Var1 = Format(rs.Fields("maxof_EndDateTime").Value, "YYYYMMDD")
Var2 = Format(Now(), "YYYYMMDD")

MsgBox (Var1)
MsgBox (Var2)

Results:-

I'm getting 00:00:00 in both Msgbox ....don't know why
Even if I do withouth format I don't get anything....
and the feild "maxof_EndDAteTime" keep only one values i.e. 27/08/2010 04:44:09

but I would like to match the YYYYMMDD against today's date i.e. NOW() thats why I would like to use format mask
awaiting for your kind response.
Many thanks
Farhan
 

james_lankford

Well-known Member
Joined
Jan 11, 2009
Messages
1,171
I made an example table and this worked for me

I made one table and named it example1
The table has 2 fields in it. The first is named id and is an autonumber. The 2nd is named maxof_EndDAteTime and is a Date/Time field.

Then I put your value, 27/08/2010 04:44:09, in the maxof_EndDAteTime field.
But after I typed it in Access automatically changed it to 8/27/2010 04:44:09 because in the US it defaults to month/day/year.

Then I put this code in a module and ran it. It works fine.

Code:
Option Compare Database
Option Explicit

Sub doit()
    
    Dim var1 As Variant
    Dim var2 As Variant
    
    Dim db As Database
    Dim rs As Recordset
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("select * from example1", dbOpenSnapshot)
    If Not rs.BOF Then
        Do While Not rs.EOF
            var1 = Format(rs.Fields("maxof_EndDateTime").Value, "YYYYMMDD")
            var2 = Format(Now(), "YYYYMMDD")
            MsgBox (var1)
            MsgBox (var2)
            rs.MoveNext
        Loop
    End If
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
End Sub
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,551
Office Version
2013
Platform
Windows
Var1 = Format(rs.Fields("maxof_EndDateTime").Value, "YYYYMMDD")
Var2 = Format(Now(), "YYYYMMDD")

MsgBox (Var1)
MsgBox (Var2)

Results:-

I'm getting 00:00:00 in both Msgbox ....don't know why
Even if I do withouth format I don't get anything....
and the feild "maxof_EndDAteTime" keep only one values i.e. 27/08/2010 04:44:09

but I would like to match the YYYYMMDD against today's date i.e. NOW() thats why I would like to use format mask
awaiting for your kind response.
Many thanks
Farhan
Your code to get these dates is enclosed in an IF clause:
Code:
[COLOR="Red"][B]If[/B][/COLOR] rs.RecordCount = 0 Or rs.Fields(Format("maxof_EndDateTime", YYYYMMDD").Value) <> Format(Now(), "YYYYMMDD") Then
So it seems likely that the conditions are not met and you never actually assign any values to var1 or var2. Query3 may not be working as expected.
 

Forum statistics

Threads
1,081,706
Messages
5,360,767
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top