Results 1 to 12 of 12
  1. #1
    Join Date
    May 2009
    Posts
    20

    Unanswered: Print report copies based on criteria in a query

    I am trying to create a form where my use can enter a sales order number and it will bring up the line items in a query.

    She can then type into that query the number of copies of a report she needs for each line item.

    SELECT dbo_SO_Detail.ORDNUM_28, dbo_SO_Detail.LINNUM_28, dbo_SO_Detail.DELNUM_28, dbo_SO_Detail.FILL04_28 AS Qty
    FROM dbo_SO_Detail
    WHERE (((dbo_SO_Detail.ORDNUM_28)=[Forms]![frmPrintLabels]![txtSalesOrder]));

    ORDNUM_28 LINNUM_28 DELNUM_28 Qty
    20047532 01 01 2
    20047532 02 01 3
    20047532 03 01 11


    Then she pushes a button and she gets all the reports printed out that she needs; two copies for line 01, 3 copies for line 02 and eleven copies for line 03.

    I have this bad boy that I copied off another web site but it doesn't work.

    Private Sub Print_Label_Click()
    On Error GoTo Err_Print_Label_Click

    Dim stDocName As String
    Dim numCopies As Integer

    stDocName = "Label"

    numCopies = Queries![qryPrintLabels]![Qty]

    DoCmd.PrintOut Copies:=numCopies

    Exit_Print_Label_Click:
    Exit Sub

    Err_Print_Label_Click:
    MsgBox Err.Description
    Resume Exit_Print_Label_Click

    End Sub

    Please help! I am stuck!

    Lucy

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Several remarks about your code:
    Code:
    Private Sub Print_Label_Click()
    On Error GoTo Err_Print_Label_Click
    
    Dim stDocName As String
    Dim numCopies As Integer
    
    10 stDocName = "Label"
    
    20 numCopies = Queries![qryPrintLabels]![Qty]
    
    30 DoCmd.PrintOut Copies:=numCopies
    Line 10: Apart from assigning the value "Label" to the variable stDocName, you never use it. It should probably be used to open the report that prints the labels but this report is not open anywhere in the function.

    Line 20: As far as I can understand, you try to reference a column (Qty) of a query (qryPrintLabels). Where is this query open?

    Line 30: According to Access documentation, the PrintOut method of the DoCmd object prints the active object of the open database. The active object can be a DataSheet, a Report, a Form, a Data Access Page or a Module. What's the active object in your database when the code is run? To me and according to your explanations, it should be the form with the command button that command the execution of the function Print_Label_Click and certainly not a report named "Label".

    If (and only if) there is a Report object in your database that's named "Label" and if this Report object prints what you expect, here is a function that should work:
    Code:
    Function PrintLabels(ByVal SalesOrder As Variant)
    
        Dim rst As DAO.Recordset
        Dim strSQL As String
        Dim i As Integer
        
        strSQL = "SELECT dbo_SO_Detail.ORDNUM_28, dbo_SO_Detail.FILL04_28 " & _
                 "FROM dbo_SO_Detail " & _
                 "WHERE dbo_SO_Detail.ORDNUM_28 = " & SalesOrder & ";"
    
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        With rst
            Do Until .EOF
                For i = 1 To !FILL04_28
                    DoCmd.OpenReport "Label", acViewNormal, , "ORDNUM_28=" & !ORDNUM_28
                Next i
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
    
    End Function
    You can call this function from the form frmPrintLabels using:
    Code:
    Private Sub Print_Label_Click()
    
        PrintLabels Me.txtSalesOrder
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    May 2009
    Posts
    20

    Not quite there yet

    Thanks much for the code.

    I got a run-time error '3464':
    Data type mismatch in criteria expression. The field "Qty" is a text field. It's a user defined field in the Sales Order Detail table.

    Is there a way to convert it to a number?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If [ORDNUM_28] is a Text type column, the line becomes:
    Code:
    DoCmd.OpenReport "Label", acViewNormal, , "ORDNUM_28='" & !ORDNUM_28 & "'"
    Have a nice day!

  5. #5
    Join Date
    May 2009
    Posts
    20

    Still getting the same error

    Nope, sorry, same error message.

    The line:

    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

    is highlighted yellow in the debugger if that helps at all.

    Lucy

  6. #6
    Join Date
    May 2009
    Posts
    20

    Query criteria

    And the Sales Order number is coming from a text box on the form:

    [Forms]![frmPrintLabels]![txtSalesOrder]

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No need to be sorry

    1. How are [ORDNUM_28] and [FILL04_28] defined in the table dbo_SO_Detail (Data Type)?

    2. Is there a reference to [ORDNUM_28] present into the report Label1?

    3. Can you post the contents of the variable strSQL when the function stops? (With the line Set rst = ... highlighted, open the immediate window (Ctrl+G) and type ? strSQL followed by the Enter key to display the value of the variable).
    Have a nice day!

  8. #8
    Join Date
    May 2009
    Posts
    20

    Text Fields

    They are both text fields.

    Lucy

  9. #9
    Join Date
    May 2009
    Posts
    20

    Bound query

    Here is the query that is bound to the report "Labels":

    SELECT dbo_SO_Detail.ORDNUM_28, dbo_SO_Detail.LINNUM_28, dbo_SO_Detail.DELNUM_28, dbo_SO_Detail.FILL04_28 AS Qty, dbo_SO_Detail.PRTNUM_28, dbo_Part_Master.PMDES1_01, dbo_Part_Master.PMDES2_01, dbo_Part_Master.PCKG_01, dbo_Customer_Part_Data.CUSTPRT_103
    FROM (dbo_SO_Detail INNER JOIN dbo_Part_Master ON dbo_SO_Detail.PRTNUM_28 = dbo_Part_Master.PRTNUM_01) LEFT JOIN dbo_Customer_Part_Data ON (dbo_SO_Detail.PRTNUM_28 = dbo_Customer_Part_Data.PRTNUM_103) AND (dbo_SO_Detail.CUSTID_28 = dbo_Customer_Part_Data.CUSTID_103)
    WHERE (((dbo_SO_Detail.ORDNUM_28)=[Forms]![frmPrintLabels]![txtSalesOrder]));

  10. #10
    Join Date
    May 2009
    Posts
    20

    Question 3

    Here is the answer to question 3:

    ? strSQL
    SELECT dbo_SO_Detail.ORDNUM_28, dbo_SO_Detail.FILL04_28 FROM dbo_SO_Detail WHERE dbo_SO_Detail.ORDNUM_28 = 20047532;

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Then, as [ORDNUM_28] is of type Text, the instruction used to compose the SQL statement should be:
    Code:
       strSQL = "SELECT dbo_SO_Detail.ORDNUM_28, dbo_SO_Detail.FILL04_28 " & _
                 "FROM dbo_SO_Detail " & _
                 "WHERE dbo_SO_Detail.ORDNUM_28 = '" & SalesOrder & "';"
    Have a nice day!

  12. #12
    Join Date
    May 2009
    Posts
    20
    Here is my code as it currently stands:

    Function PrintLabels(ByVal SalesOrder As Variant)

    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim Qty As Integer
    Dim i As Integer

    strSQL = "SELECT dbo_SO_Detail.ORDNUM_28, dbo_SO_Detail.FILL04_28 " & _
    "FROM dbo_SO_Detail " & _
    "WHERE dbo_SO_Detail.ORDNUM_28 = '" & SalesOrder & "';"

    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    With rst
    Do Until .EOF
    For i = 1 To !dbo_SO_Detail.FILL04_28
    DoCmd.OpenReport "Label", acViewNormal, , "ORDNUM_28=" & !ORDNUM_28 & "'"
    Next i
    .MoveNext
    Loop
    .Close
    End With
    Set rst = Nothing

    End Function


    Now I am getting the message:

    Compile error:

    Method or data member not found.

    .FILL04_28 is highlighted

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •