[AccessD] MoveNext not working

Chris
You need to use two recordsets. Each time you reopen the "input" recordset
the pointer is repositioned so MoveNext isn't going to the right record. Try
the following:

Option Compare Database
Option Explicit

Private Sub Command0_Click()

Dim db As Database
Dim recIn As Recordset
Dim RecOut as Recordset
Dim strECR As String

Set db = CurrentDb()

'-------------------------------
'Write first line to output table
'--------------------------------
Set recOut = db.OpenRecordset("tblScript")

recOut.AddNew

recOut("fldScript") = "set db pdm"

recOut.Update

'-------------------------------
'Open tblOutstandingECRs and move to first record
'-------------------------------

Set recIn = db.OpenRecordset("tblOutstandingECRs")

recIn.MoveFirst

' Do until end of file
Do Until recIn.EOF

strECR = recIn("ECR_No")

recOut.AddNew

recOut("fldScript") = "set record ecr\" & strECR & "\1 /var=%oldset1"

recOut.Update

recIn.MoveNext

Loop

'-------------------------------
'Write last line to output table
'--------------------------------
recOut.AddNew

recOut("fldScript") = "list record %oldset1 recname,reclevel recn"

recOut.Update

recIn.Close:set recIn=Nothing
recOut.Close:set recOut=Nothing
End Sub


--
Andy Lacey
http://www.minstersystems.co.uk


--------- Original Message --------
From: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
Subject: [AccessD] MoveNext not working
Date: 24/10/03 09:22


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 ecr401-0157631 /var=%oldset1
set record ecr401-0197371 /var=%oldset1
set record ecr401-0233591 /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 ecr401-0157631 /var=%oldset1"), and
third record ("set record ecr401-0197371 /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

________________________________________________
Message sent using UebiMiau 2.7.2