Me.RecordsetClone causes Run-time error 3420

[ppataki]

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

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]

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]

If I delete Exit Sub then I get another error message as my code continues opening another recordset
any suggestions pls?
cheers

 

[spikepl]

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]

I have added rst.close to the code, still the same error

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]

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]

If the error persists - show which line the debugger marks

 

[ppataki]

OK, I have put rst.close after nomatch as well, but unfortunately it still fails
Please find the whole code below:

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]

it marks the line
Rst.FindFirst "[HolidayDate] =" & Format(Me.HolidayDate, "\#mm\/dd\/yyyy\#")

 

[spikepl]

put

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

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

 

[ppataki]

result:
***2011.05.20.
***2011.05.13.

 

[spikepl]

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]

Please find it attached
Cheers

 

[spikepl]

i can't run it without the back end

 

[spikepl]

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]

apologies, please find local version attached

 

[ChrisO]

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]

hehe . just got to that point . I guess ChrisO is right !

 

[ppataki]

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!!