Using Access DB for mailing labels

  • Thread starter Thread starter Nathan Vanderslice
  • Start date Start date
N

Nathan Vanderslice

I am doing work with a Youth mission program and have set up a data base for
our "stockholders" (contributors) and need to be able to set up mailing
labels for each record. I have pretty much figured out how to do that but
the number of labels I need for each person in the the DB will vary from 1 -
4. Is there a way to set up a field in the DB that by entering a given
value, will result in that number of labels printed without having to
duplicate the record or copy and edit multiple reports to acheive this? If
so, How do I do it? I could not find anything in Access help.
 
I checked it out but it looks like it will only work to specify the number
of labels needed for all of the records and I can do it easier but just
running the report multiple times. If it will work to specify the number of
labels for each record, I don't understand it. Can you or someone else
perhaps refer me to someone who could walk me to through the process or even
edit a database
that I could E-mail to them?
 
Ok, let's try the walk through first.

Place a hidden text box (Visible = No) called txtCopyCount in the detail section and bind
it to the field that holds the desired copy count. In the Detail section's OnPrint event
call

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
LabelLayout Me.Name, Nz(Me.txtCopyCount, 1)
End Sub


In a module, create a Public Sub called LabelLayout

Option Compare Database
Option Explicit
Public intCopyCount As Integer

Public Sub LabelLayout(ReportName As String, intLabelCopies As Integer)
On Error GoTo CheckError
Dim rpt As Report, msg As String
Set rpt = Reports(ReportName)
If intCopyCount < intLabelCopies - 1 Then
'intCopyCount is Zero based since integers
'initialize as zero, so subtract 1 from the
'desired copy count
rpt.NextRecord = False
intCopyCount = intCopyCount + 1
Else
rpt.NextRecord = True
intCopyCount = 0
End If

CleanUp:
Set rpt = Nothing
Exit Sub

CheckError:
msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox msg, vbOKOnly + vbExclamation, "Error", Err.HelpFile, Err.HelpContext
Resume CleanUp
End Sub
 
Well, thankyou for the help! It looks a little complicated but I'm sure that
once I put the info together with Access and try it, I should be able to
figure it out. It will probably be a while before I can get to working on
this but I will get back to you to let you know how I did.
 
Well Wayne, I tried to work with it and have already gotten stumped in the
first few steps. I'm finding a little confusion in the instruction following
the creating of the hidden box and binding it to the field that holds the
data, but in the next line you have "In the Detail's OnPrint event call"
When going to the rpoperties list for the box, I could not find any "OnPrint
event". Did you mean to say, In the details section create an on print event
called "Private Sub Detail_Print"? I haven't tried the rest but I did find
out how to create a module and I think the rest is pretty straight forward.
If I am still off track, maybe you could create a "dummy" DB that I could
use as an example to create my own. For this I would probably just need,
say, a name field, shares field, and the hidden text box. If this is
possible, I am using Access 2000.
 
Here's a possibly simpler solution.

