Print Directly to the COM Port

[alpnz]

I am trying to print directly to a Com port, to produce, labels using EPL code. The following code produces a simple Error box that reads Overflow.

 
Private Sub but_testdos_Click()
On Error GoTo Err_but_testdos_Click
    Dim t1 As String
    Dim t2 As String
    Dim t3 As Integer
 
 
    t1 = "3 Kgs Nett"
    t2 = "Sweetheart Cherries"
    t3 = "941352001234"
 '   Dim stAppName As String
 '
 '  stAppName = "c:\WINNT\system32\cmd /c copy f:\manager\testelt.txt COM2"
 '  Call Shell(stAppName, 1)
Open "COM2:" For Output As #1
    Print #1, "N"
    Print #1, "S4"
    Print #1, "ZT"
    Print #1, "A505,2,0,4,1,1,N," & "t1"
    Print #1, "A100,135,0,2,2,2,N," & "t2"
    Print #1, "B100,240,1,E30,2,3,50,B," & "t3"
    Print #1, "P3"
Close "COM2 : "
 
 
Exit_but_testdos_Click:
    Exit Sub
 
Err_but_testdos_Click:
    MsgBox Err.Description, vbOKCancel
 
 
    Resume Exit_but_testdos_Click
 
End Sub
 
 

Anyone have some suggestions, as to what I am missing out in the code.

Any help most appreciated.

John S

[willakawill]

I am trying to print directly to a Com port, to produce, labels using EPL code. The following code produces a simple Error box that reads Overflow.

 
Private Sub but_testdos_Click()
On Error GoTo Err_but_testdos_Click
    Dim t1 As String
    Dim t2 As String
    Dim t3 As Integer
 
 
    t1 = "3 Kgs Nett"
    t2 = "Sweetheart Cherries"
    t3 = "941352001234"
 '   Dim stAppName As String
 '
 '  stAppName = "c:\WINNT\system32\cmd /c copy f:\manager\testelt.txt COM2"
 '  Call Shell(stAppName, 1)
Open "COM2:" For Output As #1
    Print #1, "N"
    Print #1, "S4"
    Print #1, "ZT"
    Print #1, "A505,2,0,4,1,1,N," & "t1"
    Print #1, "A100,135,0,2,2,2,N," & "t2"
    Print #1, "B100,240,1,E30,2,3,50,B," & "t3"
    Print #1, "P3"
Close "COM2 : "
 
 
Exit_but_testdos_Click:
    Exit Sub
 
Err_but_testdos_Click:
    MsgBox Err.Description, vbOKCancel
 
 
    Resume Exit_but_testdos_Click
 
End Sub
 
 

Anyone have some suggestions, as to what I am missing out in the code.

Any help most appreciated.

John S

Hi John,
Would you close com2 or close #1 ?

[missinglinq]

Although it's been a while since I worked at this level, I agree.

Close "COM2 : "

should simply be

Close # 1

[alpnz]

Although it's been a while since I worked at this level, I agree.

Close "COM2 : "

should simply be

Close # 1

Thanks for your replies,
I agree it should be Close #1, which I changed not long after starting this thread, however I still get an overflow error.
I also need to be able to concantenate, field values together to create the value instead of simple text like "Sweetheart cherries" Sweetheart and cherries being two different fields in tables related to the form this code is invoked from. BUT it needs to be done in the format

 
N
S4
D10
ZT
A505,2,0,4,1,1,N,"3 KGS"
A650,2,0,4,1,1,N,"1 OF 1"
A30,3,0,4,1,1,N,"Molynuts Fruit Corp"
A30,33,0,4,1,1,N,"Back Road"
A30,63,0,4,1,1,N,"Antartica"
A100,135,0,2,2,2,N,"Sweetheart Cherries"
A100,180,0,2,2,2,N,"34 MM"
A100,220,0,2,2,2,N,"3 Kg Presentation Pack"
A550,280,0,3,2,3,N,"TAG 1"
A550,333,0,3,2,3,N,"SIS6871-0"
b100,240,P,400,300,p100,260,30,f1,x3,y10,r60,l5,t0,o0,"3 Kg Nett when Packed. Export Quality."
P2
 
 

The above code produces a label in an Eltron printer, using the EPL command set. The huge advantage is that you pass a few bytes to the printer, and it does the rest, resulting in very very fast click to print speed. (Windows print spool to achieve the same upload of graphics and fonts, takes 20 secs, this takes 1 sec) I diverse, the point I am making is to create the Print #1 statement we are required to put the qoutes around the text, however part of the text contains quotes anyway, how do we do that again??

[willakawill]

MsgBox """" & "These are quotes" & """"

Of

