do until eof

  • Thread starter Thread starter Jerry Stoner
  • Start date Start date
J

Jerry Stoner

Guest
Dim rs As Recordset
Set rs = Me.RecordsetClone
rs.MoveFirst
Do Until rs.EOF

rs("MatchGuess") = ""
rs.Update
rs.MoveNext
Loop Until rs.EOF = True
rs.Close
Me.Form.Requery
rs.MoveNext

Loop
Set rs = Nothing

End Sub
Im trying to loop through a recordset and set values to null, my code fails at
loop until rs.eof -= Compile error loop without do.
Im sure this is simple but am lost. Any help?


[This message has been edited by Jerry Stoner (edited 03-15-2002).]
 
The essential: Your Loop statement was not correct (if you use Until to loop, the condition appears only once at the beginning). You forgot to .edit the records befor modifying them. You had a .MoveNext after having closed the recordset.

Code: 
Dim rs As Recordset
Set rs = Me.RecordsetClone


With rs
    .MoveFirst

    Do Until .EOF
        .Edit
        .Fields("MatchGuess") = ""
        .Update
        .MoveNext
    Loop

End With


BailOut:
rs.Close
Set rs = Nothing


Me.Form.Requery

Alex

[This message has been edited by Alexandre (edited 03-15-2002).]
 
Try this:

Dim rs As Recordset
Set rs = Me.RecordsetClone
rs.MoveFirst
Do Until rs.EOF
rs("MatchGuess") = ""
rs.Update
rs.MoveNext
Loop

rs.Close
Me.Form.Requery
rs.MoveNext
Set rs = Nothing

Beat out by Alex once again!


[This message has been edited by Jack Cowley (edited 03-15-2002).]
 
Bump
wink.gif


Alex
 
Alex -

And I missed the .Edit as well! Good thing you are there to keep tabs on me.... And it must be very early in the morning for you...

Jack
 
Indeed it is, you don t even imagine to what point, jack. Couldn t sleep this night, and much to do this week-end before I take some holiday
wink.gif


Alex
 
Alex -

It is now 3:30am for you. I am sorry to hear that you can't sleep. We will miss you while you are on holiday but you have surely earned it! I do hope you have fun!

Jack
 
Thanks Guys. Didnt have a chance to get back to it till now . Im getting method or data member not found (compile error) at .edit
Dim rs As Recordset
Setrs = Me.RecordsetClone
With rs
.MoveFirst
Do Until .EOF
.Edit
.Fields("MatchGuess") = ""
.Update
.MoveNext
Loop
End With
BailOut:
rs.Close
Set rs = Nothing
Me.Form.Requery
End Sub
 
Action queries are MUCH more efficient for this type of update. And besides, you won't have to figure out what is wrong with your code so they also save your time.
 
As usual you are right Pat. An append query did the trick. However just for my knowledge would still like to know my (or should I say Alexandre's code - Grin!)is not working.
Thank You.

[This message has been edited by Jerry Stoner (edited 03-16-2002).]
 
The sample was supposed to be DAO code. Check if you have a reference to the DAO library (Tools - References) and if you have one, make sure either to place it above the ADO library OR (Better) to qualify your objects. Ex: Dim rs as DAO.Recordset.

I posted a corrected code because I thought taht you would like to understand what was wrong with yours. However, take Pat's advice. Very rare are the cases where you may have to do with recordset something that you could not manage with queries. And SQL is far more performant for data manipulaton than code is.

Alex
 
Thanks Alexandre I do want to know. Moved DAO library up and no longer get a complile error but I now get Object Variable or with block Variable not set (run time error 91). Will go with the append query but Still want to know what I am doing wrong. BTW is this what you do on holiday? Just kidding.

Dim rs As DAO.Recordset
Setrs = Me.RecordsetClone
With rs
.MoveFirst
Do Until .EOF
.Edit
.Fields("txtMatchGuess") = ""
.Update
.MoveNext
Loop
End With
BailOut:
rs.Close
Set rs = Nothing
Me.Form.Requery
End Sub

[This message has been edited by Jerry Stoner (edited 03-16-2002).]
 
Jerry,

Just in case you would think I am pulling your leg with the above, I almost never post code without testing it. When I do, I say something like: not tested, but.. or just an idea, but:... I ran that one so you can be 100% sure that the problem comes from the specific circumstances where it is run on your machine. This is why I thought about the references.

Now, my new guess, is that your recordsetclone may be empty. I had hesitated to add that right in my first post because I don like to look like I am of the 'I do it better' type. But you should never work on a recordset without testing whether or not it is empty first.

Code: 
Set rs = Me.RecordsetClone

With rs
    If .NoMatch Then GoTo Ooops
    .MoveFirst

    Do Until .EOF
        .Edit
        .Fields("Last Name") = "A"
        .Update
        .MoveNext
    Loop

End With


Ooops:
rs.Close
Set rs = Nothing

This is all I can think about at the moment, but if still no chance, re-post.

Alex

[This message has been edited by Alexandre (edited 03-16-2002).]
 
I've been on this board long enough to know you are one of the most knowledgable persons here Alexandre. I also know you would never post an answer in jest. When I said "(or should I say Alexandre's code - Grin!)" I was merely stating that your code was so different than my feeble attempt I could not call it mine anymore. No offence intended or taken. That said it still doesnt work.It now fails with Invalid or unqualified reference at
If .NoMatch Then GoTo Ooops
BTW my references are:
MicrosoftAccess 9.0 Object Library
Microsoft DAO 3.6
In that order but since I Dimmed rs as DAO Recordset that shouldn't matter - Right?
 
