By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,319 Members | 1,963 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,319 IT Pros & Developers. It's quick & easy.

RecordSet Do Loop having condition or move to next condition

P: 63
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
Expand|Select|Wrap|Line Numbers
  1. Set rs0 = DB.OpenRecordset(strSQL0)
  2.    If rs0.EOF Then
  3.         rs0FindRecordCount = 0
  4.     Else
  5.         rs0.MoveLast
  6.         rs0.MoveFirst
  7. Do Until rs0.EOF
  8.         rs0FindRecordCount = rs0.RecordCount
  10.         SNComment = rs0.u_serial_number
  12.             If Nz(Forms![BigFix Management Form].First_Email_Date, "") = "" Then
  14.             CurrentDb.Execute "UPDATE Status_and_Updates SET [First_Email_Date]" & _
  15.             "= '" & vardate & "' WHERE [u_serial_number] = '" & SNComment & "'"
  16.         rs0.MoveNext
  19.             ElseIf Nz(Forms![BigFix Management Form].Second_Email_Date, "") = "" Then
  21.             CurrentDb.Execute "UPDATE Status_and_Updates SET [Second_Email_Date]" & _
  22.             "= '" & vardate & "' WHERE [u_serial_number] = '" & SNComment & "'"
  23.          rs0.MoveNext
  25.             ElseIf Nz(Forms![BigFix Management Form].Third_Email_Date, "") = "" Then
  27.             CurrentDb.Execute "UPDATE Status_and_Updates SET [Third_Email_Date]" & _
  28.             "= '" & vardate & "' WHERE [u_serial_number] = '" & SNComment & "'"
  29.         rs0.MoveNext
  30.             End If
  32.         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
  33.                 MsgBox "This device or user has already received 3 emails!" & vbCrLf & _
  34.                "This user is now a candidate for account lockout." & vbCrLf & _
  35.                "Please email this user's name " & rs0.UserName & vbCrLf & _
  36.                "and their system: " & rs0.u_serial_number & " to you manager at this time", , "User has reached lockout point"
  37.             End If
  38.         rs0.MoveNext
  39.     Loop
Jul 9 '18 #1
Share this Question
Share on Google+
11 Replies

Expert Mod 2.5K+
P: 3,284

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.
Jul 9 '18 #2

Expert Mod 15k+
P: 31,494
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.
Expand|Select|Wrap|Line Numbers
  1. Dim strTemplate As String, strMsg As String
  2. Dim fldVar As DAO.Field
  4. strTemplate = Replace("This device or user has already received 3 emails!%L" _
  5.                     & "This user is now a candidate for account lockout.%L" _
  6.                     & "Please email this user's name '%N'%L" _
  7.                     & "and system (%S) to your manager." _
  8.                     , "%L", VbNewLine)
  9. Set rs0 = DB.OpenRecordset(strSQL0)
  10. With rs0
  11.     If .EOF Then
  12.         rs0FindRecordCount = 0
  13.     Else
  14.         Call .MoveLast
  15.         Call .MoveFirst
  16.         rs0FindRecordCount = .RecordCount
  17.         Do Until .EOF
  18.             If IsNull(!First_Email_Date) Then
  19.                 Set fldVar = !First_Email_Date
  20.             ElseIf IsNull(!second_Email_Date) Then
  21.                 Set fldVar = !Second_Email_Date
  22.             ElseIf IsNull(!Third_Email_Date) Then
  23.                 Set fldVar = !Third_Email_Date
  24.             Else
  25.                 Set fldVar = Nothing
  26.             End If
  27.             If Not fldVar Is Nothing Then
  28.                 Call .Edit
  29.                 fldVar = Date
  30.                 Call .Update
  31.             Else
  32.                 strMsg = Replace(strTemplate, "%N", !UserName)
  33.                 strMsg = Replace(strMsg, "%S", !u_serial_number)
  34.                 Call MsgBox(Prompt:=strMsg _
  35.                           , Title:="User has reached lockout point")
  36.             End If
  37.             Call .MoveNext
  38.         Loop
  39.     End If
  40. End With
Jul 9 '18 #3

Expert Mod 10K+
P: 12,366
Is there a reason you need to do this in a recordset instead of an update query?
Jul 9 '18 #4

Expert Mod 15k+
P: 31,494
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.
Jul 10 '18 #5

Expert Mod 2.5K+
P: 3,284

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!
Jul 10 '18 #6

P: 63

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.lastname 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_Updates with the send date using (strSQL0).

Expand|Select|Wrap|Line Numbers
  1. strSQL0 = _
  2.   "SELECT tblStatus_and_Updates.u_serial_number" & _
  3.     ", Left([tblStatus_and_Updates].[Email_ADDR]" & _
  4.         ",InStr([tblStatus_and_Updates].[Email_ADDR]" & _
  5.         ",""@"")-1) AS UserName" & _
  6.     ", tblStatus_and_Updates.First_Email_Date" & _
  7.     ", tblStatus_and_Updates.Second_Email_Date" & _
  8.     ", tblStatus_and_Updates.Third_Email_Date" & _
  9.   "FROM tblStatus_and_Updates" & _
  10.   "WHERE" & _
  11.     "(((tblStatus_and_Updates.Status) Is Null)" & _
  12.         " AND ((tblStatus_and_Updates.EID)='" & varEID & "'))" & _
  13.       " OR (((tblStatus_and_Updates.Status) Not Like ('" & varFix & "'))" & _
  14.         " AND ((tblStatus_and_Updates.EID)='" & varEID & "'));"
Jul 10 '18 #7

Expert Mod 15k+
P: 31,494
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.
Jul 10 '18 #8

Expert Mod 15k+
P: 31,494
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.
Jul 10 '18 #9

P: 63
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!

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.
Jul 10 '18 #10

Expert Mod 10K+
P: 12,366
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.
Jul 10 '18 #11

Expert Mod 15k+
P: 31,494
Something along the lines of (and you'll need to handle creating the SQL from VBA) :
Expand|Select|Wrap|Line Numbers
  1. UPDATE [tblStatus_and_Updates]
  2. SET    [First_Email_Date]=IIf([First_Email_Date] Is Null,Date(),[First_Email_Date])
  3.      , [Second_Email_Date]=IIf(([First_Email_Date] Is Not Null) AND ([Second_Email_Date] Is Null),Date(),[Second_Email_Date])
  4.      , [Third_Email_Date]=IIf(([Second_Email_Date] Is Not Null) AND ([Third_Email_Date] Is Null),Date(),[Third_Email_Date])
  5. WHERE  ([EID]='%EID')
  6.   AND  (([First_Email_Date] Is Null)
  7.    OR   ([Second_Email_Date] Is Null)
  8.    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.
Jul 12 '18 #12

Post your reply

Sign in to post your reply or Sign up for a free account.