Label Printing 1

[Appollo14]

Hi, I've created a project that runs a query based on a list box to get a list of customers. I then use the results of the query to print a label report. This works fine if i want to print a single label per customer. However what i would really like to do is give the user the option to print a full page of labels per selected customer.

Any ideas about the best way to go on this? I did think that a DO.... WHILE... LOOP... statement might be the way to go but that does not give the desired results.

All help appreciated,
Noel.

 

[PaulBricker]

Noel, this is an FAQ written by CosmoKramer.
faq703-3276

You may be able to use it to accomplish what you are trying to do. Read it over and then post back with specific questions.

Paul

 

[Appollo14]

Thanks Paul,

Read it a couple of times to get my head around how it might work and i think that it might do the trick. I'm going to give it a try tonight or over the weekend and i'll let you know how i get on.
Thanks again.
Noel.

 

[Appollo14]

Hi Paul,

Just a note to let you know that the solution that you gave worked a treat. I just hope the end user doesnt want anymore than 200 copies cos i got bored of adding lines to the number of copies table he he....

the only other thing that i have to do now is generate a unique number for each label that i have produced... any thoughts??

many thanks,
Noel.

 

[PaulBricker]

Noel, when you say unique number do you mean

LabelA-1
LabelA-2
LabelA-3
LabelB-1
LabelB-2
etc. or

LabelA-1
LabelA-2
LabelA-3
LabelB-4
LabelB-5

Paul

 

[PaulBricker]

Sorry, can you also post the code you are using to generate multiple labels so I can see that as well.

Paul

 

[Appollo14]

Hi Paul,
Thanks for the quick reply, the kind of numbering i'm after is as in your first example. Each label needs to generate a unique number that will be used for future reference when the label comes back to the source.

Noel.

 

[CosmoKramer]

Noel,

Depending on what you mean by unique number, one of these earlier threads might be able to help:

thread703-371336

thread703-521979

Hoc nomen meum verum non est.

 

[Appollo14]

Soory Paul, it's been a long day...

I'm actually after the second example that you have given not the first..

I've had a look at the threads that you have suggested and they have covered a problem that i was having earlier that i got around using a do...while...loop statement (i'll post that just incase ppl find it of some help).

I'm going to go home and get my project running then repost with more detail and an example of the code that i'm currently using.

Thanks for your perseverance and help.
Noel.

 

[PaulBricker]

Sounds good Noel.
Cosmo, stick around. I don't have much to do with labels around here so normally I look to see what you've posted to help answer questions.

Paul

 

[Appollo14]

Hi Paul,

Here is the code that I'm using and the sql query that i'm using to get the data. As suggested I added the "LabelCopies" table to the query
and included the copies requested clause to the query. This works well for my needs. However, what i'd like to have is a unique documet number for
each label that prints. The number does not need to be stored anywhere, just printed out on the label.

Private Sub cmdPrintPreview_Click()

Dim vItm As Variant
Dim stWhat As String
Dim stCriteria As String
Dim stSQL As String
Dim loqd As QueryDef

