We help IT Professionals succeed at work.

Print labels in sets of two using ActiveX

D4430
D4430 asked
on
476 Views
Last Modified: 2013-11-27
Hi. In Microsoft Access, labels are printed for each record in a table. There are four labels. ActiveX automation is used to call each label, and the application works fine.

Each product has two serial number labels, so the top piece and bottom piece can be labeled the same. The request is to print the serial numbers in a 1,1,2,2,3,3,4,4,5,5,... series. As it works today, for 10 records, it will print 1-10 of serial number 1 label,  then 1-10 of serial number 2 label. Any ideas? The code of the command button on the form is included below.

 Private Sub cmdConfirmLabelInfo_Click()
On Error GoTo Err_cmdConfirmLabelInfo_Click

    Dim stDocName As String

    stDocName = "m_Create_Records_to_Print"
    DoCmd.RunMacro stDocName

    Dim LvApp
    Dim Label1
    Dim Label2
    Dim Label3
    Dim Label4

    Set LvApp = CreateObject("LabelVision.Application")
   
    Set Label1 = LvApp.OpenLabel("C:\LABELS\BOX.lbx")
    If Forms![frm_User_Input]![chkPrintBox] = True Then Label1.PrintOut
   
    Set Label2 = LvApp.OpenLabel("C:\LABELS\SERIALNUM1.lbx")
    If Forms![frm_User_Input]![chkPrintSN1] = True Then Label2.PrintOut
   
    Set Label3 = LvApp.OpenLabel("C:\LABELS\SERIALNUM2.lbx")
    If Forms![frm_User_Input]![chkPrintSN2] = True Then Label3.PrintOut
   
    Set Label4 = LvApp.OpenLabel("C:\LABELS\SKID.lbx")
    If Forms![frm_User_Input]![chkPrintSkid] = True Then Label4.PrintOut
   
    LvApp.Quit
   
Exit_cmdConfirmLabelInfo_Click:
    Exit Sub
Comment
Watch Question

Commented:
Im a little confused...
are you using an access report to print the labels or is the label application printing them?

are you trying to print two seperate labels with the same barcode or four seperate labels, or two copies of one label?

lastly, can you upload an example file so that I can see exactly what your trying to do?

Jon Estey

Author

Commented:
Hi. From Access, I'm calling the label application to print the labels. The data source of the label is pointed to a table in access.

Table example
item                serial number          
XYZ123                    B0001
XYZ123                    B0002
XYZ123                    B0003
XYZ123                    B0004

What I can do is print the labels as a group.
                                           box 1-4
                                           serial number1    1-4
                                           serial number2    1-4
                                           skid 1-4

What I am trying to do is print the serial numbers differently. Let's ignore the box and skid labels for the moment as these are fine.

Instead of printing all of one and then all of the other, I need:
 
                                          serial number1      1
                                          serial number2      1
                                          serial number1      2
                                          serial number2      2
                                          serial number1      3
                                          serial number2      3
                                           ................

Commented:
Ok, I get ya
I dont really know how the labeling app works but how about something like this...
make a third serial number template SERIALNUM3.lbx which has both of the labels as one (so for every record both labels are printed)
then adjust this portion of the code:
    Set Label2 = LvApp.OpenLabel("C:\LABELS\SERIALNUM1.lbx")
    If Forms![frm_User_Input]![chkPrintSN1] = True Then Label2.PrintOut
   
    Set Label3 = LvApp.OpenLabel("C:\LABELS\SERIALNUM2.lbx")
    If Forms![frm_User_Input]![chkPrintSN2] = True Then Label3.PrintOut
to be like this:
    If Forms![frm_User_Input]![chkPrintSN1] = True AND Forms![frm_User_Input]![chkPrintSN2] = True then
        Set Label2 = LvApp.OpenLabel("C:\LABELS\SERIALNUM3.lbx")
        Label2.PrintOut
    else
        Set Label2 = LvApp.OpenLabel("C:\LABELS\SERIALNUM1.lbx")
        If Forms![frm_User_Input]![chkPrintSN1] = True Then Label2.PrintOut
   
        Set Label3 = LvApp.OpenLabel("C:\LABELS\SERIALNUM2.lbx")
        If Forms![frm_User_Input]![chkPrintSN2] = True Then Label3.PrintOut
     end if


Jon Estey

Commented:
as an aside I think loading your labels would be more efficient like this...

    Dim LabelTemp
    If Forms![frm_User_Input]![chkPrintBox] = True Then
         Set LabelTemp = LvApp.OpenLabel("C:\LABELS\BOX.lbx")
         LabelTemp.PrintOut
         Set LabelTemp = Nothing
    end if

its a couple extra lines of code but that way you would only load the labels that your going to print.
Also (someone may want to correct me on this) I think its better to set objects to nothing when your done with them

Jon Estey

Author

Commented:
Thanks for the good ideas. I think that the next step is to look at doing something like:

Open the table
         look at the record
                    print label 1
                    print label 2
         go to the next record in the table

Commented:
aaa thats a good question, are you using a label printer or an inkjet (aka can you print a single label at a time?)

Jon Estey

Author

Commented:
Yes, I will be printing single labels at a time. One Zebra printer is loaded with 4 x 6 inch labels for boxes and the other Zebra printer is loaded with smaller labels for the serial numbers.

Author

Commented:
Hi. This has been put together to read the table and print a record. It will print the same record twice. How do I tell it to move to the next record?

Public Sub PrintSerialLabels()

    Dim rst As DAO.Recordset
    Dim dbs As Database
    Dim strSQL As String
   
        strSQL = "Select * from Label_Info;"
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset(strSQL)
   
            With rst
            Debug.Print ![Serial_Number]
            Debug.Print ![Serial_Number]
            End With
   
    rst.Close
    dbs.Close
   
End Sub

Commented:
Public Sub PrintSerialLabels()

    Dim rst As DAO.Recordset
    Dim dbs As Database
    Dim strSQL As String
   
        strSQL = "Select * from Label_Info;"
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset(strSQL)

        do while not rst.eof ' Add this line
            With rst
            Debug.Print ![Serial_Number]
            Debug.Print ![Serial_Number]
            End With
            rst.movenext ' this one
         loop ' and this one
    rst.Close
    dbs.Close
End Sub

Commented:
I wish I knew more about the label software, I could probably be of more help

Author

Commented:
Hi. In testing the code above, it comes back with two null records at the end. ??

The data source for the serial number labels is now pointing to another table. With just one record in the table, it will print one of the first label, one of the second, and empty the table for me.

Do you think that there is a way that I can:

Copy one record to another table (a twin of the structure)
     print label 1
     print label 2
     The label software will delete the record printed.

Repeat until EOF
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.