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

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

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:

And a link to the DCount documentation:
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

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.