Const Quote As String = """"
 
MsgBox Quote & "These are quotes" & Quote

[alpnz]

MsgBox """" & "These are quotes" & """"

Of

Const Quote As String = """"
 
MsgBox Quote & "These are quotes" & Quote

OKaaaay ...
Can you give me an example of a line above using this idea.?

[alpnz]

OKaaaay ...
Can you give me an example of a line above using this idea.?

Here is where I am at so far ... two buttons, one creates a file the other prints the label. Eventually I will Dim Vars for the text in the test label.

 
Private Sub createLab_Click()
Dim db As Database
Dim rst As Recordset
Const Quote As String = """"
 
  Set db = CurrentDb
  Set rst = db.OpenRecordset("qry_jlabel")
 
    Open "testlabel.txt" For Output As #1
        Print #1, "N"
        Print #1, "S4"
        Print #1, "D10"
        Print #1, "ZT"
        Print #1, "A450, 2, 0, 4, 1, 1, N,TD"
        Print #1, "A650, 2, 0, 4, 1, 1, N,TD"
        Print #1, A30, 3, 0, 4, 1, 1, N, Quote & "Smith Family" & Qoute; ""
        Print #1, A30, 33, 0, 4, 1, 1, N, Quote & "Sunderland St" & Quote
        Print #1, A30, 63, 0, 4, 1, 1, N, Quote & "Clyde 9330" & Quote; ""
        Print #1, A100, 135, 0, 2, 2, 2, N, Quote & "Sweet Valentine Sauce" & Quote; ""
        Print #1, A100, 180, 0, 2, 2, 2, N, Quote & "Med Bite" & Quote; ""
        Print #1, A100, 220, 0, 2, 2, 2, N, Quote & "9th January 2007" & Quote; ""
        Print #1, b100, 240, P, 400, 300, p100, 285, 30, f1, x3, y10, r60, l5, t0, o0, Quote & "Test Batch: Sweet Valentine Sauce - 09/01/2007" & Quote; ""
        Print #1, P3
    Close #1
 
End Sub
 
Private Sub printLab_Click()
Dim stAppName As String
 
   stAppName = "c:\WINNT\system32\cmd /c copy testlabel.txt COM2"
   Call Shell(stAppName, 1)
End Sub
 
 

[alpnz]

Here is where I am at so far ... two buttons, one creates a file the other prints the label. Eventually I will Dim Vars for the text in the test label.

 
Private Sub createLab_Click()
Dim db As Database
Dim rst As Recordset
Const Quote As String = """"
 
  Set db = CurrentDb
  Set rst = db.OpenRecordset("qry_jlabel")
 
    Open "testlabel.txt" For Output As #1
        Print #1, "N"
        Print #1, "S4"
        Print #1, "D10"
        Print #1, "ZT"
        Print #1, "A450, 2, 0, 4, 1, 1, N,TD"
        Print #1, "A650, 2, 0, 4, 1, 1, N,TD"
        Print #1, A30, 3, 0, 4, 1, 1, N, Quote & "Smith Family" & Qoute; ""
        Print #1, A30, 33, 0, 4, 1, 1, N, Quote & "Sunderland St" & Quote
        Print #1, A30, 63, 0, 4, 1, 1, N, Quote & "Clyde 9330" & Quote; ""
        Print #1, A100, 135, 0, 2, 2, 2, N, Quote & "Sweet Valentine Sauce" & Quote; ""
        Print #1, A100, 180, 0, 2, 2, 2, N, Quote & "Med Bite" & Quote; ""
        Print #1, A100, 220, 0, 2, 2, 2, N, Quote & "9th January 2007" & Quote; ""
        Print #1, b100, 240, P, 400, 300, p100, 285, 30, f1, x3, y10, r60, l5, t0, o0, Quote & "Test Batch: Sweet Valentine Sauce - 09/01/2007" & Quote; ""
        Print #1, P3
    Close #1
 
End Sub
 
Private Sub printLab_Click()
Dim stAppName As String
 
   stAppName = "c:\WINNT\system32\cmd /c copy testlabel.txt COM2"
   Call Shell(stAppName, 1)
End Sub
 
 

The resulting text file flies to bits at the & quote stage

N
S4
D10
ZT
A450, 2, 0, 4, 1, 1, N,TD
A650, 2, 0, 4, 1, 1, N,TD
               3             0             4             1             1                          "Smith Family
               33            0             4             1             1                          "Sunderland St"
               63            0             4             1             1                          "Clyde 9330"
               135           0             2             2             2                          "Sweet Valentine Sauce"
               180           0             2             2             2                          "Med Bite"
               220           0             2             2             2                          "9th January 2007"
               240                         400           300                         285           30                                                                                                             "Test Batch: Sweet Valentine Sauce - 09/01/2007"
 
 

Hope this is legible to you ??.

John S

[alpnz]

The resulting text file flies to bits at the & quote stage

N
S4
D10
ZT
A450, 2, 0, 4, 1, 1, N,TD
A650, 2, 0, 4, 1, 1, N,TD
               3             0             4             1             1                          "Smith Family
               33            0             4             1             1                          "Sunderland St"
               63            0             4             1             1                          "Clyde 9330"
               135           0             2             2             2                          "Sweet Valentine Sauce"
               180           0             2             2             2                          "Med Bite"
               220           0             2             2             2                          "9th January 2007"
               240                         400           300                         285           30                                                                                                             "Test Batch: Sweet Valentine Sauce - 09/01/2007"
 
 

Hope this is legible to you ??.

John S

OK,
Heres the next attempt. Note the mangled text with semi colons, perhaps I have missed something in your original reply.

 
Const Quote As String = """"
 
  Set db = CurrentDb
  Set rst = db.OpenRecordset("qry_jlabel")
 
    Open "testlabel.txt" For Output As #1
        Print #1, "N"
        Print #1, "S4"
        Print #1, "D10"
        Print #1, "ZT"
        Print #1, "A450, 2, 0, 4, 1, 1, N,TD"
        Print #1, "A650, 2, 0, 4, 1, 1, N,TD"
        Print #1, "A30, 3, 0, 4, 1, 1, N, Quote & "; Smith; Family; " & Quote"
        Print #1, "A30, 33, 0, 4, 1, 1, N, Quote & "; Sunderland; St; " & Quote"
        Print #1, "A30, 63, 0, 4, 1, 1, N, Quote & "; Clyde; 9330; " & Quote"
        Print #1, "A100, 135, 0, 2, 2, 2, N, Quote & "; Sweet; Valentine; Sauce; " & Quote"
        Print #1, "A100, 180, 0, 2, 2, 2, N, Quote & "; Med; Bite; " & Quote"
        Print #1, "A100, 220, 0, 2, 2, 2, N, Quote & "; 9; th; January; 2007; " & Quote"
        Print #1, "b100, 240, P, 400, 300, p100, 285, 30, f1, x3, y10, r60, l5, t0, o0, Quote & "; Test; Batch; Sweet; Valentine; " & Quote"
        Print #1, "P3"
    Close #1
 
 

And the resulting file

 
N
S4
D10
ZT
A450, 2, 0, 4, 1, 1, N,TD
A650, 2, 0, 4, 1, 1, N,TD
A30, 3, 0, 4, 1, 1, N, Quote &  & Quote
A30, 33, 0, 4, 1, 1, N, Quote &  & Quote
A30, 63, 0, 4, 1, 1, N, Quote &  9330  & Quote
A100, 135, 0, 2, 2, 2, N, Quote &  & Quote
A100, 180, 0, 2, 2, 2, N, Quote &  & Quote
A100, 220, 0, 2, 2, 2, N, Quote &  9  2007  & Quote
b100, 240, P, 400, 300, p100, 285, 30, f1, x3, y10, r60, l5, t0, o0, Quote &  & Quote
P3
 
 

As you can see getting really close to cracking it with your help.

Prettyeee Exciting Yogi ...
John S

[alpnz]

And the resulting file

 
N
S4
D10
ZT
A450, 2, 0, 4, 1, 1, N,TD
A650, 2, 0, 4, 1, 1, N,TD
A30, 3, 0, 4, 1, 1, N, Quote &  & Quote
A30, 33, 0, 4, 1, 1, N, Quote &  & Quote
A30, 63, 0, 4, 1, 1, N, Quote &  9330  & Quote
A100, 135, 0, 2, 2, 2, N, Quote &  & Quote
A100, 180, 0, 2, 2, 2, N, Quote &  & Quote
A100, 220, 0, 2, 2, 2, N, Quote &  9  2007  & Quote
b100, 240, P, 400, 300, p100, 285, 30, f1, x3, y10, r60, l5, t0, o0, Quote &  & Quote
P3
 
 

As you can see getting really close to cracking it with your help.

Prettyeee Exciting Yogi ...
John S[/quote]

I notice it has picked up on numbers in the text, perhaps I need to Dim the variables as strings, I.e. fields in the query, however there are occasions we want to print straight text, hence the trial with just test text.

Anyway ... been called away to a client ...

Later perhaps. :-)

John S

[alpnz]

An Update ...
This Code

 
Private Sub createLab_Click()
Dim db As Database
Dim rst As Recordset
Const LL As String = """"
 
  Set db = CurrentDb
  Set rst = db.OpenRecordset("qry_jlabel")
 
    Open "testlabel.txt" For Output As #1
        Print #1, "N"
        Print #1, "S4"
        Print #1, "D10"
        Print #1, "ZT"
        Print #1, "A650, 2, 0, 4, 1, 1, N, ""Test Label"""
        Print #1, "A30, 3, 0, 4, 1, 1, N, ""Smith Family"""
        Print #1, "A30, 33, 0, 4, 1, 1, N, ""Sunderland St"""
        Print #1, "A30, 63, 0, 4, 1, 1, N, ""Clyde 9330"""
        Print #1, "A100, 135, 0, 2, 2, 2, N,"" Sweet Valentine Sauce"""
        Print #1, "A100, 180, 0, 2, 2, 2, N,"" Mild Thai Flavour"""
        Print #1, "A100, 220, 0, 2, 2, 2, N, ""10th January 2007"""
        Print #1, "b100, 240, P, 400, 300, p100, 285, 30, f1, x3, y10, r60, l5, t0, o0, ""Sweet Valentine Medium Thai Flavour"""
        Print #1, "P3"
 
    Close #1
 
