Multiple reports from one record?

[carlos]

Hi,

I have created a report (template) for our box folder
labelling. We have thousands of box folders to be
labelled.

The report is bound to a table which consists of three
fields: Subject, FromVolume and ToVolume. Example: If one
Subject has already 10 volumes, FromVolume = 1 and
ToVolume = 10.

Is there an easy way to print 10 templates of that subject
without duplicating the records in the table?

I hope you don't get confused.

Any suggestion will be much appreciated. Thanks in advance.

Carlos

 

[Allen Browne]

You can to print 10 copies of the report, numbered Volume 1 to Volume 10,
when the FromVolume field is 1 and the ToVolume field is 10?

Base the report on a query. In the query you will have your main table, and
also a table of counter values. If there is NO join between the two tables,
you end up with a record for every number in the counter table. Now limit
that counter field, by entering this criteria:
Between FromVolume And ToVolume
and the report's query contains a record for every number between the 2
values (both inclusive).

For more detail on setting this up, see the example in this article:
Printing a Quantity of a Label
at:
http://members.iinet.net.au/~allenbrowne/ser-39.html

 

[carlos]

Dear Allen,

You're a genius. Thanks a lot.

Carlos

 

[carlos]

Dear Allen,

Your article is excellent. Indeed I have printed a lot of
labels in just one go.

Now I’m creating a list box in a form (Multi Select:
Extended) where I can print only the selected records from
the query that you have advised. How can I limit my report
base on the selected items in the list box?

Again thanks in advance.

Carlos

 

[Allen Browne]

Loop through the ItemsSelected collection of the list box, generating a
string to use with the IN operator in the WHERE clause of your SQL
statement.

This example builds up a string, and uses it in the WhereCondition of
OpenReport:

Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.

strDelim = """" 'Delimiter appropriate to field type.
With Me.MyLisbox
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) & _
strDelim & ","
End If
Next
End With
lngLen = Len(strWhere) - 1 'Without trailing comma.
If lngLen > 0 Then
strWhere = "[MyField] IN (" & Left$(strWhere, lngLen) & ")"
End If
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere


Note: If the bound column of the list box refers to a Number type field (not
a Text type field), omit the line:
strDelim = """"

 

[Carlos]

Allen,
Huh! this is terrific. How I wish to send you a whole pan
of pizza to say thank you.

Best regards,


Carlos

-----Original Message-----
Loop through the ItemsSelected collection of the list box, generating a
string to use with the IN operator in the WHERE clause of your SQL
statement.

This example builds up a string, and uses it in the WhereCondition of
OpenReport:

Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.

strDelim = """" 'Delimiter appropriate to field type.
With Me.MyLisbox
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData (varItem) & _
strDelim & ","
End If
Next
End With
lngLen = Len(strWhere) - 1 'Without trailing comma.
If lngLen > 0 Then
strWhere = "[MyField] IN (" & Left$(strWhere, lngLen) & ")"
End If
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere


Note: If the bound column of the list box refers to a Number type field (not
a Text type field), omit the line:
strDelim = """"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dear Allen,

Your article is excellent. Indeed I have printed a lot of
labels in just one go.

Now I’m creating a list box in a form (Multi Select:
Extended) where I can print only the selected records from
the query that you have advised. How can I limit my report
base on the selected items in the list box?

Again thanks in advance.

Carlos

.