Printing Labels Programmatically in Access

Question

I'm in need of giving autonomy to people regarding the printing of labels without the need of having to use Excel cells as an input. In other words people will just have to open an access database and use a form to print from a design gerenated report.

As it stands, I have a query view named SSCC_Gen, which displays a temporary table that will store the labels for printing, a form named Imp_Etiq_SCC and a report named Labels_SSCC_Gen

Sample of SSCC_Gen query view

Sample of Imp_Etiq_SCC Form

Sample of Labels_SSCC_Gen Report

Now, the purpose of the Imp_Etiq_SCC Form is to give the user the ability to print a certain number of labels (that will be generated before printing them), after the user selects the printer where the labels will be print and types in the number of labels to print.

Here's the code for the Form_Load event:

Private Sub Form_Load()
    Dim printerIndex As Integer

    For printerIndex = 0 To Application.Printers.Count - 1
        Me.cbPrintersList.AddItem (Application.Printers(printerIndex).DeviceName)
    Next printerIndex
End Sub

And here's the code for the btnPrint_Click event

Private Sub btnPrint_Click()
    'Catch blank control.
    'If set to 0, label report is blank but runs.

    If IsNull(Me.txtNumberOfLabels) Or Not IsNumeric(Me.txtNumberOfLabels.Value) Then
        MsgBox "Please indicate the number of labels you want to print", _
    vbOKOnly, "Error"
        DoCmd.GoToControl "txtNumberOfLabels"
        Exit Sub
    End If

    Dim availablePrinters As Printer
    Dim selectedPrinter As String

    DoCmd.GoToControl "cbPrintersList"

    selectedPrinter = Me.cbPrintersList.Text

    For Each availablePrinters In Application.Printers
        If availablePrinters.DeviceName = selectedPrinter Then
            Set Application.Printer = availablePrinters
            Exit For
        End If
    Next availablePrinters

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Dim lastLabelRecordIndex_Part1 As String
    Dim lastLabelRecordIndex_Part2 As String
    Dim oldLastLabelRecordIndex_Part1 As String
    Dim oldLastLabelRecordIndex_Part2 As String

    Dim strSQL As String

    Set db = CurrentDb
    strSQL = "SELECT MAX(Pre_SSCC) As MaxRecord FROM SSCC_Gen"
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

    oldLastLabelRecordIndex_Part1 = CStr(Left(rs("MaxRecord"), 8))
    oldLastLabelRecordIndex_Part2 = CStr(Right(rs("MaxRecord"), 9))

    rs.Close
    db.Close

    Dim labelRecordIndex As Long

    DoCmd.SetWarnings False

    For labelRecordIndex = CLng(oldLastLabelRecordIndex_Part2) To CLng(oldLastLabelRecordIndex_Part2) + Me.txtNumberOfLabels.Value - 1
        DoCmd.RunSQL "INSERT INTO SSCC_GenCount (Data) VALUES (#" & Format(Now(), "dd/mm/yyyy") & "#)"
    Next labelRecordIndex

    DoCmd.SetWarnings True

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

    lastLabelRecordIndex_Part1 = CStr(Left(rs("MaxRecord"), 8))
    lastLabelRecordIndex_Part2 = CStr(Right(rs("MaxRecord"), 9))
    rs.Close
    db.Close

    Dim oldLastLabelRecordIndex As String
    Dim lastLabelRecordIndex As String

    oldLastLabelRecordIndex = oldLastLabelRecordIndex_Part1 & oldLastLabelRecordIndex_Part2
    lastLabelRecordIndex = lastLabelRecordIndex_Part1 & lastLabelRecordIndex_Part2

    Dim test As Report

    Set test = Reports![Labels_SSCC_Gen]
    test.RecordSource = "SELECT * FROM SSCC_Gen WHERE Pre_SSCC BETWEEN " & CStr(oldLastLabelRecordIndex) & " AND " & CStr(lastLabelRecordIndex)

    'Variables for debugging purposes
    'Dim reportNameMemo As String
    'Dim reportOpenArgsMemo As String
    'reportNameMemo = test.Name
    'reportOpenArgsMemo = test.RecordSource

    DoCmd.OpenReport test.Name, acViewReport
    DoCmd.PrintOut , , , , 1
End Sub

My problem is on the following lines of code:

Set test = Reports![Labels_SSCC Gen]
DoCmd.OpenReport test.Name, acViewReport

Now, if I don't open the report on access, I get the following error on debug, for the first line of code I pointed out above (roughly translated):

• The name of the report 'Labels_SSCC_Gen' you entered in the expression is misspelled or refers to a report that is not open or does not exist.

However, if I do open the report (even just on design view), on debug I see the report loading up (showing up a progress bar on access), but after a while it crashes with the following error, for the second line of code pointed out above:

• Incorrect data type in criteria expression

First of all, I cannot understand why I need to have the report open to be able to access the properties, therefor I need to know if there's a way to set the recordsource of the report without the need to open it, because the purpose is to just to use the form.

Secondly, I do not understand where the incorrect data type error even comes from, because for starters, All I'm doing is to run a query on the SSCC_Gen query view so that the report only retrieves the labels that are intended for printing and the query is well written, following the example below (taken while debug was running):

"SELECT * FROM SSCC_Gen WHERE Pre_SSCC BETWEEN 35601871000061300 AND 35601871000061301"