End Sub
 
Private Sub printLab_Click()
Dim stAppName As String
 
   stAppName = "c:\WINNT\system32\cmd /c copy testlabel.txt COM2"
   Call Shell(stAppName, 1)
End Sub
 
 

Produces the file testlabel.txt

 
N
S4
D10
ZT
A650, 2, 0, 4, 1, 1, N, "Test Label"
A30, 3, 0, 4, 1, 1, N, "Smith Family"
A30, 33, 0, 4, 1, 1, N, "Sunderland St"
A30, 63, 0, 4, 1, 1, N, "Clyde 9330"
A100, 135, 0, 2, 2, 2, N," Sweet Valentine Sauce"
A100, 180, 0, 2, 2, 2, N," Mild Thai Flavour"
A100, 220, 0, 2, 2, 2, N, "10th January 2007"
b100, 240, P, 400, 300, p100, 285, 30, f1, x3, y10, r60, l5, t0, o0, "Sweet Valentine Medium Thai Flavour"
P3
 
 

Which is a bog stock EPL file ... EXCEPT I suspect it lacks linefeeds at the end of each line, as all it produces is three blank labels.
If I create this file in a standard editor like gedit, I get 3 lovely little sauce bottle files .. (I know very esoteric).

Anyone with suggestions as to how to get the linefeed into the txt file.

John S

[alpnz]

The following code creates a M$ file called testlabel.txt. Well not exactly, previous version did. This version prints directly to COM2, and produces a blank label. I have tried chr(10) on the end of each print #1, statement, and get the same result. Here is the code, which incorporates EPL command set instructions to a thermal printer.

 
Private Sub createLab_Click()
 
    Open "COM2: " For Output As #1
        Print #1, "N"
        Print #1, "S4"
        Print #1, "D10"
        Print #1, "ZT"
        Print #1, "A650, 2, 0, 4, 1, 1, N, ""Test Label"""
        Print #1, "A30, 3, 0, 4, 1, 1, N, ""Sharkbites Sauce Co"""
        Print #1, "A30, 33, 0, 4, 1, 1, N, ""Manuka Gully Road"""
        Print #1, "A30, 63, 0, 4, 1, 1, N, ""Ophir"""
        Print #1, "A100, 135, 0, 2, 2, 2, N,"" Sweet Valentine Sauce"""
        Print #1, "A100, 180, 0, 2, 2, 2, N,"" Mild Thai Flavour"""
        Print #1, "A100, 220, 0, 2, 2, 2, N, ""10th January 2007"""
        Print #1, "b100, 240, P, 400, 300, p100, 285, 30, f1, x3, y10, r60, l5, t0, o0, ""Sweet Valentine Medium Thai Flavour"""
        Print #1, "P1"
 
    Close #1
 
End Sub
 
 

I can type this into a text file on another platform, SuSE Linux, and it prints lovely dovely. The file or this direct method from within Access does not.

What am I missing here.

Any help gratefully recieved.

[willakawill]

This code:

