Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-17-2011, 01:29 AM   #1
ppataki
Newly Registered User
 
Join Date: Sep 2008
Posts: 267
Thanks: 0
Thanked 0 Times in 0 Posts
ppataki is on a distinguished road
Me.RecordsetClone causes Run-time error 3420

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

ppataki is offline   Reply With Quote
Old 05-17-2011, 01:57 AM   #2
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Me.RecordsetClone causes Run-time error 3420

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
spikepl is offline   Reply With Quote
Old 05-17-2011, 02:00 AM   #3
ppataki
Newly Registered User
 
Join Date: Sep 2008
Posts: 267
Thanks: 0
Thanked 0 Times in 0 Posts
ppataki is on a distinguished road
Re: Me.RecordsetClone causes Run-time error 3420

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

ppataki is offline   Reply With Quote
Old 05-17-2011, 02:02 AM   #4
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Me.RecordsetClone causes Run-time error 3420

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.
spikepl is offline   Reply With Quote
Old 05-17-2011, 02:09 AM   #5
ppataki
Newly Registered User
 
Join Date: Sep 2008
Posts: 267
Thanks: 0
Thanked 0 Times in 0 Posts
ppataki is on a distinguished road
Re: Me.RecordsetClone causes Run-time error 3420

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
ppataki is offline   Reply With Quote
Old 05-17-2011, 02:12 AM   #6
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Me.RecordsetClone causes Run-time error 3420

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 is offline   Reply With Quote
Old 05-17-2011, 02:15 AM   #7
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Me.RecordsetClone causes Run-time error 3420

If the error persists - show which line the debugger marks

spikepl is offline   Reply With Quote
Old 05-17-2011, 02:18 AM   #8
ppataki
Newly Registered User
 
Join Date: Sep 2008
Posts: 267
Thanks: 0
Thanked 0 Times in 0 Posts
ppataki is on a distinguished road
Re: Me.RecordsetClone causes Run-time error 3420

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 is offline   Reply With Quote
Old 05-17-2011, 02:19 AM   #9
ppataki
Newly Registered User
 
Join Date: Sep 2008
Posts: 267
Thanks: 0
Thanked 0 Times in 0 Posts
ppataki is on a distinguished road
Re: Me.RecordsetClone causes Run-time error 3420

it marks the line
Rst.FindFirst "[HolidayDate] =" & Format(Me.HolidayDate, "\#mm\/dd\/yyyy\#")
ppataki is offline   Reply With Quote
Old 05-17-2011, 02:29 AM   #10
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Me.RecordsetClone causes Run-time error 3420

put

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

just before the failing line, and look in the immediate window for what comes out when it fails
spikepl is offline   Reply With Quote
Old 05-17-2011, 03:32 AM   #11
ppataki
Newly Registered User
 
Join Date: Sep 2008
Posts: 267
Thanks: 0
Thanked 0 Times in 0 Posts
ppataki is on a distinguished road
Re: Me.RecordsetClone causes Run-time error 3420

result:
***2011.05.20.
***2011.05.13.
ppataki is offline   Reply With Quote
Old 05-17-2011, 03:40 AM   #12
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Me.RecordsetClone causes Run-time error 3420

Quote:
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?
spikepl is offline   Reply With Quote
Old 05-17-2011, 03:51 AM   #13
ppataki
Newly Registered User
 
Join Date: Sep 2008
Posts: 267
Thanks: 0
Thanked 0 Times in 0 Posts
ppataki is on a distinguished road
Re: Me.RecordsetClone causes Run-time error 3420

Please find it attached
Cheers
Attached Files
File Type: zip HolMan.zip (192.0 KB, 97 views)
ppataki is offline   Reply With Quote
Old 05-17-2011, 03:59 AM   #14
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Me.RecordsetClone causes Run-time error 3420

i can't run it without the back end
spikepl is offline   Reply With Quote
Old 05-17-2011, 04:06 AM   #15
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Me.RecordsetClone causes Run-time error 3420

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?

spikepl is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question Error Message RecordsetClone Joe8915 General 2 08-26-2009 04:58 AM
Object is invalid or no longer set. (Error 3420) izen Tables 0 05-12-2009 12:45 PM
RecordsetClone SYNTAX Error ted.martin Modules & VBA 2 04-15-2007 02:09 AM
RecordsetClone Property Error John Baker Forms 9 04-25-2006 05:21 AM
error 3251 from recordsetclone st3ve Forms 2 01-15-2004 05:23 AM




All times are GMT -8. The time now is 06:18 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World