Loop Through Recordset

[GendoPose]

Hi All,

In my database, I have a continuous form with a Name, a Date and a Yes/No field.

When the form opens, I want to look at the date of every record on the form and show a message box if it is before the current day.

The code I have is this;

Private Sub Form_Load()
    With Me.RecordsetClone
        While Not .EOF
            If Me.Date1 < Date Then
                MsgBox "" & Me.Person & ""
            End If
            If Not .EOF Then .MoveNext
        Wend
    End With
End Sub

However, it loops just the first record the amount of times there is of records (ie, it will only show the first person's name in the message box, and will show 3 times if there are 3 records).

What exactly am I doing wrong here?

Thanks for any help!

 

[pr2-eugin]

Three things, two at the minimum; I would say needs to be changed.

1. The choice of Form view, with continuous forms you have to physically move to the second record, using DoCmd.MoveNext to get it executed,
2. Then comes the choice of Method, best it is moved to the Current method than Load.
3. Finally (optional) loose the If before the MoveNext method.

 

[GendoPose]

Three things, two at the minimum; I would say needs to be changed.

1. The choice of Form view, with continuous forms you have to physically move to the second record, using DoCmd.MoveNext to get it executed,
2. Then comes the choice of Method, best it is moved to the Current method than Load.
3. Finally (optional) loose the If before the MoveNext method.

Right I've changed it to a Single Form, moved the code to the Current event and changed the line to read just .MoveNext but I'm still getting the same problem, it just shows the name in the first record 3 times?

 

[pr2-eugin]

So what is that you are after exactly? You need to display the names of customers whose date entry is less than today?

 

[GendoPose]

So what is that you are after exactly? You need to display the names of customers whose date entry is less than today?

Similar, it's to show when a staff member last had training (I'll change the date entry part to less than 2 months ago), and if that training was more than 2 months ago, show a message box. Eventually, this will go in an invisible subform on the navigation page so you see who needs training when you open up the database.

 

[vbaInet]

I'll add some extra points to Paul's and highlight some things in your code:

1. The choice of Form view, with continuous forms you have to physically move to the second record, using DoCmd.MoveNext to get it executed,
2. Then comes the choice of Method, best it is moved to the Current method than Load.
3. Finally (optional) loose the If before the MoveNext method.

4. You're not referring to any field in the recordset in your condition.
5. What's the point of this exercise? It may end up display lots of message boxes. Why don't you use a query?

Private Sub Form_Load()
    With Me.RecordsetClone
        While Not .EOF
            If [COLOR="Red"]![/COLOR]Date1 < Date[COLOR="red"]()[/COLOR] Then
                MsgBox "" & [COLOR="red"]![/COLOR]Person & ""
            End If
           [COLOR="red"].MoveNext[/COLOR]
        Wend
    End With
End Sub

 

[GendoPose]

I'll add some extra points to Paul's and highlight some things in your code:

4. You're not referring to any field in the recordset in your condition.
5. What's the point of this exercise? It may end up display lots of message boxes. Why don't you use a query?

Brilliant, that's got it, thanks!

The point is that this will be quite a rarity as staff join at different times and there's only a handful of people in this department, 12 at the most, and all their training will be at different times. It's more to trigger the user to send an email and set up the training and so they don't forget to keep checking the query.

 

[vbaInet]

Would a query not suffice? At least setting the criteria in a query would.