stWhat = "": stCriteria = ","
For Each vItm In Me!lstSaless.ItemsSelected
stWhat = stWhat & Me!lstSales.ItemData(vItm)
stWhat = stWhat & stCriteria
Next vItm
Me!txtCriteria = CStr(Left$(stWhat, Len(stWhat) - Len(stCriteria)))
Set loqd = CurrentDb.QueryDefs("qrySelect&quot
stSQL = "SELECT * "
stSQL = stSQL & "FROM qrySelect, LabelCopies WHERE dwaccountid"
stSQL = stSQL & &quot; IN (&quot; & Me!txtCriteria & &quotand copiesrequested <= Copies&quot;
loqd.SQL = stSQL
loqd.Close
DoCmd.OpenReport &quot;rptSalesLabel&quot;, acViewPreview
'Me.Form.Visible = False

End Sub


Here is the SQL query I'm using

SELECT *
FROM qrySales, LabelCopies
WHERE dwaccountid IN (7,9,13)and copiesrequested <= Copies;


As a by the by i read the posts that you pointed me to and, if i'm understanding them right, i think i came accross that problem on another project.
Just to recap, the poster wanted to print a label for a record based on the quantity of items on the record. Heres how i handled the problem.


Private Sub cmdPrintLabel_Click()
On Error GoTo Err_cmdPrintLabel_Click
Dim stdocname As String
Dim Check, Counter
Check = True: Counter = 0 ' Initialize variables.
Dim Msg, Style, Title, Help, Ctxt, Response, MyString

Msg = &quot;Print a label for each item?&quot;
Style = vbYesNo + vbWarning + vbDefaultButton1 + vbSystemModal
Title = &quot;Print all labels?&quot;
Help = &quot;DEMO.HLP&quot;
Ctxt = 1000
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then
' this bit adds collection details to the job
szAccImportCode = CompanyId.Column(1)
AccCodeTxt = CompanyId.Column(1)
szAccName = CompanyId.Column(2)
szColAddrName = CompanyId.Column(2)
szColAddr1 = CompanyId.Column(3)
szColAddr2 = CompanyId.Column(4)
szColAddr3 = CompanyId.Column(5)
szColAddr4 = CompanyId.Column(6)
szColAddr5 = CompanyId.Column(7)
szColPostCode = CompanyId.Column(8)
szColTelephone = CompanyId.Column(9)
szCustomerPOD = NextNumberId.Column(1)

' this bit saves the record
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
'this bit prints the labels and loops around until the counter equals the qty.
If dwrQuantity > 0 Then
Do ' Outer loop.
Do While Counter < dwrQuantity ' Inner loop.
Counter = Counter + 1 ' Increment Counter.by 1
stdocname = &quot;rptDespatchLabel&quot;
' NOTE ** This does not work with print preview, you must send straight to printer.
DoCmd.OpenReport stdocname, acNormal
If Counter = dwrQuantity Then ' If condition is True.
Check = False ' Set value of flag to False.
Exit Do ' Exit inner loop.
End If
Loop
Loop Until Check = False ' Exit outer loop immediately
Else: End If
'this disables the exit option until save is pressed
Me!cmdExit.Enabled = False
Me!DeleteEntry.Enabled = True

Else
End If

Exit_cmdPrintLabel_Click:
Exit Sub

Err_cmdPrintLabel_Click:
MsgBox Err.Description
Resume Exit_cmdPrintLabel_Click


Hope I got the jist of the post right and am able to help someone with this in the future. As for my problem I hope that you can help.

Regards,
Noel.

 

[Appollo14]

Sorry, told you it had been a long day. Forgot to pass thanks to Cosmo for the links
Anyway at home, fed and watered, beer in hand, ready for owt now!!

Noel

 

[Appollo14]

Mmmm... had some thoughts of me own on the current challenge. I was wondering if i could get away with creating a 2 column table (autonumber, nextnumber) then creating an update query ( UPDATE NextNumber SET NextNumber.NextNumber = [NextNumber]![NextNumber]+1
WHERE ((([NextNumber]![NumberId])=1)); ) and a macro that runs the query. So ive done that and put the macro in the ONPAGE event of my report. This sort of works! The Nextnumber is generated for every page, and the FINAL number is displayed on each label. Unfortunately I need it to generate for every record and display each number as it is being incremented. Therefore i think i have two problems - hopefully minor!! How do i get the macro to run on each record and how do i get each label to show the incremented number in turn.

Is it likely to be able to work this way or am i way off the mark?

Regards,
Noel.
PS in case you havent guessed i'm not a programmer by trade so please excuse any stupid questions.

 

[PaulBricker]

I had thought of trying to get an AutoNumber field linked with this. I've been caught up with some other stuff and am just getting back to this post. I haven't had time to read it all but it does seem that adding some kind of AutoIncrement value to the result could be concatenated to the label. I'm off for a while to a Dedication ceremony at out Athletics Facility but will try and catch up with this before the evening out.

Paul

 

[Appollo14]

Hi all,

As i dont need to store the number I'm gonna opt to use a date+time+record count string. I know this isn't that elegant but i think the chancs of it being duplicated on the reports are pretty slim. However, if anyone has any ideas how i can get my macro to increment on record and display on record i'd be overjoyed to hear them.

Thanks for the time and effort you've put in reading and responding to this post.

Regards,
Noel.

 

[PaulBricker]

Sorry Noel, I don't do labels here so usually I just pass on suggestions that I've seen other people make. It sounds like you have the situation under control. I don't think with the date time count you will have much chance of duplicates.

Paul

 

[Appollo14]

No apologies required Paul, you & Cosmo helped out & solved my initial problem and helped a great deal with is one. I tend to work to the old KISS adage (Keep It Simple Stupid!) and i think that the answer i was chasing was way more complicated than the one i actually needed.

Once again Many thanks,
Noel.

PS.. Bewarned, my boss has given me a few projects so i may require you expertise in the future ;-)