There doesn't seem to be a space between Set and rs in the line below:

Setrs = Me.RecordsetClone
 
You're right Pat however that didnt change the error. Seems to me it must be a reference problem as Alexandre first guessed. Anyway I used your previous suggestion and it works fine. The only reason Im persuing this is to learn learn learn.
Thanks.
 
Jerry,

I had taken no offense of what you said. Just wanted to let you know that you perhaps had to look not that much at the code (because I do not think you will find anymore mistake in it), but at the environment in which it is run: your form's recordsoucre, the form event you use to run it...

Send me a zip of your db, and I ll have a look at it.

Alex
 
Jerry,

Your code is at left, mine is at right:

Code: 
Dim rs As DAO.Recordset                     Dim rs As DAO.Recordset
Setrs = Me.RecordsetClone                   Set rs = Me.RecordsetClone
If .NoMatch Then GoTo Ooops                 With rs
With rs                                         If .NoMatch Then GoTo Ooops
 .MoveFirst                                     .MoveFirst
 Do Until .EOF                                  Do Until .EOF
  .Edit                                             .Edit
  .Fields("txtMatchGuess") = ""                     .Fields("txtMatchGuess") = ""
  .Update                                           .Update
  .MoveNext                                         .MoveNext
  Loop                                          Loop
  End With                                  End With
Ooops:  rs.CloseSet rs = Nothing            Ooops:
BailOut:                                    rs.Close
 rs.Close                                   Set rs = Nothing
 Set rs = Nothing                           Me.Form.Requery
  Me.Form.Requery
  End Sub

- Second line: The error Pat pointed
- Third line: With rs is meant to let your work on an object (rs here) without having to refer explicitly to it. For example .NoMatch is meant for rs.NoMatch. You cannot use implicit references like that BEFORE you state what you are going to work WITH.
- 13th and 14th lines. Ooops: and BailOut: are just labels, used to indicate where a certain piece of code starts. So you don t need both, just one of them. If .NoMatch Goto Ooops means: if the recordset is empty, skip the code (you can t work on a recorset if it is empty) and jump directly to Ooops (where we clear up everything before leaving).
- You cannot have various instructions on the same line:
So instead of Ooops: rs.CloseSet rs = Nothing
Ooops:
rs.Close
Set rs = Nothing

Alex

[This message has been edited by Alexandre (edited 03-17-2002).]
 
Ouch now thats embarrasing.I obviously didnt pay attention to what I was doing when I pasted your code in.
Thank you Alexandre,
Jerry
 
You must log in or register to reply here.

Similar threads

T
Problem with NAME Statement
Replies
2
Views
183
Mark_
M
D
Calendar displaying autonumber from a combo box..
Replies
5
Views
328
Pat Hartman
P
klsblues
VBA Conditional Formatting error because I have more than 3 formatting requirements.
2 3
Replies
53
Views
1,525
AHeyne
A
T
Invalid Qualifier
Replies
5
Views
263
TheSearcher
T
MajP
Randomize Not Working
2
Replies
25
Views
824
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom