 |
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
|
|
|

October 12th, 2005, 10:14 PM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
|
|
"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
|

October 12th, 2005, 10:25 PM
|
Friend of Wrox
|
|
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
|
|
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
|

October 12th, 2005, 10:39 PM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
|
|
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
|

October 12th, 2005, 10:48 PM
|
Friend of Wrox
|
|
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
|
|
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
|

October 12th, 2005, 11:22 PM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
|
|
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
|

October 13th, 2005, 12:48 PM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
|
|
It runs!!!!
Thank you Kevin.
Loralee
|

October 13th, 2005, 12:50 PM
|
Friend of Wrox
|
|
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
|
|
Loralee,
You are more than welcome. Anytime.
Kevin
dartcoach
|
|
 |