Create a small table, Num, with only one Long Integer field N.
Populate this table with values of N from 1 to the most copies you'll
ever want. (I find this table useful quite often and usually put in
10,000 rows - it's cheap).

Add the Num table to the Query upon which your mailing label report is
based. Don't use any join line. Instead, put a criterion on N of

<= [howmany]

where [howmany] is the field indicating how many of each label to
print.

You'll get that many copies of the label; different numbers for
different records (including zero!) is automatic. No code, nothing
special on the report; and you can even include N on the label if you
want to have it say "label N of HOWMANY".
 
John, Thankyou for your idea and it does sound much easier. I think I've got
it but just a couple of things to ask. IAm I correct that it works best to
set it up in design view (this is the only way I could find the ability to
enter a criterion)? Second, where you say "<=[howmany]"is "how many" the
field in the mailing list table that I want to put in, and do I use the
brackets?
John Vinson said:
number
of or
even
Click to expand...

Here's a possibly simpler solution.

Create a small table, Num, with only one Long Integer field N.
Populate this table with values of N from 1 to the most copies you'll
ever want. (I find this table useful quite often and usually put in
10,000 rows - it's cheap).

Add the Num table to the Query upon which your mailing label report is
based. Don't use any join line. Instead, put a criterion on N of

<= [howmany]

where [howmany] is the field indicating how many of each label to
print.

You'll get that many copies of the label; different numbers for
different records (including zero!) is automatic. No code, nothing
special on the report; and you can even include N on the label if you
want to have it say "label N of HOWMANY".
Click to expand...
 
Yeah, it's me again. Is there a way to limit the number of labels printed
with this method? Although the number of shares may be more than 4, we limit
the number of cards sent, labels needed, to 4
John Vinson said:
number
of or
even
Click to expand...

Here's a possibly simpler solution.

Create a small table, Num, with only one Long Integer field N.
Populate this table with values of N from 1 to the most copies you'll
ever want. (I find this table useful quite often and usually put in
10,000 rows - it's cheap).

Add the Num table to the Query upon which your mailing label report is
based. Don't use any join line. Instead, put a criterion on N of

<= [howmany]

where [howmany] is the field indicating how many of each label to
print.

You'll get that many copies of the label; different numbers for
different records (including zero!) is automatic. No code, nothing
special on the report; and you can even include N on the label if you
want to have it say "label N of HOWMANY".
Click to expand...
 
John, Thankyou for your idea and it does sound much easier. I think I've got
it but just a couple of things to ask. IAm I correct that it works best to
set it up in design view (this is the only way I could find the ability to
enter a criterion)?
Click to expand...

Either in query design view, or (if you're comfortable with SQL view)
in SQL view.
Second, where you say "<=[howmany]"is "how many" the
field in the mailing list table that I want to put in, and do I use the
brackets?
Click to expand...

Exactly. Brackets are required if the fieldname contains blanks or
other special characters, and brackets are *always* allowed; I
habitually include them just for readability (if it's got brackets
it's either a fieldname or a parameter).
 
Yeah, it's me again. Is there a way to limit the number of labels printed
with this method? Although the number of shares may be more than 4, we limit
the number of cards sent, labels needed, to 4
Click to expand...

Sure; just don't put a number higher than 4 in the "howmany" field.
You can prevent doing so by using a field validation rule of

<= 4
 
So far, so good, but how does this control the number of labels actually
printed? Is this field in the NUM table which the criterion statement what
does it, or am I still missing a step in either one of the tables or the
report?. The other thing is that I want to be able to indicate the actual
number of stock "purchased" in reports generated that are grouped by
volunteer so that they can each have this information in subsequent years as
they raise their funds.
 
Me again, I'm starting to feel like just creating the basic DB that I need
and then see if I can get someone else to program the functions that I need.
However, I have a little time yet so I will probably put this aside for a
little bit.
 
So far, so good, but how does this control the number of labels actually
printed? Is this field in the NUM table which the criterion statement what
does it, or am I still missing a step in either one of the tables or the
report?.
Click to expand...

Yes. You are creating a "Cartesian join" query, joining every record
in your labels report query with every record in the NUM table. If
there are 100 records in NUM, then every label will be repeated 100
times, with N values of 1, 2, 3, 4, ... , 100.

The criterion of <= [HowMany] on N compares the value of N in each of
these 100 composite records with the value of HowMany in your query.
Let's say you have HowMany set to 4 for a particular label. This will
accept the records with N equal to 1, or to 2, or to 3, or to 4, and
discard the other 96 copies. You'll therefore get four records printed
out.
The other thing is that I want to be able to indicate the actual
number of stock "purchased" in reports generated that are grouped by
volunteer so that they can each have this information in subsequent years as
they raise their funds.
Click to expand...

This appears to have absolutely nothing whatsoever to do with printing
a selected number of labels, and I really have no idea what you're
talking about! If you want to record the "actual number of stock
purchased" then you will have to record that information in a Table,
with fields for the volunteerID and the year.
 
John, thankyou for your time but I seem to be having even more trouble
understanding things so I am going to take a step or two back and see if any
of the publications available can help. I just don't want to take advantage
of your kindness. If I can't get anywhere with that, I'll try this board
again. Thanks again.
 
Back
Top