Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 12th, 2005, 10:14 PM
Friend of Wrox
 
Join Date: Mar 2004
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





 
Old October 12th, 2005, 10:25 PM
Friend of Wrox
 
Join Date: Jan 2005
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
 
Old October 12th, 2005, 10:39 PM
Friend of Wrox
 
Join Date: Mar 2004
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

 
Old October 12th, 2005, 10:48 PM
Friend of Wrox
 
Join Date: Jan 2005
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
 
Old October 12th, 2005, 11:22 PM
Friend of Wrox
 
Join Date: Mar 2004
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

 
Old October 13th, 2005, 12:48 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

It runs!!!!

Thank you Kevin.

Loralee

 
Old October 13th, 2005, 12:50 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Loralee,

You are more than welcome. Anytime.

Kevin

dartcoach





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





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.