Loop on a form

[barbarao]

Hi, I hope someone can help me. I have the following set on the On Current of a form's property and it doesn't loop. I don't see anything wrong with the code. Thanks in advance.

Set rst = Me.RecordsetClone
Set db = CurrentDb
 
 
If rst.RecordCount = 0 Then Exit Sub
 
rst.MoveFirst
Do While Not rst.EOF
rst.Edit
 
 
If Me.StartDate >= DateAdd("yyyy", -1, Date) Then Me.Eligible = "<12Months" Else Me.Eligible = "Eligible"
 
rst.Update
 
rst.MoveNext 
Loop
 
 
rst.Close
Set rst = Nothing
Set db = Nothing
 
Close
 

[NeoPa]

Line #12 uses multi-line If syntax on a single line.

Please see Before Posting (VBA or SQL) Code.

[barbarao]

I don't know how to fix that as I didn't know it was wrong. Can you help me? Thanks.

[NeoPa]

To fix your code you would use :

If Me.StartDate >= DateAdd("yyyy", -1, Date) Then
    Me.Eligible = "<12Months"
Else
    Me.Eligible = "Eligible"
End If

However, for such a requirement the following code may prove more sensible :

Me.Eligible = IIf(Me.StartDate >= DateAdd("yyyy", -1, Date), "<12Months", "Eligible")

PS. That's not the response I would have expected, had you read the linked page. Please make sure you do before posting again.
-Moderator.

[barbarao]

I tried both of those suggestions and the loop is not looping with either one. I did read the linked page; I'm still learning this stuff which is why I am reaching out when I don't understand something that I have read.

[Rabbit]

Why are you doing it this way instead of using an update query?

[barbarao]

That just might work. I will try that and let you know. Thanks.

[barbarao]

thanks so much. you are always very helpful.

[Rabbit]

Glad to help.

[NeoPa]

BarbaraO:
I tried both of those suggestions and the loop is not looping with either one.

I can't see why that would be if you put either of my snippets of code in place of line #12.

I would certainly recommend going for an update query if the record source is predictable. Sometimes though, forms have a record source that is complex and hard to reproduce in a query without complicated parsing of strings.

If you wish to proceed on the original question then please post your latest compiled and tested code (which should include the whole of the procedure) and explain exactly what you're seeing as results, with emphasis on describing any problems clearly.