Private Sub createLab_Click()
 
    Dim stOut As String
    Const quote As String = """"
 
    stOut = "N" & vbCrLf & "S4" & vbCrLf & "D10" & vbCrLf & "ZT" & vbCrLf _
        & "A650, 2, 0, 4, 1, 1, N," & quote & "Test Label" & quote & vbCrLf _
        & "A30, 3, 0, 4, 1, 1, N," & quote & "Sharkbites Sauce Co" & quote & vbCrLf _
        & "A30, 33, 0, 4, 1, 1, N," & quote & "Manuka Gully Road" & quote & vbCrLf _
        & "A30, 63, 0, 4, 1, 1, N," & quote & "Ophir" & quote & vbCrLf _
        & "A100, 135, 0, 2, 2, 2, N," & quote & " Sweet Valentine Sauce" & quote & vbCrLf _
        & "A100, 180, 0, 2, 2, 2, N," & quote & " Mild Thai Flavour" & quote & vbCrLf _
        & "A100, 220, 0, 2, 2, 2, N," & quote & "10th January 2007" & quote & vbCrLf _
        & "b100, 240, P, 400, 300, p100, 285, 30, f1, x3, y10, r60, l5, t0, o0," _
        & quote & "Sweet Valentine Medium Thai Flavour" & quote & vbCrLf & "P1" & vbCrLf
 
    Open "c:\comout.txt" For Output As #1
        Print #1, stOut
    Close #1
 
End Sub
 

produces this output:

N
S4
D10
ZT
A650, 2, 0, 4, 1, 1, N,"Test Label"
A30, 3, 0, 4, 1, 1, N,"Sharkbites Sauce Co"
A30, 33, 0, 4, 1, 1, N,"Manuka Gully Road"
A30, 63, 0, 4, 1, 1, N,"Ophir"
A100, 135, 0, 2, 2, 2, N," Sweet Valentine Sauce"
A100, 180, 0, 2, 2, 2, N," Mild Thai Flavour"
A100, 220, 0, 2, 2, 2, N,"10th January 2007"
b100, 240, P, 400, 300, p100, 285, 30, f1, x3, y10, r60, l5, t0, o0,"Sweet Valentine Medium Thai Flavour"
P1

[alpnz]

This code:

Private Sub createLab_Click()
 
    Dim stOut As String
    Const quote As String = """"
 
    stOut = "N" & vbCrLf & "S4" & vbCrLf & "D10" & vbCrLf & "ZT" & vbCrLf _
        & "A650, 2, 0, 4, 1, 1, N," & quote & "Test Label" & quote & vbCrLf _
        & "A30, 3, 0, 4, 1, 1, N," & quote & "Sharkbites Sauce Co" & quote & vbCrLf _
        & "A30, 33, 0, 4, 1, 1, N," & quote & "Manuka Gully Road" & quote & vbCrLf _
        & "A30, 63, 0, 4, 1, 1, N," & quote & "Ophir" & quote & vbCrLf _
        & "A100, 135, 0, 2, 2, 2, N," & quote & " Sweet Valentine Sauce" & quote & vbCrLf _
        & "A100, 180, 0, 2, 2, 2, N," & quote & " Mild Thai Flavour" & quote & vbCrLf _
        & "A100, 220, 0, 2, 2, 2, N," & quote & "10th January 2007" & quote & vbCrLf _
        & "b100, 240, P, 400, 300, p100, 285, 30, f1, x3, y10, r60, l5, t0, o0," _
        & quote & "Sweet Valentine Medium Thai Flavour" & quote & vbCrLf & "P1" & vbCrLf
 
    Open "c:\comout.txt" For Output As #1
        Print #1, stOut
    Close #1
 
End Sub
 

produces this output:

N
S4
D10
ZT
A650, 2, 0, 4, 1, 1, N,"Test Label"
A30, 3, 0, 4, 1, 1, N,"Sharkbites Sauce Co"
A30, 33, 0, 4, 1, 1, N,"Manuka Gully Road"
A30, 63, 0, 4, 1, 1, N,"Ophir"
A100, 135, 0, 2, 2, 2, N," Sweet Valentine Sauce"
A100, 180, 0, 2, 2, 2, N," Mild Thai Flavour"
A100, 220, 0, 2, 2, 2, N,"10th January 2007"
b100, 240, P, 400, 300, p100, 285, 30, f1, x3, y10, r60, l5, t0, o0,"Sweet Valentine Medium Thai Flavour"
P1

Many many Thanks for the reply.
I wonder is it possible to explain the difference, in using a defined const as apposed to the direct way. Would including a vbCrLF _ in my original work?. The reason I ask, is that I wish to apply a good deal of logic to the inclusion of some of the lines, and having it laid out in the manner I have, makes it a bit easier to apply conditional IF's to each line. The other situation will be the case of variables, from the recordset. In the case of a variable, do I just quote & "&[var1]" & quote like that, OR as in my Case ""&var1""" vbCrLf _ OR would that be ""var1""vbCrLf _"

Once again many thanks.
John S

[alpnz]

