Compile Difficulties

[Malcy]

Compile difficulties (do while loop)

I have put this code in one of my modules. Function is to delete any records where a field value is 0.
Every time I try to debug or compile it I get the message "loop without do". I cannot make sense of it since it seems OK to me

-------------
With rst
.MoveFirst
Do While Not rst.EOF
If .Fields("dblSupQty") = 0 Then
.Delete
.MoveNext
Loop
End With
-------------

I have set up cnn and rst properly in ADO earlier in the module.
Any ideas most welcomed!
Thanks

Malcy

 

[DavidWRouse]

You have forgot to put an "End If"


With rst
.MoveFirst
Do While Not rst.EOF
If .Fields("dblSupQty") = 0 Then
.Delete
.MoveNext
End If <<<<<<<HERE
Loop
End With

 

[Malcy]

Thanks David
Every time I think I am getting somewhere I end up overlooking something simple. Obvious when pointed out!
Perhaps I should not be working on a Sunday!!
Best wishes

Malcy

 

[Mile-O]

Also,

you can change this line: Do While Not rst.EOF

to: Do While Not .EOF

 

[Malcy]

Also,

you can change this line: Do While Not rst.EOF

to: Do While Not .EOF

Thanks Mile
Does it make a difference, other than saving some keystrokes when putting
the code in?
Best wishes
Malcy

 

[billyr]

Dbl Oops

Check every record. Right?

With rst
.MoveLast
.MoveFirst
Do While Not .EOF
If .Fields("dblSupQty") = 0 Then
.Delete
End If
.MoveNext
Loop
End With

 

[Malcy]

Thanks billyr
I did spot the end if not being in the right position but had not got the move last then move first routine.
That should make it bombproof.
I think that you guys must wear special specs. Where can I get a pair?
Best wishes

Malcy

 

[Mile-O]

Thanks Mile
Does it make a difference, other than saving some keystrokes when putting
the code in?

Nothing much but since you'd opened a With...End With structure around the rst object, there seemed little point in referring to it as you were already explicitly referring to it.

 

[billyr]

You do have them.

All humans have "special glasses" that make the mistakes of others much easier to spot than our own - in every aspect of life.

 

[chris_dono]

-------------
With rst
.MoveFirst
Do While Not rst.EOF
If .Fields("dblSupQty") = 0 Then
.Delete
.MoveNext
Loop
End With
-------------

You could try this as well:
With rst
.MoveLast
.Movefirst
While Not .EOF
If .Fields("dblSupQty") = 0 Then
.Delete
.MoveNext
End If
Wend
End With

 

[Malcy]

Thanks for the suggestion Chris
Have now got it working fine
Malcy