473,324 Members | 2,535 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

RecordSet Do Loop having condition or move to next condition

MitchR
65 64KB
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
  9.  
  10.         SNComment = rs0.u_serial_number
  11.  
  12.             If Nz(Forms![BigFix Management Form].First_Email_Date, "") = "" Then
  13.  
  14.             CurrentDb.Execute "UPDATE Status_and_Updates SET [First_Email_Date]" & _
  15.             "= '" & vardate & "' WHERE [u_serial_number] = '" & SNComment & "'"
  16.         rs0.MoveNext
  17.  
  18.  
  19.             ElseIf Nz(Forms![BigFix Management Form].Second_Email_Date, "") = "" Then
  20.  
  21.             CurrentDb.Execute "UPDATE Status_and_Updates SET [Second_Email_Date]" & _
  22.             "= '" & vardate & "' WHERE [u_serial_number] = '" & SNComment & "'"
  23.          rs0.MoveNext
  24.  
  25.             ElseIf Nz(Forms![BigFix Management Form].Third_Email_Date, "") = "" Then
  26.  
  27.             CurrentDb.Execute "UPDATE Status_and_Updates SET [Third_Email_Date]" & _
  28.             "= '" & vardate & "' WHERE [u_serial_number] = '" & SNComment & "'"
  29.         rs0.MoveNext
  30.             End If
  31.  
  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
  40.  
Jul 9 '18 #1
11 3560
twinnyfo
3,653 Expert Mod 2GB
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.
Jul 9 '18 #2
NeoPa
32,556 Expert Mod 16PB
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
  3.  
  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
Rabbit
12,516 Expert Mod 8TB
Is there a reason you need to do this in a recordset instead of an update query?
Jul 9 '18 #4
NeoPa
32,556 Expert Mod 16PB
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
twinnyfo
3,653 Expert Mod 2GB
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!
Jul 10 '18 #6
MitchR
65 64KB
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.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
NeoPa
32,556 Expert Mod 16PB
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
NeoPa
32,556 Expert Mod 16PB
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.
Jul 10 '18 #9
MitchR
65 64KB
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.
Jul 10 '18 #10
Rabbit
12,516 Expert Mod 8TB
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
NeoPa
32,556 Expert Mod 16PB
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

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

Similar topics

36
by: Remi Villatel | last post by:
Hi there, There is always a "nice" way to do things in Python but this time I can't find one. What I'm trying to achieve is a conditionnal loop of which the condition test would be done at...
2
by: Patrik | last post by:
Hi, I cannot find the right way to write this code. I keep getting the error : Next without For Here's my code. For Each Choix In RapInd_Code.ItemsSelected S6 =...
3
by: Douglas | last post by:
Hi, In the loop for(i=0; i< h+1; i++); if h=10 say, then is h+1 evaluated every time or, between iterations, does the for loop remember that the condition i<11 is being applied? I'm guessing...
3
by: Ben R. | last post by:
In an article I was reading (http://www.ftponline.com/vsm/2005_06/magazine/columns/desktopdeveloper/), I read the following: "The ending condition of a VB.NET for loop is evaluated only once,...
2
by: solar | last post by:
Is there a command in VB with which to move to the next row in the table of Access database ? For example my table TblCrates consists of : CrateId City 1. Berlin I need a...
4
MMcCarthy
by: MMcCarthy | last post by:
The following code is simply an example of some code that processes through two recordsets. It can be helpful for anyone curious as to how to start processing with recordsets (Which objects to refer...
55
by: indhu | last post by:
HI all one sequence has many panels. when i select combo it displays all the fields but panel has more rows. i want to view that also. how to do that? move next and previous commds are there...
3
by: dianaj86 | last post by:
I have multiple dropdownlists each one filled with values from a specific column in the table. Also I have multiple textboxes corresponding to dropdownlists. For example, when I select an item from...
2
by: GlenC | last post by:
Hello everyone, I'm brand new to this forum and have not yet trolled through all the posts so bear with me please. I am a beginner at VB.Net programming (using VB express 2008) and (of course)...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.