An Update ...
I have tried both ways, and in fact when yours is in a larger window it follows a logical order as well, so not much difference.
Unfortunately the file created does not result in a label, compared with a straight text file. What I plan to do is some research on what is exactly different about the notepad files, and the files created from within Access. I sniff a M$ foible at work here.

Many thanks for your help so far ...

Cheers
j Smith

[willakawill]

An Update ...
I have tried both ways, and in fact when yours is in a larger window it follows a logical order as well, so not much difference.
Unfortunately the file created does not result in a label, compared with a straight text file. What I plan to do is some research on what is exactly different about the notepad files, and the files created from within Access. I sniff a M$ foible at work here.

Many thanks for your help so far ...

Cheers
j Smith

If you output this to a textfile as per my example, does the text file print correctly?

BTW my example of constructing a string is good coding practise. It isolates potential bugs and makes life easier. Using a constant, such as quote, again makes things easier to read and debug. You can add a variable anywhere in the string like this:

"blah blah blah, " & var1 & ",  blah blah blah "

or

"blah blah blah, " & quote & var1 & quote & ", blah blah blah"

[alpnz]

If you output this to a textfile as per my example, does the text file print correctly?

BTW my example of constructing a string is good coding practise. It isolates potential bugs and makes life easier. Using a constant, such as quote, again makes things easier to read and debug. You can add a variable anywhere in the string like this:

"blah blah blah, " & var1 & ",  blah blah blah "

or

"blah blah blah, " & quote & var1 & quote & ", blah blah blah"

I agree on the coding practice ... having never taken any formal training, and self taught myself in Access from v1.00 (might have been 1.1 Altzhiemers is kicking in you know :-)) any coding practice, was goood! :-)

I will eliminate the possibilities outside of Access, first.
Many thanks for your help, expect an update in the next day or so.
John S

[alpnz]

An Update

After pulling the text file created by Access into my editor on my desktop (SuSE Linux v10), i notice that there are spaces either side of each command , So
Rather than
A650,30,4,0, etc
it is
A650 , 30 , 4 , 0 , etc
By editing out the spaces, the label prints just as the program set asks.

Is it possible to stop Access adding in the spaces ... If you type the lines into your Access vbEditor, you will notice that it inserts the spaces, as you go down a line ... Am I on the right track .. is it possible ... so close ... the saga continues ... (Roll music)

[willakawill]

An Update

After pulling the text file created by Access into my editor on my desktop (SuSE Linux v10), i notice that there are spaces either side of each command , So
Rather than
A650,30,4,0, etc
it is
A650 , 30 , 4 , 0 , etc
By editing out the spaces, the label prints just as the program set asks.

Is it possible to stop Access adding in the spaces ... If you type the lines into your Access vbEditor, you will notice that it inserts the spaces, as you go down a line ... Am I on the right track .. is it possible ... so close ... the saga continues ... (Roll music)

Access will not insert spaces inside a string. Only in a list of parameters. Keep all of the commas inside the string and all will be fine.

This will not print spaces:

Private Sub createLab_Click()

Dim stOut As String
Const quote As String = """"

stOut = "N" & vbCrLf & "S4" & vbCrLf & "D10" & vbCrLf & "ZT" & vbCrLf _
& "A650,2,0,4,1,1,N," & quote & "Test Label" & quote & vbCrLf _
& "A30,3,0,4,1,1,N," & quote & "Sharkbites Sauce Co" & quote & vbCrLf _
& "A30,33,0,4,1,1,N," & quote & "Manuka Gully Road" & quote & vbCrLf _
& "A30,63,0,4,1,1,N," & quote & "Ophir" & quote & vbCrLf _
& "A100,135,0,2,2,2,N," & quote & " Sweet Valentine Sauce" & quote & vbCrLf _
& "A100,180,0,2,2,2,N," & quote & " Mild Thai Flavour" & quote & vbCrLf _
& "A100,220,0,2,2,2,N," & quote & "10th January 2007" & quote & vbCrLf _
& "b100,240,P,400,300,p100,285,30,f1,x3,y10,r60,l5,t 0,o0," _
& quote & "Sweet Valentine Medium Thai Flavour" & quote & vbCrLf & "P1" & vbCrLf

Open "c:\comout.txt" For Output As #1
Print #1, stOut
Close #1

End Sub

[alpnz]

Roll Credits ...
Works like a charm. Now I can go to it, and insert the data variables, for an excellent fast solution to printing a few thousand labels per day.
I have had a look at the posting I copied your code from, and the spaces were there. I wish I were younger, so much to learn ...
Serves me right for cutting and pasting eh.
If ever you are down our way, make sure and pay a visit, I'll even break out a wine for you ...

Many Many thanks.
John S

[alpnz]

Well its printing OK, however I am struggling with getting the Variables in there.
This

Private Sub createLab_Click()
    Dim var1 As String * 3
    Dim var2 As String * 13
    Dim var3 As String * 25
 
    Dim mydb As Database, myset As Recordset
    Dim rst As String
 
 
    rst = "SELECT * FROM qry_jlabel"
 
    Set mydb = CurrentDb()
    Set myset = mydb.OpenRecordset(rst)
 
    var1 = myset![jb_id]
    var2 = myset![jb_startdate]
    var3 = myset![jb_shortdesc]
    Const quote As String = """"
 
    Dim lab1 As String
 
