Do Loop in Access Code Runs to Infinity

[bterri50]

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 EscapeEndlessLo op that they appear to be using but is not working.

Private Sub cmdPrintLabels_Click()

DoCmd.SetWarnings True
DoCmd.OpenQuery "qdClearLabelQuantities"
DoCmd.OpenQuery "qdClearLabels"
DoCmd.OpenQuery "qaLabelQuantity"
DoCmd.SetWarnings True

Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim prm As ADODB.Parameter

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "qsLabelQuantityNotZeroNew"
cmd.CommandType = adCmdTable

cmd.Parameters.Refresh
For Each prm In cmd.Parameters
    prm.Value = Eval(prm.Name)
Next prm

Set rst = cmd.Execute

DoCmd.SetWarnings True

Do
    DoCmd.OpenQuery "qaPartNumberLabels"
    DoCmd.OpenQuery "quQuantityMinusOneNew"
    On Error GoTo EscapeEndlessLoop
    rst.MoveFirst
Loop

EscapeEndlessLoop:
rst.Close
Set rst = Nothing
Set cmd = Nothing

DoCmd.SetWarnings True

DoCmd.OpenReport "rlPartNumbers", acViewPreview

End Sub

[Rabbit]

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.

[bterri50]

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.

[Rabbit]

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

[bterri50]

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.

[Rabbit]

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.