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

Stop append query from running when it reaches 0 records

P: 5
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdPrintLabels_Click()
  3. DoCmd.SetWarnings False
  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
  26. Do While Not rst.EOF
  28.    DoCmd.OpenQuery "qaPartNumberLabels"
  29.    DoCmd.OpenQuery "quQuantityMinusOneNew"
  30.    On Error GoTo EscapeEndlessLoop
  31.    rst.MoveFirst
  32. Loop
  34. EscapeEndlessLoop:
  36. rst.Close
  37. Set rs = Nothing
  38. Set cmd = Nothing
  40. DoCmd.SetWarnings True
  42. DoCmd.OpenReport "rlPartNumbers", acViewPreview
  44. End Sub
Jun 4 '14 #1
Share this Question
Share on Google+
3 Replies

Expert 100+
P: 1,240
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 quQuantityMinusOneNew changes something that affects qaPartNumberLabels, making qaPartNumberLabels find nothing to do, you could do this:
Expand|Select|Wrap|Line Numbers
  1. dim rs2 as dao.Recordset
  2. set rs2 = Currentdb.OpenRecordSet(Select Count(*) from qaPartNumberLabels",dbreadonly)
  3. rs2.movelast
  4. if rs2.RecordCount=0 then we're done
  5. rs2.close
  6. 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.

Jun 4 '14 #2

P: 5

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.

Jun 4 '14 #3

Expert Mod 2.5K+
P: 3,488

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:

Expand|Select|Wrap|Line Numbers
  1. Do While Not DCount("*", [qsLabelQuantityNotZeroNew], "[Quantity] > 0") <> 0
  2.    DoCmd.OpenQuery "qaPartNumberLabels" 
  3.    DoCmd.OpenQuery "quQuantityMinusOneNew" 
  4. 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!
Jun 10 '14 #4

Post your reply

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