Print labels for each subform record 2

[TrekBiker]

This is a follow on question about printing labels for subform records.

I want the procedure launched by the Create Barcodes button on the main form to step through all subform rows and print the 'Quantity' number of copies of ones with CaskGroup = Cask Beer or Craft Beer.

So with the current selection to print 2 for Oscar Wilde, then 1 for Jake The Snake, then 2 for Cascade, all in one operation.

My first attempt was based on the source for the subform and didn't split the printing by Barcode.

 

[dhookom]

Based on the code in the previous thread:

With Forms!frmPickList.sfmPickListBarcodes.Form.RecordsetClone
    Dim intI as Integer
    .MoveFirst
    Do Until .EOF
        For intI = 1 to !Quantity
            Debug.Print !ProductName.Value
            Debug.Print !Quantity.Value
	Next
       .MoveNext
    Loop
End With

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

More like:

Dim intI as Integer

With Forms!frmPickList.sfmPickListBarcodes.Form.RecordsetClone
    .MoveFirst
    Do Until .EOF[blue]
        If !CaskGroup.Value = "Cask Beer" or !CaskGroup.Value = "Craft Beer" Then[/blue]
            For intI = 1 to !Quantity
                Debug.Print !ProductName.Value
                Debug.Print !Quantity.Value
	    Next[blue]
        End If[/blue]
       .MoveNext
    Loop
End With

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

Good catch on the filter Andy.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Thank you so much, both of you. This is getting near to the solution.

I now want to create labels for the lower subform's records, printing the Quantity for each, so 2 for the first Cask Beer, 1 for the second, and 2 for the Craft Beer.

I tried building the RecordSource for rptPickListBarcode as below

Private Sub cmdCreateBarcodes_Click()

    Dim intI As Integer

    With Forms!frmPickList.sfmPickListBarcodes.Form.RecordsetClone
        .MoveFirst
            Do Until .EOF
            If !CaskGroup.Value = "Cask Beer" Or !CaskGroup.Value = "Craft Beer" Then
            For intI = 1 To !Quantity
            
            Reports!rptPickListBarcode.RecordSource = "SELECT !ProductName.Value,!Barcode.Value;"
               
            DoCmd.OpenReport "rptPickListBarcode", acViewPreview
            DoCmd.PrintOut , , , , !Quantity.Value
               
            Next
        End If
       .MoveNext
    Loop
    
End With

This gave error 2451, 'this report is misspelled or doesn't exist'.

If the answer is to add the data to the report's OnOpen procedure how would I reference !ProductName and !Quantity?

Or am I barking up the wrong trees?!

 

[dhookom]

You can't change the record source of a report that isn't open. I would set the record source to a saved query with a name like:

qselRptPickListBarCode

Then your code might be

Private Sub cmdCreateBarcodes_Click()

    Dim intI As Integer
    Dim strSQL as String
    With Forms!frmPickList.sfmPickListBarcodes.Form.RecordsetClone
        .MoveFirst
            Do Until .EOF
            If !CaskGroup.Value = "Cask Beer" Or !CaskGroup.Value = "Craft Beer" Then
            For intI = 1 To !Quantity
                [COLOR=#4E9A06]'May need to add more filters to the query[/color]
                strSQL = "SELECT ... FROM ...  WHERE ProductName = '" & !ProductName & "' AND Barcode = '" & !Barcode & "' "
                debug.Print strSQL
                CurrentDb.Querydefs("qselRptPickListBarCode").SQL = strSQL
                [COLOR=#4E9A06]'you should be able to print without the preview[/color]
                DoCmd.OpenReport "rptPickListBarcode", acViewPreview
                DoCmd.PrintOut , , , , !Quantity.Value
               
            Next
        End If
       .MoveNext
    Loop
    
End With

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Wouldn't that code create "2 [reports] for the first Cask Beer, 1 [report] for the second, and 2 [reports] for the Craft Beer."

To have just one report, you may consider:

Private Sub cmdCreateBarcodes_Click()

    Dim intI As Integer
    Dim strSQL as String
    With Forms!frmPickList.sfmPickListBarcodes.Form.RecordsetClone
        .MoveFirst
            Do Until .EOF
            If !CaskGroup.Value = "Cask Beer" Or !CaskGroup.Value = "Craft Beer" Then
            For intI = 1 To !Quantity[blue]
                If Len(strSQL) > 0 Then
                    strSQL = strSQL & " UNION ALL "
                End If[/blue][green]
                'May need to add more filters to the query[/green]
                strSQL =[blue] strSQL & [/blue]"SELECT ... FROM ...  WHERE ProductName = '" & !ProductName & "' AND Barcode = '" & !Barcode & "' "[green]
                'debug.Print strSQL
                'CurrentDb.Querydefs("qselRptPickListBarCode").SQL = strSQL
                ''you should be able to print without the preview
                'DoCmd.OpenReport "rptPickListBarcode", acViewPreview
                'DoCmd.PrintOut , , , , !Quantity.Value               [/green]
            Next
        End If
       .MoveNext
    Loop
End With 

If Len(strSQL) > 0 Then
    debug.Print strSQL
    CurrentDb.Querydefs("qselRptPickListBarCode").SQL = strSQL[green]
    'you should be able to print without the preview[/green]
    DoCmd.OpenReport "rptPickListBarcode", acViewPreview
    DoCmd.PrintOut
End If

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Thanks Duane, your help is much appreciated.

Had to change the code a bit because I don't think the For loop for intI is needed because Docmd.PrintOut has the quantity parameter option. It was printing duplicate pages with it in.

Private Sub cmdCreateBarcodes_Click()

   'Dim intI As Integer
    Dim strSQL As String
    With Forms!frmPickList.sfmPickListBarcodes.Form.RecordsetClone
        .MoveFirst
            Do Until .EOF  

            If !CaskGroup.Value = "Cask Beer" Or !CaskGroup.Value = "Craft Beer" Then
    '            For intI = 1 To !Quantity
            
                   'May need to add more filters to the query
                    Debug.Print !ProductName, !Barcode
                    strSQL = "SELECT CompanyName, Town, Barcode, ProductName From qryPickListBarcodes"
                    strSQL = strSQL & " WHERE ProductName = '" & !ProductName & "' AND Barcode = '" & !Barcode & "'; "
                    Debug.Print strSQL
                    Debug.Print "                                 "
    
                    CurrentDb.QueryDefs("qryPickListItem").SQL = strSQL
                    
                   'You should be able to print without the preview
                    DoCmd.OpenReport "rptPickListBarcode"
                    DoCmd.PrintOut , , , , !Quantity.Value
'                Next
            End If
        .MoveNext
        Loop

    End With
End Sub

The debug lines produced these for the data set I tried, and they are correct.

The only remaining issue now is I'm getting blank pages when printing to a standard printer, not a label printer.

The Margins are 4 to 6 mm and the column settings are Width 8cm, Height 5.5cm

Any final thought, the star is already on the way!

 

[TrekBiker]

Two additions

Andy - thanks for this thought, which I'll look at next

Duane - the final comment about printing is now irrelevant because the label printer produces single pages.

But I was only getting one label for each product. I restored the For loop and it's now perfect.

Thanks for all your time, star each.