Label run.

[Steve_in_Kent]

I need to print some labels, the data is in a table.
As i loop through the table, i need to check a value in the table to determine if to print it or not. (its a yes/no field)

The labels, are stored as a report. and there are 3 fields on the report.

something like
DoCmd.RunSQL "INSERT INTO tblCustomerLabels " & "SELECT * " & "FROM Customers" ??

[HansV]

Create a query based on the table that selects only the records where the Yes/No field is True.
Use this query as record source for the label report.
Or, if you want to be able to run the report with all records too, leave its record source set to the table, and open the report from a command button on a form:

Code: Select all

Private Sub cmdReport_Click()
    On Error GoTo ErrHandler
    DoCmd.OpenReport "rptLabelReport", acViewPreview, , "[YesNoField]=True"
    Exit Sub
ErrHandler:
    If Err <> 2501 Then
        MsgBox Err.Description, vbExclamation
    End If
End Sub

Substitute the correct names.
If you want to print directly, change acViewPreview to acViewNormal.

[Steve_in_Kent]

Thanks Hans.. will give it a crack!

[Steve_in_Kent]

Its currently only printing the first label.

also.. is there a way to enforce a particular printer? it IS printing to the label printer.. or, is that set up as part of the design of the report?

for the other printing that i have going on , for different labels.. i'm using

Code: Select all

DoCmd.OpenReport "Main_Label", acViewPreview, , , acHidden
  Set Application.Printer = Application.Printers(ChosenPrinter)
  DoCmd.SelectObject acReport, "Main_Label"
  DoCmd.PrintOut acSelection
  DoCmd.Close acReport, "Main_Label"

[HansV]

If possible, set the printer to be used in the design of the report (Page Setup tab of the ribbon > Page Setup button > Page tab of the dialogue).

How are you opening the report now?

[Steve_in_Kent]

I put this text in to print the labels.

' -----------------------------------------------------------Print the complete list of labels.
'If PrintAuto = True And flag = 1 Then
' DoCmd.OpenReport "WO_Label", acViewNormal
'End If

And, its just printing the first label only at the moment.

[Steve_in_Kent]

I put this text in to print the labels.

' -----------------------------------------------------------Print the complete list of labels.
'If PrintAuto = True And flag = 1 Then
' DoCmd.OpenReport "WO_Label", acViewNormal
'End If

And, its just printing the first label only at the moment.

[HansV]

What happens if you change acViewNormal to acViewPreview? How many records do you see?

[Steve_in_Kent]

Just the 1 still hans

[Steve_in_Kent]

Although the query does show 3

[HansV]

1) Have you set the query as Record Source of the report?
2) Does the report have something set in the Filter property, and Yes in the Filter on Load property? If so, clear the Filter Property and set Filter on Load to No.
3) Does the report have an On Load or On Open event procedure?

[Steve_in_Kent]

1) Have you set the query as Record Source of the report?

Yes


2) Does the report have something set in the Filter property, and Yes in the Filter on Load property? If so, clear the Filter Property and set Filter on Load to No.
Filter (blank)
Filter on Load No

3) Does the report have an On Load or On Open event procedure?
on open only
as below.. commented out atm

Private Sub Report_Open(Cancel As Integer)
'Me.Printer.LeftMargin = 0
'Me.Printer.RightMargin = 0
'Me.Printer.BottomMargin = 0
'Me.Printer.TopMargin = 0
End Sub

[HansV]

We're not getting any closer to a solution. Could you create a stripped down copy of the database, zip it and attach the zip file to a reply?

[Steve_in_Kent]

Yea, will do tomorrow hans. thanks.

at the moment, i have two other problems..

Suddenly its changed the label size from 38mm (wide label), to 38 x 90mm.. and it keeps changing it back for some strange reason.. so it started printing a huge label.

its throwing an error on
objTextStream.Close

that it never did before..

[HansV]

I'd have to see the database to comment on those problems too...

[Steve_in_Kent]

Zipped copy

[HansV]

I understand that you don't want to share sensitive information, but without any records at all in the OptionData table it's impossible to test anything.

[HansV]

If the text file is not found, objTextStream will not be initialized, so it can't be closed either. You could use

Code: Select all

    If Not objTextStream Is Nothing Then
        objTextStream.Close
    End If

or

Code: Select all

    On Error Resume Next
    objTextStream.Close

[HansV]

I notice that all controls in the report(s) are in the page header. They should be in the Detail section.

[Steve_in_Kent]

This is the text file.