Stop append query from running when it reaches 0 records

[bterri50]

I inherited a previous employees project in Access 2010 and need to fix a loop that locks the database. I set Warnings to True in the code to see where the problem is so that I could escape out and avoid the lockup. After pressing Yes to append rows from about 30 tables the query eventually returns 0 records. Choosing NO pulls up the report perfectly. However, it isn't very effective to sit and press the ENTER key 20 times every time I need the report. I can't seem to find the right combination of code to make this work and there is no documentation on the project. Any help would be appreciated. The code is below. I've tried a variety of rst.Move commands and MoveFirst is the only thing that seems to call these queries correctly.

Private Sub cmdPrintLabels_Click()

DoCmd.SetWarnings False
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 While Not rst.EOF
    
   DoCmd.OpenQuery "qaPartNumberLabels"
   DoCmd.OpenQuery "quQuantityMinusOneNew"
   On Error GoTo EscapeEndlessLoop
   rst.MoveFirst
Loop
    
EscapeEndlessLoop:

rst.Close
Set rs = Nothing
Set cmd = Nothing

DoCmd.SetWarnings True

DoCmd.OpenReport "rlPartNumbers", acViewPreview

End Sub

[jimatqsi]

What is the looping all about? Why does the second time through the loop do anything differently from the first time through? If it is because quQuantityMinus OneNew changes something that affects qaPartNumberLab els, making qaPartNumberLab els find nothing to do, you could do this:

dim rs2 as dao.Recordset
set rs2 = Currentdb.OpenRecordSet(Select Count(*) from qaPartNumberLabels",dbreadonly)
rs2.movelast
if rs2.RecordCount=0 then we're done
rs2.close
set rs2 = nothing

It will be something like that but probably not exactly that because you can't do that using an action query. But you can write a query that will Select based on the same criteria as your action query, open the select query to get the record count and then decide what to do based on that.

There may be a more elegant way to do that but without knowing more about your queries I can't think of one now.

Don't forget to close RS2 whether the following IF test is true or false.

Jim

[bterri50]

Jim,

I agree the queries are looping for no good reason but when I stop the looping the report is wrong. There are quite a few nested queries so it is going to be a long process. I was hoping for a quick solution in the meantime.

I am not having any luck with your suggestion but am going to continue trying to find the right combination.

Thanks.

[twinnyfo]

bterri50,

It appears that your code is doing this:

1. It clears out the Old and appends the New.

2. You create a recordset finding all labels that are not zero.

3. In your loop, you append records that (probably) have a value greater than zero, and then update those same records to reduce the value by one.

4. Then, you keep looping through by just moving to the first record of the recordset. (I can only see that this would cause an infinite loop--no way around it).

Along the lines of Jim's advice, why not place a different type of loop into your code as such:

Do While Not DCount("*", [qsLabelQuantityNotZeroNew], "[Quantity] > 0") <> 0
   DoCmd.OpenQuery "qaPartNumberLabels" 
   DoCmd.OpenQuery "quQuantityMinusOneNew" 
Loop

This will re-evaluate your query each time through, until your query can't find any more Labels that have a quantity other than zero associated with it.

As a side note, in general, I don't like to use append queries in order to build a recordset for a Report, the data of which is just then deleted again, unless I really have to. It could be that you really have to in this situation, but not knowing the details of your tables and your label printing requirements, I can't say.

Hope this hepps!