Infinite Do While Lloop

[stoyleg]

I have a script which, when a button is clicked, moves the user to the next record but goes back through the record just input, loops through the names and adds them to the new record. So basically all the names entered on the previous record (via a continuous form) are retained for the new record.

The problem is, this works (almost) perfectly using the following code...

If rs.RecordCount <> 0 Then
          rs.MoveLast
          Do While Not rs.BOF
          rs.AddNew
          rs("Name") = strName
          rs.Update
          rs.MovePrevious
          Loop
       End If

...but it orders the names in reverse because obviously it's moving to the last name then cycling back through them using 'MovePrevious' (I can't find a method of reordering the names). The obvious solution is to change the code to this...

If rs.RecordCount <> 0 Then
          rs.MoveFirst
          Do While Not rs.EOF
          rs.AddNew
          rs("Name") = strName
          rs.Update
          rs.MoveNext
          Loop
       End If

...but for some reason this results in an infinite loop.

Does anyone know why this is happening and maybe think of another solution whereby the names entered in a continuous form can be retained so they are available when the user moves to the next record?

I hope I explained that ok...thanks for any help.

 

[jdraw]

http://allenbrowne.com/ser-29.html

see looping here
http://www.functionx.com/vbaccess/Lesson09.htm

 

[stoyleg]

Thanks for the links.

I've used numerous loops from these sites but the only one that works is the one I'm using. It should be a relatively simple thing to do but for some reason I get this infinite loop when I use the standard method.

Do you have any ideas why?

 

[thechazm]

Just a thought but couldn't you change your SQL to sort the fields already for you when you make the recordset instead of having to do it all by code?

Just use your original if statement and do your sorting in SQL.

If rs.RecordCount <> 0 Then
rs.MoveFirst
Do While Not rs.EOF
rs.AddNew
rs("Name") = strName
rs.Update
rs.MoveNext
Loop
End If

 

[stoyleg]

Thanks thechazm - that worked great! I just added "ORDER BY ID DESC" to the end of the SQL.

Can't believe I spent so long trying to find a solution and was just looking in the wrong place!

Thanks again!

 

[thechazm]

It's ok we all do it Glad that works for you.

 

[gemma-the-husky]

why use moveprevious. this should do the job

while not rs.eof
    rs.AddNew
    rs("Name") = strName
    rs.Update
    rs.Movenext
wend

 

[tehNellie]

I have a script which, when a button is clicked, moves the user to the next record but goes back through the record just input, loops through the names and adds them to the new record. So basically all the names entered on the previous record (via a continuous form) are retained for the new record.

The problem is, this works (almost) perfectly using the following code...

If rs.RecordCount <> 0 Then
          rs.MoveLast
          Do While Not rs.BOF
          rs.AddNew
          rs("Name") = strName
          rs.Update
          rs.MovePrevious
          Loop
       End If

...but it orders the names in reverse because obviously it's moving to the last name then cycling back through them using 'MovePrevious' (I can't find a method of reordering the names). The obvious solution is to change the code to this...

If rs.RecordCount <> 0 Then
          rs.MoveFirst
          Do While Not rs.EOF
          rs.AddNew
          rs("Name") = strName
          rs.Update
          rs.MoveNext
          Loop
       End If

...but for some reason this results in an infinite loop.

Does anyone know why this is happening and maybe think of another solution whereby the names entered in a continuous form can be retained so they are available when the user moves to the next record?

I hope I explained that ok...thanks for any help.

In terms of why you were getting an infinite loop, You were at the first record in your recordset. (1 of 1 in the table for arguments sake)

You then added a new record so you're now at record 1 of 2.

You then moved to the next record. now at 2 of 2

you then added a new record. so you're now at record 2 of 3

Everytime you stepped forward one record, you added a new one so you could never reach the end of your recordset.

On your first example you started at the end of the recordset and worked backwards so the fact you were adding new records after your original starting position made no difference.

 

[stoyleg]

Thanks for the replies - this is sorted now