Me.RecordsetClone causes Run-time error 3420 (1 Viewer)

ppataki

Registered User.
Local time
Today, 07:19
Joined
Sep 5, 2008
Messages
267
Dear All,

I have the below code in a subform
It is tied to a beforeupdate event of a field called HolidayDate
When the code runs for the first time it is OK (displays the msgbox), but if I modify the HolidayDate then I get the Run-time error 3420 Object invalid or no longer set

Could you please advise?
nb. I tried the code at an afterupdate event as well, same error

Code:
Private Sub HolidayDate_BeforeUpdate(Cancel As Integer)

Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "[HolidayDate] =" & Format(Me.HolidayDate, "\#mm\/dd\/yyyy\#")
If rst.NoMatch Then
Else
MsgBox "Ezt a napot már korábban felhasználtad!/You have already taken this day!", vbCritical
Me.Undo
Exit Sub
End If
rst.Close
...........

Many thanks
 

spikepl

Eledittingent Beliped
Local time
Today, 15:19
Joined
Nov 3, 2010
Messages
6,144
The joy of Exit Sub.!

Your record set is not closed, if you hit the message box. I m not sure if that is the cure, because I have never coded like this :)
 

ppataki

Registered User.
Local time
Today, 07:19
Joined
Sep 5, 2008
Messages
267
If I delete Exit Sub then I get another error message as my code continues opening another recordset
any suggestions pls?
cheers
 

spikepl

Eledittingent Beliped
Local time
Today, 15:19
Joined
Nov 3, 2010
Messages
6,144
Yeah. Go through the logic of your code and make sure that the record set gets closed no matter what you do.

But I suspect that this is not the cause of your error but something else. We'll see.
 

ppataki

Registered User.
Local time
Today, 07:19
Joined
Sep 5, 2008
Messages
267
I have added rst.close to the code, still the same error

Code:
Dim Rst As Recordset
Set Rst = Me.RecordsetClone
Rst.FindFirst "[HolidayDate] =" & Format(Me.HolidayDate, "\#mm\/dd\/yyyy\#")
If Rst.NoMatch Then
Else
MsgBox "Ezt a napot már korábban felhasználtad!/You have already taken this day!", vbCritical
Me.Undo
Rst.Close
Exit Sub
End If
 

spikepl

Eledittingent Beliped
Local time
Today, 15:19
Joined
Nov 3, 2010
Messages
6,144
But now you don't close it if rst.Nomatch. This is not difficult - trace the logic, and check it.

And you problem can be elsewhere. Show the rest of the code, but AFTER you have fixed and tested it.
 

spikepl

Eledittingent Beliped
Local time
Today, 15:19
Joined
Nov 3, 2010
Messages
6,144
If the error persists - show which line the debugger marks
 

ppataki

Registered User.
Local time
Today, 07:19
Joined
Sep 5, 2008
Messages
267
OK, I have put rst.close after nomatch as well, but unfortunately it still fails
Please find the whole code below:

Code:
Private Sub HolidayDate_BeforeUpdate(Cancel As Integer)
Dim Rst As Recordset
Set Rst = Me.RecordsetClone
Rst.FindFirst "[HolidayDate] =" & Format(Me.HolidayDate, "\#mm\/dd\/yyyy\#")
If Rst.NoMatch Then
Rst.Close
Else
MsgBox "Ezt a napot már korábban felhasználtad!/You have already taken this day!", vbCritical
Me.Undo
Rst.Close
Exit Sub
End If

Dim rs1 As Recordset
Set rs1 = CurrentDb.OpenRecordset("qry_all_holidays", dbOpenDynaset)
rs1.FindFirst "[Holidays] =" & Format(Me.HolidayDate, "\#mm\/dd\/yyyy\#")
If rs1.NoMatch Then
Else
MsgBox "A választott nap munkaszüneti nap, kérlek válassz egy másikat!/This date is a holiday, please choose another one!", vbCritical
Me.Undo
Exit Sub
End If
rs1.Close

Dim rs2 As Recordset
Set rs2 = CurrentDb.OpenRecordset("tbl_current_workday_list", dbOpenDynaset)
rs2.FindFirst "[Workdays] =" & Format(Me.HolidayDate, "\#mm\/dd\/yyyy\#")
If rs2.NoMatch And (Weekday(Me.HolidayDate, vbMonday) = 6 Or Weekday(Me.HolidayDate, vbMonday) = 7) Then
MsgBox "A választott nap munkaszüneti nap, kérlek válassz egy másikat!/This date is a holiday, please choose another one!", vbCritical
Me.Undo
Exit Sub
Else
End If
rs2.Close
End Sub
 

ppataki

Registered User.
Local time
Today, 07:19
Joined
Sep 5, 2008
Messages
267
it marks the line
Rst.FindFirst "[HolidayDate] =" & Format(Me.HolidayDate, "\#mm\/dd\/yyyy\#")
 

spikepl

Eledittingent Beliped
Local time
Today, 15:19
Joined
Nov 3, 2010
Messages
6,144
put

debug.print "***" & me.holidaydate

just before the failing line, and look in the immediate window for what comes out when it fails
 

spikepl

Eledittingent Beliped
Local time
Today, 15:19
Joined
Nov 3, 2010
Messages
6,144
Dim Rst As Recordset Set Rst = Me.RecordsetClone
' you debug-print HERE?

Rst.FindFirst "[HolidayDate] =" & Format(Me.HolidayDate, "\#mm\/dd\/yyyy\#") If Rst.NoMatch Then Else MsgBox "Ezt a napot már korábban felhasználtad!/You have already taken this day!", vbCritical Me.Undo Rst.Close Exit Sub End If

If Yes, then I have no clue. Can you post a stripped/zipped db?
 

ppataki

Registered User.
Local time
Today, 07:19
Joined
Sep 5, 2008
Messages
267
Please find it attached
Cheers
 

Attachments

  • HolMan.zip
    192 KB · Views: 167

spikepl

Eledittingent Beliped
Local time
Today, 15:19
Joined
Nov 3, 2010
Messages
6,144
i can't run it without the back end
 

spikepl

Eledittingent Beliped
Local time
Today, 15:19
Joined
Nov 3, 2010
Messages
6,144
Hmmm ... This is difficult for me,since I cannot run your db. I wonder if what you are doing is EXCATLY the same between the first and second time? Ie.e you dont' hit the UNDO somewhere?
 

ppataki

Registered User.
Local time
Today, 07:19
Joined
Sep 5, 2008
Messages
267
apologies, please find local version attached
 

Attachments

  • HolMan - Copy.zip
    103.2 KB · Views: 110

ChrisO

Registered User.
Local time
Tomorrow, 01:19
Joined
Apr 30, 2003
Messages
3,202
Without testing it try this: -

Me.Undo
Cancel = True
Exit Sub

I’m not saying I like the structure of the code but see if that fixes the error.

Chris.
 

spikepl

Eledittingent Beliped
Local time
Today, 15:19
Joined
Nov 3, 2010
Messages
6,144
hehe . just got to that point . I guess ChrisO is right !
 

ppataki

Registered User.
Local time
Today, 07:19
Joined
Sep 5, 2008
Messages
267
I have tried that , no success
BUT
if I remove me.undo completely and just replace it with cancel= 1 then that works!!

Thank you for the hint!!
 

Users who are viewing this thread

Top Bottom