Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 12th, 2005, 10:14 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default "no current record" error in DAO code

I am migrating alot of records from an old flatfile table into new (unpopulated) tables, and I'm new to DAO.

1. I am consistently getting the error "no current record" when my procedure loops through the procedure for FIRST time. If I re-run the procedure, it usually runs and leaves me with 2 copies of the first record in the newly populated table. Do I need to do something a little different for the first loop through an empty table?

2. I got the SAME error ("no current record") in the MIDDLE of running this procedure and I can't see anything unusual about the record it is complaining about. By then, about 4K records had updated without a problem but that left 17K+ more to run.

My code appears to be tripping at the .movenext method(?).

Question 1: In a previously unpopulated table, where DOES the cursor go after you issue the .addnew and then .update? How do I fix this? Any suggestions for a better way?

Question 2: Any ideas as to what I should look for in the offending record in the middle of the procedure (Problem variation 2)?


Thank you!!

Loralee
************************************************** *
My code is

dim db as dao.database
dim rstPatient as dao.recordset ' contains data, (source)
dim rstPxNotes as dao.recordset ' empty, (target)
dim varStatusT as variant
dim varRefByT as variant
dim varProcDate as variant
dim varHospT as variant
dim lngPatientFK as long
dim strPxNotes as string

set db = currentDb
set rstpatient = db.openrecordset("tblPatient", dbopentable)
set rstPxNotes = db.openrecordset("tblpxnotes", dbopentable)

rstPatient.movefirst
do While not rstPatient.EOF
     lngPatientFK = rstPatient!patientid
     varStatusT = rstPatient!statusid
     varRefByT = rstPatient!statusT
     varProcDate = rstPatient!procdateT
     varHospT = rstPatient!hospT

' now test variables and create px notes string
   if not isnull(varStatusT) then
       strPxNotes = "Pt status in old db is " & varStatusT & ". "
   end if

   'rest of similiar code here to build up string and it works

    If strPxNotes <>"" then
        With rstPxNotes
           .addnew
           !patientid = lngPatientFK
           !PxNotes = strPxNotes
           .update
        End With
    End if
    rstPxNotes.movenext
Loop

' clean up
rstPatient.close
rstPxNotes.close
set rstPatient = nothing
set rstPxNotes = nothing

end sub





Reply With Quote
  #2 (permalink)  
Old October 12th, 2005, 10:25 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Loralee,

I don't believe that you need the:
  rstPxNotes.movenext
statement. You are moving to the next record whether you are adding a record or not. Also, you don't have to move to the next, because you are always adding a record.

Kevin

dartcoach
Reply With Quote
  #3 (permalink)  
Old October 12th, 2005, 10:39 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

Thanks, Kevin. I assume that would go for all loops then and not just the first one?

Any idea why it would throw the same error 4000 records into the run? I don't see any "funky data"?

Thanks,

Loralee

Reply With Quote
  #4 (permalink)  
Old October 12th, 2005, 10:48 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Loralee,

If the first record in rstPatient didn't write a record, you have not inserted a record and are trying to go to the next, which you can't do. In the middle, same thing, if the last record, did not add a new record, then you again are trying to move to the next and you can't.

Make sense?

Kevin

dartcoach
Reply With Quote
  #5 (permalink)  
Old October 12th, 2005, 11:22 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

The first record does. I'll try it tomorrow and see if it will run though all of records without erroring in the middle.

Thanks!
Loralee

Reply With Quote
  #6 (permalink)  
Old October 13th, 2005, 12:48 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

It runs!!!!

Thank you Kevin.

Loralee

Reply With Quote
  #7 (permalink)  
Old October 13th, 2005, 12:50 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Loralee,

You are more than welcome. Anytime.

Kevin

dartcoach
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Current Record Number Base VB How-To 1 December 7th, 2005 05:26 PM
runtime error 3021-no current record togs74 Access 1 July 30th, 2005 02:10 AM
dao error code 0xbe5 m002864 Crystal Reports 0 July 11th, 2005 10:02 AM
UPDATE CURRENT RECORD ranakdinesh Classic ASP Databases 6 March 18th, 2005 02:22 AM
Error 3021 - No Current Record JackNimble BOOK: Expert One-on-One Access Application Development 5 October 2nd, 2004 03:26 PM



All times are GMT -4. The time now is 11:30 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.