[AccessD] MoveNext not working

Your problem is that you are using one recordset to access 2 tables.  Within
your do loop you are using this
Set rec = db.OpenRecordset("tblScript")
But the loop is controlled by rec and you are reinitialising it on each pass.
So it will never end.

If you want to go the code method, use 2 recordsets.  Say rectblScript and
rectblOutstandingECRs.

Then iterate through rectblOutstandingECRs and for each record add a new record
to rectblScript.

If you need help modifying the code drop me a note.

Another potentially simpler method to to write a query that does all of one you
want in one hit.  (ie append query)

Regards,

Stuart

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Foote, Chris
> Sent: Friday, 24 October, 2003 5:21 PM
> To: 'Access Developers discussion and problem solving'
> Subject: [AccessD] MoveNext not working
>
>
> Good morning all!
>
> Can anyone please see what I've doing wrong in the code below?
>
> I have two tables, tblOutstandingECRs, and tblScript.
>
> tblOutstandingECRs has a single text field which contains a
> list of document
> numbers (like 401-015763 and 401-019737). I need to step through these
> records, one at a time, and copy them, with changes, to
> tblScript. The first
> record of tblScript needs to have a fixed bit of text ("set
> db pdm") and the
> last is also fixed ("list record %oldset1 recname,reclevel recn").
>
>
> If tblOutstandingECRs contained the three records:
>
> 401-015763
> 401-019737
> 401-023359
>
> I need tblScript to have:
>
> set db pdm
> set record ecr\401-015763\1 /var=%oldset1
> set record ecr\401-019737\1 /var=%oldset1
> set record ecr\401-023359\1 /var=%oldset1
> list record %oldset1 recname,reclevel recn
>
>
> The problem that I'm getting is that it writes the first
> record ("set db
> pdm"), second record ("set record ecr\401-015763\1
> /var=%oldset1"), and
> third record ("set record ecr\401-019737\1 /var=%oldset1")
> fine, then goes
> into and endless loop of writing the third record again.
>
> Any ideas please what I'm doing wrong here?
>
> TIA!
> Chris Foote
>
>
> ----------(Code)------------
> Option Compare Database
> Option Explicit
>
> Private Sub Command0_Click()
>
> Dim db As Database
> Dim rec As Recordset
> Dim strECR As String
>
> Set db = CurrentDb()
>
> '-------------------------------
> 'Write first line to output table
> '--------------------------------
> Set rec = db.OpenRecordset("tblScript")
>
> rec.AddNew
>
> rec("fldScript") = "set db pdm"
>
> rec.Update
>
> rec.Close
>
> '-------------------------------
> 'Open tblOutstandingECRs and move to first record
> '-------------------------------
>
> Set rec = db.OpenRecordset("tblOutstandingECRs")
>
> rec.MoveFirst
>
> ' Do until end of file
> Do Until rec.EOF
>
> strECR = rec("ECR_No")
>
> Set rec = db.OpenRecordset("tblScript")
>
> rec.AddNew
>
> rec("fldScript") = "set record ecr\" & strECR & "\1 /var=%oldset1"
>
> rec.Update
>
> Set rec = db.OpenRecordset("tblOutstandingECRs")
>
> rec.MoveNext
>
> Loop
>
> '-------------------------------
> 'Write last line to output table
> '--------------------------------
> Set rec = db.OpenRecordset("tblScript")
>
> rec.AddNew
>
> rec("fldScript") = "list record %oldset1 recname,reclevel recn"
>
> rec.Update
>
> rec.Close
>
> End Sub
> -------------(End Code)----------
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>