lab1 = "N" & vbCrLf & "S4" & vbCrLf & "A650,2,0,4,1,1,N," & quote & "& var1 &" & quote & vbCrLf _
        & "A30,3,0,4,1,1,N," & quote & "& var2 &" & quote & vbCrLf _
        & "A30,33,0,4,1,1,N," & quote & "Manuka Gully Road" & quote & vbCrLf _
        & "A30,63,0,4,1,1,N," & quote & "Ophir" & quote & vbCrLf _
        & "A100,135,0,2,2,2,N," & quote & "& var3 &" & quote & vbCrLf _
        & "A100,180,0,2,2,2,N," & quote & " Mild Thai Flavour" & quote & vbCrLf _
        & "A100,220,0,2,2,2,N," & quote & "10th January 2007" & quote & vbCrLf _
        & "b100,240,P,400,300,p100,285,30,f1,x3,y10,r60,l5,t0,o0," & quote & "Sweet Valentine Medium Thai Flavour" & quote & vbCrLf _
        & "P1" & vbCrLf
 
 
 
 
Open "COM2:" For Output As #1
       Print #1, lab1
 
Close #1
 
End Sub
 
 
 

Gives me the label however the variables just print as & var1 & and & var2 & etc.
Can you explain the different scenarios, E.g. I might want to have "Job#:[var1]" as the text to print on the first line etc, Or it might just be a consolidated line built in the qry_jlabel.

Is using a query the right choice. I want to amalgamate fields together to read say
[34 mm] [Sweetheart] [Cherries]
which I would build in the query as "var3:=[sz_code]&" "&[vr_name]&" "&[vg_name]" three fields from three different tables, Size, Variety, and VGroup tables, in the query. The criteria for the query being a listbox on a form, with the jobs listed in it.

A huge advantage of your code, is that it is easy to build multiple lab1, lab2 labels in code, and just have conditional if statements prior to the Print#1 statement at the end. As you say good coding practice.

[alpnz]

OK this works for variables

Private Sub createLab_Click()
    Dim var1 As String * 3
    Dim var2 As String * 13
    Dim var3 As String * 25
 
    Dim mydb As Database, myset As Recordset
    Dim rst As String
 
 
    rst = "SELECT * FROM qry_jlabel"
 
    Set mydb = CurrentDb()
    Set myset = mydb.OpenRecordset(rst)
 
    var1 = myset![jb_id]
    var2 = myset![jb_startdate]
    var3 = myset![jb_shortdesc]
    Const quote As String = """"
 
    Dim lab1 As String
 
lab1 = "N" & vbCrLf & "S4" & vbCrLf & "A650,2,0,4,1,1,N," & quote & [var1] & quote & vbCrLf _
        & "A30,3,0,4,1,1,N," & quote & [var2] & quote & vbCrLf _
        & "A30,33,0,4,1,1,N," & quote & "Manuka Gully Road" & quote & vbCrLf _
        & "A30,63,0,4,1,1,N," & quote & "Ophir" & quote & vbCrLf _
        & "A100,135,0,2,2,2,N," & quote & [var3] & quote & vbCrLf _
        & "A100,180,0,2,2,2,N," & quote & " Mild Thai Flavour" & quote & vbCrLf _
        & "A100,220,0,2,2,2,N," & quote & "10th January 2007" & quote & vbCrLf _
        & "b100,240,P,400,300,p100,285,30,f1,x3,y10,r60,l5,t0,o0," & quote & "Sweet Valentine Medium Thai Flavour" & quote & vbCrLf _
        & "P1" & vbCrLf
 
 
 
 
Open "COM2:" For Output As #1
       Print #1, lab1
 
Close #1
 
 

Note the bracketing, no quotes, just the const. However as I say, how would I put text and variable together.

John S

[willakawill]

    Dim mydb As Database, myset As Recordset
    Dim rst As String
 
    rst = "SELECT * FROM qry_jlabel"
 
    Set mydb = CurrentDb()
    Set myset = mydb.OpenRecordset(rst)
 
    Const quote As String = """"
 
    Dim lab1 As String
    Open "COM2:" For Output As #1
    Do While Not myset.EOF
 
        lab1 = "N" & vbCrLf & "S4" & vbCrLf & "A650,2,0,4,1,1,N," & quote & myset![jb_id] & quote & vbCrLf _
            & "A30,3,0,4,1,1,N," & quote & myset![jb_startdate] & quote & vbCrLf _
            & "A30,33,0,4,1,1,N," & quote & "Manuka Gully Road" & quote & vbCrLf _
            & "A30,63,0,4,1,1,N," & quote & "Ophir" & quote & vbCrLf _
            & "A100,135,0,2,2,2,N," & quote & myset![jb_shortdesc] & quote & vbCrLf _
            & "A100,180,0,2,2,2,N," & quote & " Mild Thai Flavour" & quote & vbCrLf _
            & "A100,220,0,2,2,2,N," & quote & "10th January 2007" & quote & vbCrLf _
            & "b100,240,P,400,300,p100,285,30,f1,x3,y10,r60,l5,t0  ,o0," & quote & "Sweet Valentine Medium Thai Flavour" & quote & vbCrLf _
            & "P1" & vbCrLf
        Print #1, lab1
        myset.MoveNext
    Loop
    Close #1

