Do...Loop Won't !!!

  • Thread starter Thread starter greg
  • Start date Start date
G

greg

I have a main form, frmHead, with a subform, sbfrmTail
based on tables, tblHead and tblTail. What I am trying to
do is change the values of the field, fldLine on the
subform from whatever to 0. The reason I am doing this,
is because, I have a function which will increment the
line numbers of the subform that works great. However, if
a user should delete one of the lines, it would be "nice"
to be able to "reset" the line numbers. In order to do
this, I need first, to reset the line numbers to 0, then
loop through again while calling the line number
function. This is what I have tried:

Private Sub Command15_Click()

With Forms!frmHead!sbfrmTail.Form.RecordsetClone
.MoveFirst
Do Until .EOF
Forms!frmHead!sbfrmTail.Form.fldLine = 0
.MoveNext
Loop
End With
Requery
End Sub

this and a dozen other configurations and examples which I
have looked up in the newsgroups, ms help and on the
internet. The above, will move to the first record and
change the value, but it won't loop through the rest of
the records. When I step through the code, it "appears"
to loop through them, but none of the other values are
changed. help please...

greg
 
Hi Greg

You need to .Edit a recordset before you can change the current record, and
then .Update it to save the changes. Also, you are updating the current
record in the subform, not the current record in the RecordsetClone:

Do Until .EOF
.Edit
!fldLine = 0
.Update
.MoveNext
Loop

Why don't you do the renumbering in the same loop?

For iCounter = 1 to .RecordCount
.Edit
!fldLine = iCounter
.Update
.MoveNext
Next iCounter
 
Well, I know i tried that in one of my configurations.... when I was trying to use set recordset, but I kept getting the error message "Method or
Property not found" Any way, I did what you said and it worked just fine, and for that I give you much thanks. this is what I ended up with.

Private Sub Command15_Click()

Dim iCounter As Integer

With Forms!frmHead!sbfrmTail.Form.RecordsetClone

Do Until .EOF

For iCounter = 1 To .RecordCount
.Edit
!fldLine = 0
!fldLine = iCounter
.Update
.MoveNext
Next iCounter
Loop

End With
End Sub

I don't know if that is the correct way to use icounter, but it worked. One thing I did notice though, is it will only work one time. If i go to the
next record on the main form, it won't work a second time; however, if I close the form, reopen it, then go to the next record, it works ok. Is there
a reason for this? It doesn't really matter at this point, because the sub will eventually be working on a form with a single record.... but for
future reference... .

much thanks again
greg
 
Back
Top