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
> How To Avoid Error 3021 (no Current Record), Access 2003    
 
   
zocker
post Feb 12 2020, 10:29 AM
Post#1


Utterly Eccentric and Moderator
Posts: 4,100
Joined: 4-March 00
From: Bristol / Ipswich / Spain/Gloucester


Greetings all! Your thoughts required on this:

Here's code to run through a recordset (rs) generated from a SQL statement.

CODE
Do While Not rs.EOF

' Add results to Result string

    Result = Result & "  " & rs("AccountID") & ";  " & rs("AccName") _
            & ";  " & rs("WhatFor") & ";  " & rs("TransactionDate") & ";"

   ' Debug.Print Result & vbCrLf

    rs.MoveNext

Loop


All the result stuff is fine except at the last iteration, it does what is required: goes to the next...but there isn't one so it errors out 3021. How can it be stopped from going to no record? Possible I suppose to do something 'like' [If countIterations = rs.recordcount Then Exit Do] but that seems clunky...
Your thought ladies and gentlemen please.

Thanks in advance-
Zocker

--------------------
I think they're for 1 am. Rene Descartes
Go to the top of the page
 
theDBguy
post Feb 12 2020, 10:40 AM
Post#2


UA Moderator
Posts: 77,729
Joined: 19-June 07
From: SunnySandyEggo


Hi Z. If you're getting an error with that, there may be another issue. The Do While loop should stop as soon as there's no more records. So, for example, if you open a recordset with only one record in it, then the first time through the loop, you get a result, then the MoveNext command should hit the EOF. So, the next pass through the loop, the Do While should exit out.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
zocker
post Feb 12 2020, 10:53 AM
Post#3


Utterly Eccentric and Moderator
Posts: 4,100
Joined: 4-March 00
From: Bristol / Ipswich / Spain/Gloucester


Thanks! It's the same for 1 record. All the data is there and shows correctly...mystified I am!

Z

--------------------
I think they're for 1 am. Rene Descartes
Go to the top of the page
 
theDBguy
post Feb 12 2020, 10:56 AM
Post#4


UA Moderator
Posts: 77,729
Joined: 19-June 07
From: SunnySandyEggo


I say the error must be happening elsewhere. Do you get the same error with an empty rs?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
zocker
post Feb 12 2020, 12:39 PM
Post#5


Utterly Eccentric and Moderator
Posts: 4,100
Joined: 4-March 00
From: Bristol / Ipswich / Spain/Gloucester


There is handling for 'no records'. As the code builds a string for the RowSource of a list box, including making ColumnHeadimgs, we don't want to do any of that if No Data. Later, I'll provide a cutdown db.
Thanks Leo!

--------------------
I think they're for 1 am. Rene Descartes
Go to the top of the page
 
zocker
post Feb 12 2020, 07:14 PM
Post#6


Utterly Eccentric and Moderator
Posts: 4,100
Joined: 4-March 00
From: Bristol / Ipswich / Spain/Gloucester


This database tracks household bank accounts. It opens on the Form 'Acounts'. This allows quick access to any bank account, its details (like Ac Number, date opened etc.) a subform shows transactions (Amount in, amount out, Date, For What etc.)
If a user has an outgoing amount in question and wishes to know to which account it belongs:

On the main form Click the 'OUT' Button.
Enter the amount in the Input Box or just use the default value 10.50
Click OK on the Input Box
If there are no records, the user will be told by text written in the wide Listbox on the form.
Because there are likely to be more than one instance of an Amount outgoing, the user can choose from the Listbox which instance of the amount it is wished to examine.
When there are records, code constructs headings for the Listbox, constructs a row in the Listbox for each record then exits. Error 3012 appears at this point. This is the present problem. a default value is in place on the Input box. Code is not written beyond populating the Listbox.

All data is sample there are no real records.

Many thanks for looking!
Attached File(s)
Attached File  HouseHold_Demo.zip ( 79.34K )Number of downloads: 5
 

--------------------
I think they're for 1 am. Rene Descartes
Go to the top of the page
 
pere_de_chipstic...
post Feb 14 2020, 06:34 PM
Post#7


UtterAccess Editor
Posts: 10,595
Joined: 8-November 07
From: South coast, England


Hi Zocker

Your code appears to fail on the line NavigateTo = rs("AccountID")

This is when the code has already reached the end of the recordset.

You need to jump out of the code before it reaches NavigateTo = rs("AccountID")

hth

--------------------
Warm regards
Bernie
Go to the top of the page
 
zocker
post Feb 15 2020, 01:48 PM
Post#8


Utterly Eccentric and Moderator
Posts: 4,100
Joined: 4-March 00
From: Bristol / Ipswich / Spain/Gloucester


Thanks Bernie!

James

--------------------
I think they're for 1 am. Rene Descartes
Go to the top of the page
 
tina t
post Feb 15 2020, 03:31 PM
Post#9



Posts: 6,459
Joined: 11-November 10
From: SoCal, USA


hi James, you could try changing the code as follows:

CODE
If rs.RecordCount > 0 Then

    rs.MoveLast
    rs.MoveFirst
    Do

' Add results to Result string

        Result = Result & "  " & rs("AccountID") & ";  " & rs("AccName") _
                & ";  " & rs("WhatFor") & ";  " & rs("TransactionDate") & ";"

   ' Debug.Print Result & vbCrLf

        rs.MoveNext

    Loop Until rs.EOF

End If

you may not even need the MoveLast and MoveFirst actions.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
pere_de_chipstic...
post Feb 15 2020, 04:17 PM
Post#10


UtterAccess Editor
Posts: 10,595
Joined: 8-November 07
From: South coast, England


yw.gif
Hope you've got it sorted!

--------------------
Warm regards
Bernie
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    7th April 2020 - 12:13 PM