RecordSet Do Loop having condition or move to next condition

[MitchR]

I need some process help for moving next in a do loop when the if statement condition is met. I am opening a recordset that contains 3 date fields and a serial number. I have 4 If statements.. My Update statements are working well but I cannot figure out how to process the operations to move to the next record and execute the action consistently

1.) If the FirstEmailDate field for the serial number record is not populated then populate the current date and rs.movenext

2.) otherwise if FirstEmailDate is populated and SecondEmailDate is not populated then populate the current date into SecondEmailDate and rs.movenext

3.) otherwise if FirstEmailDate and SecondEmailDate fields are populated then populate the current date into ThirdEmailDate and rs.movenext

4.) otherwise if FirstEmailDate and SecondEmailDate and Third EmailDate fields are populated then MsgBox that threshold has been met, user clicks ok and rs.movenext

Set rs0 = DB.OpenRecordset(strSQL0)
   If rs0.EOF Then
        rs0FindRecordCount = 0
    Else
        rs0.MoveLast
        rs0.MoveFirst
Do Until rs0.EOF
        rs0FindRecordCount = rs0.RecordCount
        
        SNComment = rs0.u_serial_number
        
            If Nz(Forms![BigFix Management Form].First_Email_Date, "") = "" Then
            
            CurrentDb.Execute "UPDATE Status_and_Updates SET [First_Email_Date]" & _
            "= '" & vardate & "' WHERE [u_serial_number] = '" & SNComment & "'"
        rs0.MoveNext
             
             
            ElseIf Nz(Forms![BigFix Management Form].Second_Email_Date, "") = "" Then
            
            CurrentDb.Execute "UPDATE Status_and_Updates SET [Second_Email_Date]" & _
            "= '" & vardate & "' WHERE [u_serial_number] = '" & SNComment & "'"
         rs0.MoveNext
            
            ElseIf Nz(Forms![BigFix Management Form].Third_Email_Date, "") = "" Then
            
            CurrentDb.Execute "UPDATE Status_and_Updates SET [Third_Email_Date]" & _
            "= '" & vardate & "' WHERE [u_serial_number] = '" & SNComment & "'"
        rs0.MoveNext
            End If
            
        If Nz(Forms![BigFix Management Form].Third_Email_Date, "") <> "" & Nz(Forms![BigFix Management Form].Second_Email_Date, "") <> "" & Nz(Forms![BigFix Management Form].First_Email_Date, "") <> "" Then
                MsgBox "This device or user has already received 3 emails!" & vbCrLf & _
               "This user is now a candidate for account lockout." & vbCrLf & _
               "Please email this user's name " & rs0.UserName & vbCrLf & _
               "and their system: " & rs0.u_serial_number & " to you manager at this time", , "User has reached lockout point"
            End If
        rs0.MoveNext
    Loop

[twinnyfo]

MitchR,

You should be able to simple remove the first three iterations of rs0.MoveNext. The queries will only execute if the particular criteria are met. After all the checking, then just move to the next record.

[NeoPa]

My first comment would be that you should always try to indent your code. Non-indented code is very difficult to read and work with. The only thing worse than non-indented code is code that is indented - but not in any consistent way. That's a disaster and should be avoided if you possibly can. It makes it extraordinarily difficult to work with. My indenting below will start from the left but you will probably have to indent it further to fit within a procedure.

So, I'll assume for now that the the parts you've not posted are nevertheless in your actual code somewhere as I don't want to waste time with trivial details you should have covered already.

NB. Obviously what you've posted can't possibly work. This may be partly or completely due to your leaving out parts that you consider irrelevant. As a general rule, even if you include only a part of the code, what you include should be code that at least compiles. Posting uncompiled code is a waste of everyone's time and experts are liable to get upset with members that don't first ensure that what they've posted is both Copy/Pasted and is from code that compiles.

NB. I've suggested some code that matches your explanation. Your existing code references a Form that isn't included in that explanation so I hope I haven't been wasting my time based on your not explaining the situation accurately. However, your explanation does make sense, while your code doesn't, so that's what I've gone with.

Dim strTemplate As String, strMsg As String
Dim fldVar As DAO.Field

strTemplate = Replace("This device or user has already received 3 emails!%L" _
                    & "This user is now a candidate for account lockout.%L" _
                    & "Please email this user's name '%N'%L" _
                    & "and system (%S) to your manager." _
                    , "%L", VbNewLine)
Set rs0 = DB.OpenRecordset(strSQL0)
With rs0
    If .EOF Then
        rs0FindRecordCount = 0
    Else
        Call .MoveLast
        Call .MoveFirst
        rs0FindRecordCount = .RecordCount
        Do Until .EOF
            If IsNull(!First_Email_Date) Then
                Set fldVar = !First_Email_Date
            ElseIf IsNull(!second_Email_Date) Then
                Set fldVar = !Second_Email_Date
            ElseIf IsNull(!Third_Email_Date) Then
                Set fldVar = !Third_Email_Date
            Else
                Set fldVar = Nothing
            End If
            If Not fldVar Is Nothing Then
                Call .Edit
                fldVar = Date
                Call .Update
            Else
                strMsg = Replace(strTemplate, "%N", !UserName)
                strMsg = Replace(strMsg, "%S", !u_serial_number)
                Call MsgBox(Prompt:=strMsg _
                          , Title:="User has reached lockout point")
            End If
            Call .MoveNext
        Loop
    End If
End With

[Rabbit]

