printing label at specific position

Question

I have 11 inch X 8.5 inch paper to print labels. The paper is divided into two columns i.e 4.25 width of each column containing 11 labels in each.

So, Size of each label is 1 inch X 4.25 inch.

Now my Question is: I'm using Northwind DB in MS Access 2010, in that considering the table dbo_Products, I want to print Product ID and Product Name on each label.

I could join the label report to form but i couldn't get output.

As said earlier The sheet contains two columns, if user wants to print label on specific label position it should be able to print on that.

(Ex. User wants to print Product ID: 10 on the position 5, the corresponding product info must be print on the label located on the 5th position of the page. (the positioning of labels on the page is shown below)

1     |     2 

3     |     4

5     |     6

7     |     8

...... till 22 

It would be great if anyone could help me out in this issue by showing the connectivity between the form to label and print it in specific label place.

Thank you

Answer 1

Start with the instructions found at http://www.techrepublic.com/blog/how-do-i/how-do-i-start-an-access-label-report-with-any-label-on-the-sheet/

Next I modified that to have three textboxes instead of one. They are named 'txtStart', 'txtEnd', 'txtLabelPos'. Use the code below for that form.

Note the 'WHERE' clause in the SQL... change the tables / field names to suit your own needs.

Option Compare Database
Option Explicit


Private Sub cmdCancel_Click()
    'Reset and take no further action.
    Me!txtStart.Value = 1
End Sub



Private Sub cmdPrint_Click()
'Pass table with label data, position for first label, and label report.

Dim bytPosition As Variant
Dim bytCounter As Byte
Dim rst As New ADODB.Recordset

If IsNull(Me.txtStart) Or Me.txtStart = "" Then
    MsgBox "You must enter a starting range for the data.", vbOKOnly + vbCritical, "Missing Start Range"
    Exit Sub
End If

If IsNull(Me.txtEnd) Or Me.txtEnd = "" Then
    MsgBox "You must enter an ending range for the data.", vbOKOnly + vbCritical, "Missing End Range"
    Exit Sub
End If

If IsNull(Me.txtLabelPos) Or Me.txtLabelPos = "" Or Not IsNumeric(Me.txtLabelPos) Then
    MsgBox "You must enter the starting label position to print on.", vbOKOnly + vbCritical, "Missing Label Position"
    Exit Sub
End If

Set rst.ActiveConnection = CurrentProject.Connection
rst.Open "SELECT * FROM tblCustomerLabels" _
        , , adOpenDynamic, adLockOptimistic

'Delete previous label data.
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM tblCustomerLabels"

'Add one empty record for each missing label.
bytPosition = Nz(Me!txtLabelPos.Value, 0)

For bytCounter = 2 To bytPosition
    rst.AddNew
    rst.Update
Next

'Update label data.
Dim strSQL  As String
strSQL = "INSERT INTO tblCustomerLabels ( Company, [Last Name], [First Name], Address, City, [State/Province], [ZIP/Postal Code], [Country/Region] ) " & _
            "SELECT Customers.Company, Customers.[Last Name], Customers.[First Name], Customers.Address, Customers.City, Customers.[State/Province], Customers.[ZIP/Postal Code], Customers.[Country/Region] " & _
            "FROM Customers " & _
            "Where [Last Name] >= '" & Me.txtStart & "' AND [Last Name] <= '" & Me.txtEnd & "';"
DoCmd.RunSQL strSQL

'Open label report.
DoCmd.SetWarnings True
DoCmd.OpenReport "rptCustomerLabels", acViewPreview

rst.Close
Set rst = Nothing

Exit Sub

errHandler:
MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"
rst.Close
Set rst = Nothing
DoCmd.SetWarnings True

End Sub