Solved: help with labels in access 2003

[RonNCmale]

I need vba code to be able to print a single label and also to print on a label sheet that has missing labels. In other words to be able to move the label to a particular label on the label sheet.

I've found two VBA codes. one will print only the current label:
Private Sub cmdPrintLabel_Click()

'Send STGO data to label report

Dim str As String

On Error GoTo ErrHandler

If IsNull(Me!UnitNumber) Then


Exit Sub

End If

str = "UnitNumber = '" & Me!UnitNumber & "'"

Debug.Print str

'Open report in Print Preview.

DoCmd.OpenReport "rptSTGOLabels", acViewPreview, , str

Exit Sub

ErrHandler:

MsgBox Err.Number & ": " _



End Sub

the other code moves the labels the number entered to a unused area on the label sheet.
Private Sub cmdPrint_Click()
Dim rst As New ADODB.Recordset
Dim bytCounter As Byte
Dim bytBlanks As Byte

On Error GoTo ErrHandler
bytBlanks = Forms!frmLabels!txtBlanks.Value
On Error GoTo 0

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM tblSTGOLabels"

Set rst.ActiveConnection = CurrentProject.Connection
rst.Open "SELECT * FROM tblSTGOLabels", , _
adOpenDynamic, adLockOptimistic
For bytCounter = 2 To bytBlanks
rst.AddNew
rst.Update
Next
rst.Close
Set rst = Nothing

DoCmd.RunSQL "INSERT INTO tblSTGOLabels " _
& "SELECT * FROM STGO"
DoCmd.SetWarnings True
DoCmd.OpenReport "rptSTGOLabels", acViewPreview
Exit Sub

ErrHandler:
MsgBox "Please enter a valid label number"
DoCmd.SetWarnings True

End Sub

If possible I need to combine these codes to be able to print a single label to a unused label on the label sheet. Any help will be appreaciated.

[Carl A]

Here is a Knowledge base site.
http://support.microsoft.com/kb/299024/

[RonNCmale]

I've seen this, but this does not print only one label, this code will print on unused labels though. I have the two separate codes, that need to be merged somehow. I'm not that familiar with vba code, but i'm learning quickly. Thanks for your reply.

[Carl A]

The code on the Microsoft site will print just one label. You can set it up as a parameter query or by referencing a forms text, list or combobox as in the example database.

HTH

[RonNCmale]

Thanks for your reply, i'll give it a try.

[RonNCmale]

Thanks for your help Carl, gotta another problem now. The preview is showing the labels skipped, but when printing, it prints from the beginning of the label sheet. I need your help again.
Thanks in advance.

[Carl A]

In your Print Setup make sure these options are
the default.

Print Options Tab
Top: 0.5
Bottom 0.5
Left 0.3
Right 0.3

Columns Tab

Number of Columns 3
Row Spacing 0"
Column Spacing 0.4125

Columns Size

width 2.3375" Height 1"
Same as Detail checked

Column Layout
select
Across then Down

[RonNCmale]

Set the defaults as you stated. still prints from the beginning instead of from how the preview looks. The attachment that you sent does the same thing. Did it print for you correctly.

[Carl A]

You are right. It won't even export without losing its formatting. I went to Microsoft and downloaded the Report Sample Database and guess what. Theirs don't work either. Here is the Microsoft site.

http://tinyurl.com/2qrpjg

[Carl A]

I found some code on msdn and modified it to work in the example. It loads blank records to a temp table then appends the record you select.

Original Code is here
http://msdn2.microsoft.com/en-us/library/aa140106.aspx

[RonNCmale]

Well thanks again Carl, this code actually works, but I didn't see any additional code to print duplicates. Guess, I'll stick with this until I can do better. Again thanks for your help and time.

[Carl A]

Sorry about the option for the multiple copies. See if this is more to your liking.
:motz2:
<rant>At present I'm using Office 2007 and I have just about lost my patience with the garbage Microsoft has once again imposed upon us. </end rant>

[RonNCmale]

Oh yea, that's the ticket. Thanks for everything. Your the man!!!! Now I've got something going due to your patience and expertise. Thanks again

[Trevor]

check out the northwinds DB , it is a sample db in access, just search the help files of access for it or google northwinds database, it has I believe what your looking for, its in either forms or reptorts print lables or print mailing lables