Is there a reason you need to do this in a recordset instead of an update query?

[NeoPa]

As we currently have no idea of what's in strSQL0 I didn't even go there.

That's not to say it's a bad idea, but it will require some practical feedback from Mitch.

PS. Update.
While this particular post (OP) may be a bit of a mess I've looked back over some of his earlier posts and he's always tried to post well and always interacts positively and politely, so I would say you can expect a reasonable and helpful reply relatively shortly.

[twinnyfo]

NeoPa,

Very creative approach and very elegant. Your usage of fldVar has given me a new tool to use. I always end up using .Field.Name, which just gets messy.

Thanks again!

[MitchR]

Team,

Thank you! Thank you! I really appreciate your time and patience with me and my code. I have been spinning in the mud to make this work and NeoPa's solution was the skeleton key to the lock. I went through and indented all of my code right off the bat. It is so much easier to follow now. Twinnyfo is absolutely on point about fldvar. It is awesome!
The purpose of the op creates an email in outlook and I have a similar procedure for Lotus notes that locates a user and the devices assigned to that person. Having the results displayed in a form; I work to capture the asset data for each user in strSQL0. My thinking here was to have one recordset and keep using it over and over for the different processes.
I use the (strSQL0) to generate a HTML formatted email with images and with a listing of the devices assigned in the body (strSQL0).
NeoPa helped me tremendously a couple of weeks ago with an issue in the capture of a first name & last name from a firstname.lastn ame Email address for this purpose.
Then I use (strSQL0) again to add comments from a textbox on the form to each record in (strSQL0).
Now with NeoPa's assistance again, I can update the email date in the tblStatus_and_U pdates with the send date using (strSQL0).

strSQL0 = _
  "SELECT tblStatus_and_Updates.u_serial_number" & _
    ", Left([tblStatus_and_Updates].[Email_ADDR]" & _
        ",InStr([tblStatus_and_Updates].[Email_ADDR]" & _
        ",""@"")-1) AS UserName" & _
    ", tblStatus_and_Updates.First_Email_Date" & _
    ", tblStatus_and_Updates.Second_Email_Date" & _
    ", tblStatus_and_Updates.Third_Email_Date" & _
  "FROM tblStatus_and_Updates" & _
  "WHERE" & _
    "(((tblStatus_and_Updates.Status) Is Null)" & _
        " AND ((tblStatus_and_Updates.EID)='" & varEID & "'))" & _
      " OR (((tblStatus_and_Updates.Status) Not Like ('" & varFix & "'))" & _
        " AND ((tblStatus_and_Updates.EID)='" & varEID & "'));"

[NeoPa]

That's great Mitch. I'm very pleased I was able to help. Particularly so that you chose to take onboard the rationale of proper code indenting. You should find it helps enormously.

All that said, you may get to learn a bit more about the fundamentals of Access if Rabbit chooses to progress with the SQL UPDATE query that he was referring to in post #4, now that you've supplied the info that he'd need.

As it might be a more than basic set of SQL I'll leave that to him to decide if it's worth the effort. Certainly an UPDATE query would affect all the changes in a more efficient way, and could be done as part of a single transaction more easily.

PS. When you're ready for it, you can also consider indenting your SQL in such a way as to make it easier to read and work with. I, and many others have extressed the same thing, find it much easier to work with SQL that's formatted consistently and legibly. It gets more and more important as you work directly in SQL more, of course.

[NeoPa]

TwinnyFo:
Very creative approach and very elegant. Your usage of fldVar has given me a new tool to use. I always end up using .Field.Name, which just gets messy.

Thank you my friend.

An object variable, even if of a specific type, also has the advantage that it can be set to, and read as, Nothing, which we use in this particular construct.

[MitchR]

Everyone, I really appreciate your taking time and a great deal of patience out of your day to help me with this app. Thank You!

Rabbit,
Thank you for giving me another way to solve this one! If you have a minute, I would love see about using an update query as you suggested. I would like to optimize the OP as much as possible. Being able to run the Update Query from a SQL statement inside the sub would be awesome. But I will admit I am not really sure how to proceed with incorporating the conditions.

Thank you NeoPa! I really appreciate your taking the extra time to show me how to use best practices in my operation in addition to the answer for my issue. I really want to learn the correct way to produce a better applications.

[Rabbit]

If you just need to update the data and don't need the user feedback, you can accomplish that by using an update query that uses nested iifs.

[NeoPa]

Something along the lines of (and you'll need to handle creating the SQL from VBA) :

UPDATE [tblStatus_and_Updates]
SET    [First_Email_Date]=IIf([First_Email_Date] Is Null,Date(),[First_Email_Date])
     , [Second_Email_Date]=IIf(([First_Email_Date] Is Not Null) AND ([Second_Email_Date] Is Null),Date(),[Second_Email_Date])
     , [Third_Email_Date]=IIf(([Second_Email_Date] Is Not Null) AND ([Third_Email_Date] Is Null),Date(),[Third_Email_Date])
WHERE  ([EID]='%EID')
  AND  (([First_Email_Date] Is Null)
   OR   ([Second_Email_Date] Is Null)
   OR   ([Third_Email_Date] Is Null))

NB. The VBA would need to ensure that the replacement parameter (%EID for the EID) is reflected in the eventual SQL that's executed.

Also, bear in mind this wouldn't handle the message for those who have exhausted all their lives. So, probably not a solution at the end of the day but nevertheless worth looking at.