loop wthout do error (1 Viewer)

[bigmac]

can you help me this code please?
on a subform I am trying to look at the [en update ] textbox (contains a date),
and if it is within 28 days of the date then it will change the value of textbox [EN update info] to 0
the code I have been trying is as follows but I keep getting an "loop without DO error ,

firstly is my code right for looking at [en update ] and checking the date contained correct?
and what else is wrong with my code please

Private Sub Form_Current()
Dim rs As dao.Recordset
Set rs = Me!subform.Form.RecordsetClone
With rs
If Not (.BOF And .EOF) Then .MoveFirst
Do While Not .EOF
If ![EN update] >= DateAdd("m", 0, Date) And DateAdd("d", 28, Date) Then
Me.en_update_info.Value = 0
.MoveNext
Loop
Exit Do
.Close
End If
End If
End Sub

 

[Solo712]

can you help me this code please?
on a subform I am trying to look at the [en update ] textbox (contains a date),
and if it is within 28 days of the date then it will change the value of textbox [EN update info] to 0
the code I have been trying is as follows but I keep getting an "loop without DO error ,

firstly is my code right for looking at [en update ] and checking the date contained correct?
and what else is wrong with my code please

Private Sub Form_Current()
Dim rs As dao.Recordset
Set rs = Me!subform.Form.RecordsetClone
With rs
If Not (.BOF And .EOF) Then .MoveFirst
Do While Not .EOF
If ![EN update] >= Date And DateAdd("d", 28, Date) Then
Me.en_update_info.Value = 0
Exit Do
End If
.MoveNext
Loop
.Close
End If
End With
End Sub

Assuming that the Date is correct format ("mm/dd/yyyy") then the above red edits should fix your problem with the do loop. Not sure if that is the end of your problems.

Best,
Jiri

 

[Gasman]

Not even sure of your syntax?

If you indented the code and put it between code tags it would be easier to read.?

Shouldn't the date check be ?

If ![EN update] >= Date And ![EN update]  <= DateAdd("d", 28, Date) Then

 

[The_Doc_Man]

Or were you looking for a "BETWEEN" in this case?

The syntax of your statement involves the imposition of a logical AND between two disparate types OR imposes a logical AND between two non-logical types. What you wrote can be interpreted by people two different ways. See below for highlighting to show what parts COULD be taken together. I added parentheses to make my point clearer.

If [COLOR="Blue"] ( ![EN update] >= DateAdd("m", 0, Date) ) [/COLOR] And [COLOR="DarkRed"] ( DateAdd("d", 28, Date) )[/COLOR] Then

If [COLOR="DarkRed"] ( ![EN update] ) [/COLOR] >=[COLOR="Blue"] ( DateAdd("m", 0, Date) And DateAdd("d", 28, Date) ) [/COLOR] Then

I believe that the "priority of operators" rule means the first of these two lines is what would actually be compiled. Were you looking for this?

If ![EN update] [COLOR="Blue"]BETWEEN[/COLOR] DateAdd("m", 0, Date) [COLOR="blue"]AND[/COLOR] DateAdd("d", 28, Date) Then

Remember that operators such as + - * \ / < > <= >= <> = AND OR XOR are BINARY operators. They do what they do to TWO things at a time. You have three things on the line to the right of the IF so that means ONE of your two operators will be executed first, then the other. You wanted a three-way operation, which would be done with a BETWEEN ... AND or by adding a second explicit comparison as noted by Gasman. Since AND is done last in priority of operation, Gasman's suggested code doesn't even need parentheses

 

[bigmac]

hi The_Doc_Man , yes this is what I am trying to achieve but I get a compile syntax error?

 

[The_Doc_Man]

First, look at Jiri's (SOLO712) suggestion, which invites you to move the LOOP and END IF statements.

Your error is that that an IF/THEN/ENDIF block is treated like a little sub-domain unto itself. You can use a GOTO or other "transfer of control" syntax (e.g. END SUB) to terminate this little sub-domain by leaving it, but you cannot jump into it from the outside. You can only enter the area of that code through the IF at the top. Think of IF and END IF as "multi-line brackets" and that placement violates the bracketing boundaries.

You have the DO WHILE outside the sub-domain but the LOOP inside it. In essence, the DO WHILE is invisible to the LOOP because they are in different levels of nesting (if you prefer that terminology). Therefore, your error message of LOOP WITHOUT DO pertains to the incorrect order of LOOP and END IF.

 

[Cronk]

My take on this is that I wonder why the need for a loop through all records in the sub form. Surely a query would suffice.

 

[June7]

I also ask why save calculated value instead of just calculate when needed? What is purpose of EN_Update_Info field?

 

[The_Doc_Man]

Cronk and June7 both raise valid points for consideration.

WHENEVER you are about to iterate through a recordset, this question will be valid: Can I do this via an update query instead? Or an aggregate query if you are doing summations. Or a SELECT query that involves a formula if you are looking at a computed value that doesn't change its inputs by the fact of the computation. (I.e. if you compute XYZ but X, Y, and Z don't change because of the computation.)

Queries, because the query processor is managed by directly executed code, is more efficient than programmed VBA recordset loops which are interpreted, not executed. VBA does not truly compile to machine code - it compiles to pseudo-code that then is executed interpretively by the VBA processor.