May someone shed some light as to what am I doing wrong? What am I missing?

Answer 1

This article outlines the things you can pass to the OpenReport command. It's very flexible. Generally, the best approach is to set a query or SQL for the RecordSource in the designer, and then apply a Filter Name or Where Condition when you call OpenReport.

In your case, I would imagine that the RecordSource would be SELECT * FROM SSCC_Gen, and your Where condition that you pass to OpenReport would be Pre_SSCC BETWEEN 35601871000061300 AND 35601871000061301.

Answer 2

Ok, so I changed the code and everything is working except one thing... After printing, if I want to close Access it gives me the following error message (roughly translated):

This action cannot be performed when processing a form or report event. A macro specified as the OnOpen, OnLoad, OnClose, OnFormat, OnRemove, OnPage, or OnPrint property definition contains an invalid action for the property. When you click OK, an Action Failed dialog box will show the name of the macro that failed and its arguments.

However, if I open the report, even if it's just on design-view, I can then close access.

I'm at a loss because I've no idea what's impeding access from being closed.

Here's my current code:

Private Sub btnPrint_Click()
'Validate Input Given. If the input is less than or equal to 0 discard the print command.
'Two If statements, one for validating the input type and if the input is a positive number.

If IsNull(Me.txtNumberOfLabels) Or Not IsNumeric(Me.txtNumberOfLabels.Value) Then
    MsgBox "Please indicate the number of labels you want to print", _
    vbOKOnly, "Error"
    DoCmd.GoToControl "txtNumberOfLabels"
    Exit Sub
End If

If Me.txtNumberOfLabels.Value <= 0 Then
    MsgBox ("O número de etiquetas a imprimir deve ser superior a 0")
    Exit Sub
End If

Dim availablePrinters As Printer
Dim selectedPrinter As String

DoCmd.GoToControl "cbPrintersList"

selectedPrinter = Me.cbPrintersList.Text

For Each availablePrinters In Application.Printers
    If availablePrinters.DeviceName = selectedPrinter Then
        Set Application.Printer = availablePrinters
        Exit For
    End If
Next availablePrinters

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim lastLabelRecordIndex_Part1 As String
Dim lastLabelRecordIndex_Part2 As String
Dim lastLabelRecordIndex_Part3 As String
Dim oldLastLabelRecordIndex_Part1 As String
Dim oldLastLabelRecordIndex_Part2 As String
Dim oldLastLabelRecordIndex_Part3 As String

Dim strSQL As String

Set db = CurrentDb
strSQL = "SELECT MAX(Pre_SSCC) As MaxRecord FROM SSCC_Gen"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

oldLastLabelRecordIndex_Part1 = CStr(Left(rs("MaxRecord"), 8))
oldLastLabelRecordIndex_Part2 = CStr(Mid(rs("MaxRecord"), 9, 4))
oldLastLabelRecordIndex_Part3 = CStr(Right(rs("MaxRecord"), 5))

rs.Close
db.Close

Dim labelRecordIndex As Long

DoCmd.SetWarnings False

For labelRecordIndex = CLng(oldLastLabelRecordIndex_Part3) To CLng(oldLastLabelRecordIndex_Part3) + Me.txtNumberOfLabels.Value - 1
    DoCmd.RunSQL "INSERT INTO SSCC_GenCount (Data) VALUES (#" & Format(Now(), "dd/mm/yyyy") & "#)"
Next labelRecordIndex

DoCmd.SetWarnings True

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

lastLabelRecordIndex_Part1 = CStr(Left(rs("MaxRecord"), 8))
lastLabelRecordIndex_Part2 = CStr(Mid(rs("MaxRecord"), 9, 4))
lastLabelRecordIndex_Part3 = CStr(Right(rs("MaxRecord"), 5))
rs.Close
db.Close

Dim oldLastLabelRecordIndex As String
Dim lastLabelRecordIndex As String

oldLastLabelRecordIndex = oldLastLabelRecordIndex_Part1 & oldLastLabelRecordIndex_Part2 & CStr(oldLastLabelRecordIndex_Part3 + 1)
lastLabelRecordIndex = lastLabelRecordIndex_Part1 & lastLabelRecordIndex_Part2 & lastLabelRecordIndex_Part3

DoCmd.SetWarnings False

DoCmd.OpenReport Report_Labels_SSCC_Gen.Name, , , "Pre_SSCC BETWEEN '" & CStr(oldLastLabelRecordIndex) & "' AND '" & CStr(lastLabelRecordIndex) & "'", acHidden

    DoCmd.SetWarnings True
End Sub

Private Sub Form_Load()
    Dim printerIndex As Integer

    For printerIndex = 0 To Application.Printers.Count - 1
        Me.cbPrintersList.AddItem (Application.Printers(printerIndex).DeviceName)
    Next printerIndex
End Sub

I'm pretty sure the problem lies in following line of code, but I've no idea what I can do to overcome this:

• DoCmd.OpenReport Report_Labels_SSCC_Gen.Name, , , "Pre_SSCC BETWEEN '" & CStr(oldLastLabelRecordIndex) & "' AND '" & CStr(lastLabelRecordIndex) & "'", acHidden

Any suggestions?

Solution:

All that was needed was to set the modal and popup properties, of the report window itself, to true.

Everything's fine and dandy now :)

Thanks everyone for their time.