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

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

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

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

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

It runs!!!!

Thank you Kevin.

Loralee

Loralee,

You are more than welcome. Anytime.

Kevin

dartcoach