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

Do Loop in Access Code Runs to Infinity

P: 5
I have inherited someone else's database and process. There is a report that would not work. The problem lies in the code but I do not know enough to fix it.

I turned on all Warnings to help me determine where the problem is. The queries loop continuously as expected but when 0 rows are returned the loop needs to stop.

I have determined this because if I hit No when it asks to return 0 records the report appears in perfect condition. Can someone help me with the EscapeEndlessLoop that they appear to be using but is not working.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdPrintLabels_Click()
  2.  
  3. DoCmd.SetWarnings True
  4. DoCmd.OpenQuery "qdClearLabelQuantities"
  5. DoCmd.OpenQuery "qdClearLabels"
  6. DoCmd.OpenQuery "qaLabelQuantity"
  7. DoCmd.SetWarnings True
  8.  
  9. Dim cmd As ADODB.Command
  10. Dim rst As ADODB.Recordset
  11. Dim prm As ADODB.Parameter
  12.  
  13. Set cmd = New ADODB.Command
  14. Set cmd.ActiveConnection = CurrentProject.Connection
  15. cmd.CommandText = "qsLabelQuantityNotZeroNew"
  16. cmd.CommandType = adCmdTable
  17.  
  18. cmd.Parameters.Refresh
  19. For Each prm In cmd.Parameters
  20.     prm.Value = Eval(prm.Name)
  21. Next prm
  22.  
  23. Set rst = cmd.Execute
  24.  
  25. DoCmd.SetWarnings True
  26.  
  27. Do
  28.     DoCmd.OpenQuery "qaPartNumberLabels"
  29.     DoCmd.OpenQuery "quQuantityMinusOneNew"
  30.     On Error GoTo EscapeEndlessLoop
  31.     rst.MoveFirst
  32. Loop
  33.  
  34. EscapeEndlessLoop:
  35. rst.Close
  36. Set rst = Nothing
  37. Set cmd = Nothing
  38.  
  39. DoCmd.SetWarnings True
  40.  
  41. DoCmd.OpenReport "rlPartNumbers", acViewPreview
  42.  
  43. End Sub
May 4 '14 #1
Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,430
No error is thrown when a query has no records. Instead, loop until the query returns no records by using a DCount on the query.
May 4 '14 #2

P: 5
Thank you for responding but I don't really know how to write code. What would that DCount look like and where within the Loop command would I put it?

Thanks in advance for any assistance.
May 5 '14 #3

Rabbit
Expert Mod 10K+
P: 12,430
Since you don't know how to write code, you will probably want to learn how to before taking on a project like this.

Here is a link to a tutorial: http://www.functionx.com/vbaccess/Lesson01.htm

And a link to the DCount documentation: http://office.microsoft.com/en-us/ac...001228817.aspx
May 5 '14 #4

P: 5
Thank you. I was told it was just a simple Access database or I would have turned it down. Thanks again for your help. I think I'll go take a VBA class ASAP.
May 5 '14 #5

Rabbit
Expert Mod 10K+
P: 12,430
Once you have some of the basics down, let us know if you run into trouble with the DCount. You can post the code you used and any error messages you get.
May 5 '14 #6

Post your reply

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