UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Run Time Error 3021 - Access 2016    
 
   
horrendous44
post Feb 13 2019, 04:15 PM
Post#1



Posts: 2
Joined: 13-February 19



Im basically new using Access and I was assigned the task of fixing a database that is showing the infamous error 3021. I have looked online through various forums but haven't been able to find an answer that could help me fix this issue. I must make the note that I am new to the company and the database stop working 2 weeks ago.

Here is the piece of code that is been highlighted with the error:
Dim rsdata As DAO.Recordset
Set rsdata = Application.CurrentDb.OpenRecordset("qryChecktblNotes")
Dim strnote As String
If IsNull(rsdata.Fields("note").Value) Then
strnote = ""
Else
strnote = rsdata.Fields("note").Value
End If

rsdata.Close

I appreciate any possible recommendations as it to be able to fix this issue.
Go to the top of the page
 
GroverParkGeorge
post Feb 13 2019, 04:39 PM
Post#2


UA Admin
Posts: 35,502
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

Unfortunately, I haven't managed to memorize all of the possible error codes yet, so I had to look this one up again. This one means "No Current Record Found" so that tells us your recordset opens with NO records in it.

That's possible if the query, "qryChecktblNotes" has a filter that manages to eliminate all records, so it opens with no records.

Then, when you try to check the value of one field in that empty recordset, Access responds, "Hey, there IS no record to check!"

So you can handle this by checking the recordset itself prior to checking that one field in it. Or you can make sure this code only runs when you know the query will return one or more records.

Maybe change it like this. (with a couple of additional tweaks to suit my style)

CODE
    Dim rsdata As DAO.Recordset
    Dim strnote As String

    Set rsdata = Application.CurrentDb.OpenRecordset("qryChecktblNotes")
    rsdata.MoveLast
    rsdata.Movefirst
    If rstdata.Recordcount > 0 Then
        If IsNull(rsdata.Fields("note").Value) Then
            strnote = ""
        Else
            strnote = rsdata.Fields("note").Value
        End If
    Else
        MsgBox "Nothing to Check"
    End If
    rsdata.Close

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
horrendous44
post Feb 13 2019, 04:53 PM
Post#3



Posts: 2
Joined: 13-February 19



Thank you for answering George!
Now I get the same error, but the difference is that now is highlighting rsdata.Movelast
Go to the top of the page
 
GroverParkGeorge
post Feb 13 2019, 05:17 PM
Post#4


UA Admin
Posts: 35,502
Joined: 20-June 02
From: Newcastle, WA


My bad. Sorry.

Yes, let's do it this way instead. You need to check if the recordset is already at the end before doing ANYTHING else. So check for EOF or End of File.

CODE
  
    Dim rsdata As DAO.Recordset
    Dim strnote As String

    Set rsdata = Application.CurrentDb.OpenRecordset("qryChecktblNotes")
    If Not rstdata.EOF Then
        If IsNull(rsdata.Fields("note").Value) Then
            strnote = ""
        Else
            strnote = rsdata.Fields("note").Value
        End If
    Else
        MsgBox "Nothing to Check"
    End If
    rsdata.Close

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th August 2019 - 07:46 AM