Batch Print

[birdlegs]

One major function of my database will be to print labels to be used for solicitation. Not all labels will be printed at the same time and I need a way to not repeat printing. I have little experience in VBA and I'm sure there is a better, quicker way to do what I've managed to do thus far. But I am stuck on how to finish this out.

Database Structure:
tblCo with fields: pkCoID, strCo, strAddress, strZip, fkBatch, ysnLabels
tblBatch with fields: pkBatch, dteBatchCreated
qryCo joins the tables
qryTop30 filters 30 companies without batch id's and is used for creating the batches
qryToPrint filters companies already in a batch but that have been printed yet.
frmLabels will essentially be the print dialogue (and is where I'm stuck)

Because the labels print 30 per page, I have code to create batches in groups of 30. The code executes by cmdBatch button.
I have a textbox on frmLabels for the user to enter the number of pages to print.
How do I then use that input to select that many batches and then print when user clicks cmdPrint?
And then once those labels have been printed, I want to change the ysnLabels from 0 to -1 so they aren't printed again.
I have it to where I can select 1 batch for printing, but what about multiples?

The code for creating batches is working:

 Private Sub cmdBatch_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String
    Dim lngBatch As Long
    Dim i As Integer
    Dim mb As Integer
    
    m = Me.txtMaxBatch.Value
    
    If mb = 0 Then
        MsgBox "All labels have been batched."
    Else
        For i = 1 To mb
            Set db = CurrentDb()
            Set rs = db.OpenRecordset("tblBatch", dbOpenDynaset, dbAppendOnly)
            rs.AddNew
                rs!BatchCreated = Now()
                lngBatch = rs!pkBatch
            rs.Update
            rs.Close
            
            strSql = "UPDATE qryTop30 Set fkBatch = " & lngBatch & " WHERE ysnLabels = 0;"
            db.Execute strSql, dbFailOnError
            
            DoCmd.Requery qryTop30
        Next i
    End If
    
Exit_Handler:
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
    
Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "cmdCreate_Click()"
    Resume Exit_Handler
    
End Sub

I can print 1 Batch. How do I print x number of batches and then mark those batches as printed using the ysnLabels?

Private Sub cmdPrint_Click()
'On Error GoTo Err_Handler
    Dim strWhere As String
    Const strcDoc = "rptLabels"
    Dim i As Integer
    Dim p As Integer
    
    p = Me.txtPrint.Value
    
    'Close the report if it's already open (so the filtering is right.)
    If CurrentProject.AllReports(strcDoc).IsLoaded Then
        DoCmd.Close acReport, strcDoc
    End If
    
    'Open it filtered to the batch in the list box.
    strWhere = "fkBatch = " & DMin("[fkBatch]", "qryToPrint")
    DoCmd.OpenReport strcDoc, acViewPreview, , strWhere

Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, ".cmdPrintBatch_Click"
    Resume Exit_Handler
    
End Sub

[zmbd]

There are several ways I can think of how to do what you're after using several methods, such as an extra field in the table, tempvars-collection, another table (or two), etc...
Would you need a record of when the batches were printed?
Would you need to know which labels were in each batch?
Would you need to know the last time a record's label was printed?

If no to all of the questions then I would use a tempvars collection to hold the record numbers, and possibly a batching value, needed for the batches and a counter to handle printing each batch of labels the required number of times.

The complexity increases if you answer yes to any of the questions. The simplest would be a flag field in the table that is set when selected, a DAO.Recordset to filter those records out for printing using SQL-Top to return just the first 30 records, an inner loop to print the required number of copies, code to clear the flag on the returned records, and the outer-loop to fetch the next 30 records.

From there we're looking possibly a running table with batch IDs related to the record table and/or more tables to hold the history.

Also please consider changing this:

Set rs = Nothing
Set db = Nothing

to this

If Not rs  Is Nothing Then
     rs.close
     Set rs  = Nothing
  End if
If Not db Is Nothing Then Set db = Nothing

If you open it close it first before releasing the object
If you set on object then release it - but check that it's been set first!
This will avoid memory leak, data corruption, and errors that seem to pop-up from nowhere.
(BTW: NEVER do db.close on the when using Set db = CurrentDb() - I did that by mistake in one of my early applications, took me ages to find the error!!)

[birdlegs]

I have the table that holds batch numbers and the date and time batches were created. But no, I don't need to know when the labels were printed, just IF they were printed, which is what the ysnLabels field is for. Basically, I need to know, if the user wants to print three pages of labels, for example, how to select three batch numbers (I am guessing maybe a GROUP BY sql) and load only those batches to the rptLabels to print. Then after printing is successful (confirmed with message box) I need to check the ysnfield so they aren't printed again.
And thank you for the tips on .Close at the end. Very insightful and helpful!

[NeoPa]

Hi Birdlegs.

Putting this in simple terms you would do something like the following :

1. Identify those items that you want selected for a print run of labels.
2. Copy those items identified into a specially-designed temporary table used for the printing of the labels.
3. You can allow incrementally adding items in with multiple batches if you choose or, if not, you can clear any existing records prior to this step. If you do the latter then make sure they are used to identify the records that have been printed if appropriate. That depends on the stage at which you require these to be flagged. See #2 below.
4. When do you want the originals flagged as printed? If it's as soon as they're identified then ensure the data from this temporary table is used to update the originals to reflect that. If it is later, once they've actually been submitted to print, or even once you have humanly determined they have printed correctly, then apply this step later in the overall process where it makes sense.
5. Whether you trigger the next step manually (Using a CommandButton for instance.) or logically (Once some records have been identified and made available.) doesn't matter too much, but proceed onto it one way or the other.
6. Print off the items from within your temporary table to the printer.
7. Once you have determined that all have been successfully printed then (optionally) update the original data (See #3.) then DELETE all the records from the temporary table.

If you follow this logic you should have a system that does what you want it to do.