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

Inefficient Loop

P: 6

I'm trying to simplify and make this piece of code more efficient (basically would like to remove the 2nd loop); any suggestions?


Expand|Select|Wrap|Line Numbers
  1.     Dim db As Database, rst1 As Recordset, rst2 As Recordset, qry1 As String, qry2 As String
  2.     Dim intCurrentProgress As Integer, intTotalWidth As Integer, intProgressBarMax As Integer
  4.     q = MsgBox("Are you sure you want to update all ranks?", vbExclamation + vbOKCancel)
  5.     If q = vbCancel Then Exit Function
  7.     qry1 = "SELECT DISTINCT tblStudents.StudentID, tblStudents.RankID FROM tblStudents INNER JOIN tblGradingResults ON tblStudents.StudentID = tblGradingResults.StudentID"
  11.     Set db = CurrentDb()
  12.     Set rst1 = db.OpenRecordset("qryLastGradingPerStudent")
  13.     intProgressBarMax = rst1.RecordCount
  15.     SysCmd acSysCmdInitMeter, "Updating: ", intProgressBarMax
  16.     If Not rst1.EOF Then
  17.         rst1.MoveFirst
  18.         Do Until rst1.EOF
  20.         SysCmd acSysCmdUpdateMeter, intCurrentProgress
  22.         qry2 = "SELECT tblStudents.StudentID, tblStudents.RankID, tblStudents.LastGrading FROM tblStudents WHERE (((tblStudents.StudentID)=" & rst1!StudentID & "))"
  23.         Set rst2 = db.OpenRecordset(qry2)
  24.             rst2.Edit
  25.                 rst2!RankID = rst1!RankID
  26.                 rst2!LastGrading = rst1!GradingDate
  27.                 rst2.Update
  28.             rst2.Close
  29.                 Set rst2 = Nothing
  32.             'update Progress Barr
  33.             intCurrentProgress = intCurrentProgress + 1
  34.         rst1.MoveNext
  35.         Loop
  36.     End If
  38.     rst1.Close
  39. '    rst2.Close
  40.     Set rst1 = Nothing
  41.     Set rst2 = Nothing
  42.     Set db = Nothing
Feb 19 '12 #1
Share this Question
Share on Google+
6 Replies

Expert Mod 10K+
P: 12,366
Is there a reason you're doing it with a recordset instead of using an update query?
Feb 20 '12 #2

P: 6
I did, but got an error: "Operation must use and updateable query"; hence why I used the recordset.
Feb 20 '12 #3

Expert Mod 100+
P: 2,321
The reason it is not updatable is because you are using the keyword Distinct in your SQL, which also leads me to wonder if your data is properly normalised since you feel you need the distinct keyword.

I agree with Rabbit that from what you have presented here, it seems more like something for an update query,but we would need more details on your table design to say anything for sure.

I would still like however to answer your question.
In terms of making your code more efficient, you need to move the second recordset opening out of the loop, and then use rst2.findfirst and rst2.findNext combined with rst2.Nomatch (use the help function to read about the 3 functions) inside the loop.
Feb 20 '12 #4

P: 6
Thanks for your answer Smiley. Qry1 is no longer required (I forgot to delete it from the code). The table structure is quite complex and can't be changed at this point of time.

I'm not sure to understand how to use the "find first" and "find Next" option, could you please give me more details?

Feb 20 '12 #5

Expert Mod 100+
P: 2,321
I still need more info on your table structure to give you a proper answer. At best I would be guessing and probably confusing you.

Imagine however a tbl_Orders (with primary Key: PK_Order) and a tbl_OrderDetails (With Foreign Key FK_Order) in which we would want to loop through the orders shipped today, specifying that each item was shipped succesfully.
Expand|Select|Wrap|Line Numbers
  1. Dim cDB as dao.Database
  2. Dim rsOrder as Dao.RecordSet
  3. Dim rsOrderDetails as Dao.Recordset
  4. set rsOrder=cDB.OpenRecordset("SELECT tbl_Order.PK_Order Where dt_OrderDate=Date()",dbopendynaset)
  5. Set rsOrderDetails =cdb.OpenRecordset("tbl_OrderDetails",dbOpenDynaset)
  7. dim strCriteria as string
  8. Do while not rsOrder.EOF
  9.   strCriteria="FK_Order=" & rsOrder!PK_Order
  10.   rsOrderDetails.FindFirst strCriteria
  11.   Do while NOT rsOrderDetails.Nomatch
  12.     rsOrderDetails.Edit
  13.       rsOrderDetails!b_Shipped=True
  14.     rsOrderDetails.Update
  16.     'Move to next match
  17.     rsOrderDetails.FindNext strCriteria
  18.   Loop
  19. Loop
  22. 'Cleanup
  23. set cDb=Nothing
  24. set rsOrder=nothing
  25. set rsOrderDetails=nothing
Feb 20 '12 #6

P: 6
FindFirst works like a charm, speed gain is great. Thanks for your help.

Feb 21 '12 #7

Post your reply

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