[alpnz]

Thankyou,

This will work for a recordset with multiple records. In my case there is only likely to be one record matching the criteria in the query. I notice also you refer directly to the variable, which cuts down on code as well. Many many thanks for your time.

John S

[willakawill]

Thankyou,

This will work for a recordset with multiple records. In my case there is only likely to be one record matching the criteria in the query. I notice also you refer directly to the variable, which cuts down on code as well. Many many thanks for your time.

John S

You are very welcome

[alpnz]

Hi,
I have endeavoured to use this code in a live system, however I get a User-Defined Type not define error. The qry_elabel queries linked tables, Is it possible that I am defining the recordeset in the wrong way

Private Sub create_eplab_Click()
' First sort out the variables. Including label content, quantity to print etc
 
 
    Dim var1 As String
    Dim var2 As String
    Dim var3 As String
    Dim var4 As String
    Dim var5 As String
 
' Sort out where the data is coming from. Aim for reusable code
 
    Dim mydb As Database, myset As Recordset
    Dim sql_rst As String
 
 
   sql_rst = "SELECT * FROM qry_elabel"
 
    Set mydb = CurrentDb
    Set myset = mydb.OpenRecordset(sql_rst)
' Declare the variables values
 
    var1 = myset![var1]
    var2 = myset![var2]
    var3 = myset![var3]
    var4 = myset![var4]
    var5 = myset![var5]
    Const quote As String = """"
 
' Code each label combination required
 
    Dim lab1 As String
 
lab1 = "N" & vbCrLf _
        & "S4" & vbCrLf _
        & "A30,30,0,5,1,1,N," & quote & [var1] & quote & vbCrLf _
        & "A30,70,0,4,1,1,N," & quote & [var2] & quote & vbCrLf _
        & "A30,135,0,4,1,1,N," & quote & [var3] & quote & vbCrLf _
        & "B560,425,1,E30,1,2,160,B," & quote & [var4] & quote & vbCrLf _
        & "A300,300,0,4,1,1,N," & quote & [var5] & quote & vbCrLf _
        & "P1" & vbCrLf
 
 
' Print the label to the printer.
 
Open "COM2:" For Output As #1
       Print #1, lab1
 
Close #1
 
End Sub
 
 

This code worked fine in the test database as per our previous threads however pasting into the live system produces an error.
I would appreciate your thoughts.
John S

[willakawill]

And what, exactly, is the difference between your 'live' system and your 'test' system?

[alpnz]

And what, exactly, is the difference between your 'live' system and your 'test' system?

It is running on the same box, but it is a frontend / backend setup. The data is kept in a database file of its own. In other words the same version etc of Access is used.

[alpnz]

I have started a new thread with more detail. I am not sure how you quote the new thread, but it has the subject
VB compile error User defined Object

[willakawill]

I have started a new thread with more detail. I am not sure how you quote the new thread, but it has the subject
VB compile error User defined Object

That means you can't use 'CurrentDb'
As you have moved to another thread, I will leave it up to you to find your solution

[alpnz]

That means you can't use 'CurrentDb'
As you have moved to another thread, I will leave it up to you to find your solution

Oh now thats not fair ... "You cannot use CurrentDb" ..., and then leave me hanging ...:-)

Cheers
John S

[NeoPa]

(VB compile error User defined Object ) is the other thread.
Is your question answered John?
It's hard to know if you split the thread like that.