×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Print labels for each subform record
2

Print labels for each subform record

Print labels for each subform record

(OP)

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.

RE: Print labels for each subform record

Based on the code in the previous thread:

CODE --> vba

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

RE: Print labels for each subform record

More like:

CODE

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
                Debug.Print !ProductName.Value
                Debug.Print !Quantity.Value
	    Next
        End If
       .MoveNext
    Loop
End With 

---- Andy

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

RE: Print labels for each subform record

Good catch on the filter Andy.

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

RE: Print labels for each subform record

(OP)

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

CODE -->

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?!

RE: Print labels for each subform record

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

CODE --> vba

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
                strSQL = "SELECT ... FROM ...  WHERE ProductName = '" & !ProductName & "' AND Barcode = '" & !Barcode & "' "
                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
               
            Next
        End If
       .MoveNext
    Loop
    
End With 

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

RE: Print labels for each subform record

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

To have just one report, you may consider:

CODE

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
                If Len(strSQL) > 0 Then
                    strSQL = strSQL & " UNION ALL "
                End If
                'May need to add more filters to the query
                strSQL = strSQL & "SELECT ... FROM ...  WHERE ProductName = '" & !ProductName & "' AND Barcode = '" & !Barcode & "' "
                '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               
            Next
        End If
       .MoveNext
    Loop
End With 

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

---- Andy

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

RE: Print labels for each subform record

(OP)

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.

CODE -->

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!


RE: Print labels for each subform record

(OP)

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close