By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 2,060 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

Print Directly to the COM Port

alpnz
100+
P: 113
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.
Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub but_testdos_Click()
  3. On Error GoTo Err_but_testdos_Click
  4.     Dim t1 As String
  5.     Dim t2 As String
  6.     Dim t3 As Integer
  7.  
  8.  
  9.     t1 = "3 Kgs Nett"
  10.     t2 = "Sweetheart Cherries"
  11.     t3 = "941352001234"
  12.  '   Dim stAppName As String
  13.  '
  14.  '  stAppName = "c:\WINNT\system32\cmd /c copy f:\manager\testelt.txt COM2"
  15.  '  Call Shell(stAppName, 1)
  16. Open "COM2:" For Output As #1
  17.     Print #1, "N"
  18.     Print #1, "S4"
  19.     Print #1, "ZT"
  20.     Print #1, "A505,2,0,4,1,1,N," & "t1"
  21.     Print #1, "A100,135,0,2,2,2,N," & "t2"
  22.     Print #1, "B100,240,1,E30,2,3,50,B," & "t3"
  23.     Print #1, "P3"
  24. Close "COM2 : "
  25.  
  26.  
  27. Exit_but_testdos_Click:
  28.     Exit Sub
  29.  
  30. Err_but_testdos_Click:
  31.     MsgBox Err.Description, vbOKCancel
  32.  
  33.  
  34.     Resume Exit_but_testdos_Click
  35.  
  36. End Sub
  37.  
  38.  
Anyone have some suggestions, as to what I am missing out in the code.

Any help most appreciated.

John S
Jan 5 '07 #1
Share this Question
Share on Google+
31 Replies


100+
P: 1,646
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.
Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub but_testdos_Click()
  3. On Error GoTo Err_but_testdos_Click
  4.     Dim t1 As String
  5.     Dim t2 As String
  6.     Dim t3 As Integer
  7.  
  8.  
  9.     t1 = "3 Kgs Nett"
  10.     t2 = "Sweetheart Cherries"
  11.     t3 = "941352001234"
  12.  '   Dim stAppName As String
  13.  '
  14.  '  stAppName = "c:\WINNT\system32\cmd /c copy f:\manager\testelt.txt COM2"
  15.  '  Call Shell(stAppName, 1)
  16. Open "COM2:" For Output As #1
  17.     Print #1, "N"
  18.     Print #1, "S4"
  19.     Print #1, "ZT"
  20.     Print #1, "A505,2,0,4,1,1,N," & "t1"
  21.     Print #1, "A100,135,0,2,2,2,N," & "t2"
  22.     Print #1, "B100,240,1,E30,2,3,50,B," & "t3"
  23.     Print #1, "P3"
  24. Close "COM2 : "
  25.  
  26.  
  27. Exit_but_testdos_Click:
  28.     Exit Sub
  29.  
  30. Err_but_testdos_Click:
  31.     MsgBox Err.Description, vbOKCancel
  32.  
  33.  
  34.     Resume Exit_but_testdos_Click
  35.  
  36. End Sub
  37.  
  38.  
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 ?
Jan 5 '07 #2

missinglinq
Expert 2.5K+
P: 3,532
Although it's been a while since I worked at this level, I agree.
Expand|Select|Wrap|Line Numbers
  1. Close "COM2 : "
should simply be
Expand|Select|Wrap|Line Numbers
  1. Close # 1
Jan 6 '07 #3

alpnz
100+
P: 113
Although it's been a while since I worked at this level, I agree.
Expand|Select|Wrap|Line Numbers
  1. Close "COM2 : "
should simply be
Expand|Select|Wrap|Line Numbers
  1. 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

Expand|Select|Wrap|Line Numbers
  1.  
  2. N
  3. S4
  4. D10
  5. ZT
  6. A505,2,0,4,1,1,N,"3 KGS"
  7. A650,2,0,4,1,1,N,"1 OF 1"
  8. A30,3,0,4,1,1,N,"Molynuts Fruit Corp"
  9. A30,33,0,4,1,1,N,"Back Road"
  10. A30,63,0,4,1,1,N,"Antartica"
  11. A100,135,0,2,2,2,N,"Sweetheart Cherries"
  12. A100,180,0,2,2,2,N,"34 MM"
  13. A100,220,0,2,2,2,N,"3 Kg Presentation Pack"
  14. A550,280,0,3,2,3,N,"TAG 1"
  15. A550,333,0,3,2,3,N,"SIS6871-0"
  16. b100,240,P,400,300,p100,260,30,f1,x3,y10,r60,l5,t0,o0,"3 Kg Nett when Packed. Export Quality."
  17. P2
  18.  
  19.  
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??
Jan 6 '07 #4

100+
P: 1,646
Expand|Select|Wrap|Line Numbers
  1. MsgBox """" & "These are quotes" & """"
Of
Expand|Select|Wrap|Line Numbers
  1. Const Quote As String = """"
  2.  
  3. MsgBox Quote & "These are quotes" & Quote
Jan 6 '07 #5

alpnz
100+
P: 113
Expand|Select|Wrap|Line Numbers
  1. MsgBox """" & "These are quotes" & """"
Of
Expand|Select|Wrap|Line Numbers
  1. Const Quote As String = """"
  2.  
  3. MsgBox Quote & "These are quotes" & Quote
OKaaaay ...
Can you give me an example of a line above using this idea.?
Jan 10 '07 #6

alpnz
100+
P: 113
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub createLab_Click()
  3. Dim db As Database
  4. Dim rst As Recordset
  5. Const Quote As String = """"
  6.  
  7.   Set db = CurrentDb
  8.   Set rst = db.OpenRecordset("qry_jlabel")
  9.  
  10.     Open "testlabel.txt" For Output As #1
  11.         Print #1, "N"
  12.         Print #1, "S4"
  13.         Print #1, "D10"
  14.         Print #1, "ZT"
  15.         Print #1, "A450, 2, 0, 4, 1, 1, N,TD"
  16.         Print #1, "A650, 2, 0, 4, 1, 1, N,TD"
  17.         Print #1, A30, 3, 0, 4, 1, 1, N, Quote & "Smith Family" & Qoute; ""
  18.         Print #1, A30, 33, 0, 4, 1, 1, N, Quote & "Sunderland St" & Quote
  19.         Print #1, A30, 63, 0, 4, 1, 1, N, Quote & "Clyde 9330" & Quote; ""
  20.         Print #1, A100, 135, 0, 2, 2, 2, N, Quote & "Sweet Valentine Sauce" & Quote; ""
  21.         Print #1, A100, 180, 0, 2, 2, 2, N, Quote & "Med Bite" & Quote; ""
  22.         Print #1, A100, 220, 0, 2, 2, 2, N, Quote & "9th January 2007" & Quote; ""
  23.         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; ""
  24.         Print #1, P3
  25.     Close #1
  26.  
  27. End Sub
  28.  
  29. Private Sub printLab_Click()
  30. Dim stAppName As String
  31.  
  32.    stAppName = "c:\WINNT\system32\cmd /c copy testlabel.txt COM2"
  33.    Call Shell(stAppName, 1)
  34. End Sub
  35.  
  36.  
Jan 10 '07 #7

alpnz
100+
P: 113
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub createLab_Click()
  3. Dim db As Database
  4. Dim rst As Recordset
  5. Const Quote As String = """"
  6.  
  7.   Set db = CurrentDb
  8.   Set rst = db.OpenRecordset("qry_jlabel")
  9.  
  10.     Open "testlabel.txt" For Output As #1
  11.         Print #1, "N"
  12.         Print #1, "S4"
  13.         Print #1, "D10"
  14.         Print #1, "ZT"
  15.         Print #1, "A450, 2, 0, 4, 1, 1, N,TD"
  16.         Print #1, "A650, 2, 0, 4, 1, 1, N,TD"
  17.         Print #1, A30, 3, 0, 4, 1, 1, N, Quote & "Smith Family" & Qoute; ""
  18.         Print #1, A30, 33, 0, 4, 1, 1, N, Quote & "Sunderland St" & Quote
  19.         Print #1, A30, 63, 0, 4, 1, 1, N, Quote & "Clyde 9330" & Quote; ""
  20.         Print #1, A100, 135, 0, 2, 2, 2, N, Quote & "Sweet Valentine Sauce" & Quote; ""
  21.         Print #1, A100, 180, 0, 2, 2, 2, N, Quote & "Med Bite" & Quote; ""
  22.         Print #1, A100, 220, 0, 2, 2, 2, N, Quote & "9th January 2007" & Quote; ""
  23.         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; ""
  24.         Print #1, P3
  25.     Close #1
  26.  
  27. End Sub
  28.  
  29. Private Sub printLab_Click()
  30. Dim stAppName As String
  31.  
  32.    stAppName = "c:\WINNT\system32\cmd /c copy testlabel.txt COM2"
  33.    Call Shell(stAppName, 1)
  34. End Sub
  35.  
  36.  
The resulting text file flies to bits at the & quote stage

Expand|Select|Wrap|Line Numbers
  1. N
  2. S4
  3. D10
  4. ZT
  5. A450, 2, 0, 4, 1, 1, N,TD
  6. A650, 2, 0, 4, 1, 1, N,TD
  7.                3             0             4             1             1                          "Smith Family
  8.                33            0             4             1             1                          "Sunderland St"
  9.                63            0             4             1             1                          "Clyde 9330"
  10.                135           0             2             2             2                          "Sweet Valentine Sauce"
  11.                180           0             2             2             2                          "Med Bite"
  12.                220           0             2             2             2                          "9th January 2007"
  13.                240                         400           300                         285           30                                                                                                             "Test Batch: Sweet Valentine Sauce - 09/01/2007"
  14.  
  15.  
Hope this is legible to you ??.

John S
Jan 10 '07 #8

alpnz
100+
P: 113
The resulting text file flies to bits at the & quote stage

Expand|Select|Wrap|Line Numbers
  1. N
  2. S4
  3. D10
  4. ZT
  5. A450, 2, 0, 4, 1, 1, N,TD
  6. A650, 2, 0, 4, 1, 1, N,TD
  7.                3             0             4             1             1                          "Smith Family
  8.                33            0             4             1             1                          "Sunderland St"
  9.                63            0             4             1             1                          "Clyde 9330"
  10.                135           0             2             2             2                          "Sweet Valentine Sauce"
  11.                180           0             2             2             2                          "Med Bite"
  12.                220           0             2             2             2                          "9th January 2007"
  13.                240                         400           300                         285           30                                                                                                             "Test Batch: Sweet Valentine Sauce - 09/01/2007"
  14.  
  15.  
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Const Quote As String = """"
  3.  
  4.   Set db = CurrentDb
  5.   Set rst = db.OpenRecordset("qry_jlabel")
  6.  
  7.     Open "testlabel.txt" For Output As #1
  8.         Print #1, "N"
  9.         Print #1, "S4"
  10.         Print #1, "D10"
  11.         Print #1, "ZT"
  12.         Print #1, "A450, 2, 0, 4, 1, 1, N,TD"
  13.         Print #1, "A650, 2, 0, 4, 1, 1, N,TD"
  14.         Print #1, "A30, 3, 0, 4, 1, 1, N, Quote & "; Smith; Family; " & Quote"
  15.         Print #1, "A30, 33, 0, 4, 1, 1, N, Quote & "; Sunderland; St; " & Quote"
  16.         Print #1, "A30, 63, 0, 4, 1, 1, N, Quote & "; Clyde; 9330; " & Quote"
  17.         Print #1, "A100, 135, 0, 2, 2, 2, N, Quote & "; Sweet; Valentine; Sauce; " & Quote"
  18.         Print #1, "A100, 180, 0, 2, 2, 2, N, Quote & "; Med; Bite; " & Quote"
  19.         Print #1, "A100, 220, 0, 2, 2, 2, N, Quote & "; 9; th; January; 2007; " & Quote"
  20.         Print #1, "b100, 240, P, 400, 300, p100, 285, 30, f1, x3, y10, r60, l5, t0, o0, Quote & "; Test; Batch; Sweet; Valentine; " & Quote"
  21.         Print #1, "P3"
  22.     Close #1
  23.  
  24.  
And the resulting file

Expand|Select|Wrap|Line Numbers
  1.  
  2. N
  3. S4
  4. D10
  5. ZT
  6. A450, 2, 0, 4, 1, 1, N,TD
  7. A650, 2, 0, 4, 1, 1, N,TD
  8. A30, 3, 0, 4, 1, 1, N, Quote &  & Quote
  9. A30, 33, 0, 4, 1, 1, N, Quote &  & Quote
  10. A30, 63, 0, 4, 1, 1, N, Quote &  9330  & Quote
  11. A100, 135, 0, 2, 2, 2, N, Quote &  & Quote
  12. A100, 180, 0, 2, 2, 2, N, Quote &  & Quote
  13. A100, 220, 0, 2, 2, 2, N, Quote &  9  2007  & Quote
  14. b100, 240, P, 400, 300, p100, 285, 30, f1, x3, y10, r60, l5, t0, o0, Quote &  & Quote
  15. P3
  16.  
  17.  
As you can see getting really close to cracking it with your help.

Prettyeee Exciting Yogi ...
John S
Jan 10 '07 #9

alpnz
100+
P: 113
And the resulting file

Expand|Select|Wrap|Line Numbers
  1.  
  2. N
  3. S4
  4. D10
  5. ZT
  6. A450, 2, 0, 4, 1, 1, N,TD
  7. A650, 2, 0, 4, 1, 1, N,TD
  8. A30, 3, 0, 4, 1, 1, N, Quote &  & Quote
  9. A30, 33, 0, 4, 1, 1, N, Quote &  & Quote
  10. A30, 63, 0, 4, 1, 1, N, Quote &  9330  & Quote
  11. A100, 135, 0, 2, 2, 2, N, Quote &  & Quote
  12. A100, 180, 0, 2, 2, 2, N, Quote &  & Quote
  13. A100, 220, 0, 2, 2, 2, N, Quote &  9  2007  & Quote
  14. b100, 240, P, 400, 300, p100, 285, 30, f1, x3, y10, r60, l5, t0, o0, Quote &  & Quote
  15. P3
  16.  
  17.  
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
Jan 10 '07 #10

alpnz
100+
P: 113
An Update ...
This Code
Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub createLab_Click()
  3. Dim db As Database
  4. Dim rst As Recordset
  5. Const LL As String = """"
  6.  
  7.   Set db = CurrentDb
  8.   Set rst = db.OpenRecordset("qry_jlabel")
  9.  
  10.     Open "testlabel.txt" For Output As #1
  11.         Print #1, "N"
  12.         Print #1, "S4"
  13.         Print #1, "D10"
  14.         Print #1, "ZT"
  15.         Print #1, "A650, 2, 0, 4, 1, 1, N, ""Test Label"""
  16.         Print #1, "A30, 3, 0, 4, 1, 1, N, ""Smith Family"""
  17.         Print #1, "A30, 33, 0, 4, 1, 1, N, ""Sunderland St"""
  18.         Print #1, "A30, 63, 0, 4, 1, 1, N, ""Clyde 9330"""
  19.         Print #1, "A100, 135, 0, 2, 2, 2, N,"" Sweet Valentine Sauce"""
  20.         Print #1, "A100, 180, 0, 2, 2, 2, N,"" Mild Thai Flavour"""
  21.         Print #1, "A100, 220, 0, 2, 2, 2, N, ""10th January 2007"""
  22.         Print #1, "b100, 240, P, 400, 300, p100, 285, 30, f1, x3, y10, r60, l5, t0, o0, ""Sweet Valentine Medium Thai Flavour"""
  23.         Print #1, "P3"
  24.  
  25.     Close #1
  26.  
  27. End Sub
  28.  
  29. Private Sub printLab_Click()
  30. Dim stAppName As String
  31.  
  32.    stAppName = "c:\WINNT\system32\cmd /c copy testlabel.txt COM2"
  33.    Call Shell(stAppName, 1)
  34. End Sub
  35.  
  36.  
Produces the file testlabel.txt

Expand|Select|Wrap|Line Numbers
  1.  
  2. N
  3. S4
  4. D10
  5. ZT
  6. A650, 2, 0, 4, 1, 1, N, "Test Label"
  7. A30, 3, 0, 4, 1, 1, N, "Smith Family"
  8. A30, 33, 0, 4, 1, 1, N, "Sunderland St"
  9. A30, 63, 0, 4, 1, 1, N, "Clyde 9330"
  10. A100, 135, 0, 2, 2, 2, N," Sweet Valentine Sauce"
  11. A100, 180, 0, 2, 2, 2, N," Mild Thai Flavour"
  12. A100, 220, 0, 2, 2, 2, N, "10th January 2007"
  13. b100, 240, P, 400, 300, p100, 285, 30, f1, x3, y10, r60, l5, t0, o0, "Sweet Valentine Medium Thai Flavour"
  14. P3
  15.  
  16.  
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
Jan 10 '07 #11

alpnz
100+
P: 113
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub createLab_Click()
  3.  
  4.     Open "COM2: " For Output As #1
  5.         Print #1, "N"
  6.         Print #1, "S4"
  7.         Print #1, "D10"
  8.         Print #1, "ZT"
  9.         Print #1, "A650, 2, 0, 4, 1, 1, N, ""Test Label"""
  10.         Print #1, "A30, 3, 0, 4, 1, 1, N, ""Sharkbites Sauce Co"""
  11.         Print #1, "A30, 33, 0, 4, 1, 1, N, ""Manuka Gully Road"""
  12.         Print #1, "A30, 63, 0, 4, 1, 1, N, ""Ophir"""
  13.         Print #1, "A100, 135, 0, 2, 2, 2, N,"" Sweet Valentine Sauce"""
  14.         Print #1, "A100, 180, 0, 2, 2, 2, N,"" Mild Thai Flavour"""
  15.         Print #1, "A100, 220, 0, 2, 2, 2, N, ""10th January 2007"""
  16.         Print #1, "b100, 240, P, 400, 300, p100, 285, 30, f1, x3, y10, r60, l5, t0, o0, ""Sweet Valentine Medium Thai Flavour"""
  17.         Print #1, "P1"
  18.  
  19.     Close #1
  20.  
  21. End Sub
  22.  
  23.  
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.
Jan 10 '07 #12

100+
P: 1,646
This code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub createLab_Click()
  2.  
  3.     Dim stOut As String
  4.     Const quote As String = """"
  5.  
  6.     stOut = "N" & vbCrLf & "S4" & vbCrLf & "D10" & vbCrLf & "ZT" & vbCrLf _
  7.         & "A650, 2, 0, 4, 1, 1, N," & quote & "Test Label" & quote & vbCrLf _
  8.         & "A30, 3, 0, 4, 1, 1, N," & quote & "Sharkbites Sauce Co" & quote & vbCrLf _
  9.         & "A30, 33, 0, 4, 1, 1, N," & quote & "Manuka Gully Road" & quote & vbCrLf _
  10.         & "A30, 63, 0, 4, 1, 1, N," & quote & "Ophir" & quote & vbCrLf _
  11.         & "A100, 135, 0, 2, 2, 2, N," & quote & " Sweet Valentine Sauce" & quote & vbCrLf _
  12.         & "A100, 180, 0, 2, 2, 2, N," & quote & " Mild Thai Flavour" & quote & vbCrLf _
  13.         & "A100, 220, 0, 2, 2, 2, N," & quote & "10th January 2007" & quote & vbCrLf _
  14.         & "b100, 240, P, 400, 300, p100, 285, 30, f1, x3, y10, r60, l5, t0, o0," _
  15.         & quote & "Sweet Valentine Medium Thai Flavour" & quote & vbCrLf & "P1" & vbCrLf
  16.  
  17.     Open "c:\comout.txt" For Output As #1
  18.         Print #1, stOut
  19.     Close #1
  20.  
  21. End Sub
  22.  
produces this output:
Expand|Select|Wrap|Line Numbers
  1. N
  2. S4
  3. D10
  4. ZT
  5. A650, 2, 0, 4, 1, 1, N,"Test Label"
  6. A30, 3, 0, 4, 1, 1, N,"Sharkbites Sauce Co"
  7. A30, 33, 0, 4, 1, 1, N,"Manuka Gully Road"
  8. A30, 63, 0, 4, 1, 1, N,"Ophir"
  9. A100, 135, 0, 2, 2, 2, N," Sweet Valentine Sauce"
  10. A100, 180, 0, 2, 2, 2, N," Mild Thai Flavour"
  11. A100, 220, 0, 2, 2, 2, N,"10th January 2007"
  12. b100, 240, P, 400, 300, p100, 285, 30, f1, x3, y10, r60, l5, t0, o0,"Sweet Valentine Medium Thai Flavour"
  13. P1
Jan 10 '07 #13

alpnz
100+
P: 113
This code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub createLab_Click()
  2.  
  3.     Dim stOut As String
  4.     Const quote As String = """"
  5.  
  6.     stOut = "N" & vbCrLf & "S4" & vbCrLf & "D10" & vbCrLf & "ZT" & vbCrLf _
  7.         & "A650, 2, 0, 4, 1, 1, N," & quote & "Test Label" & quote & vbCrLf _
  8.         & "A30, 3, 0, 4, 1, 1, N," & quote & "Sharkbites Sauce Co" & quote & vbCrLf _
  9.         & "A30, 33, 0, 4, 1, 1, N," & quote & "Manuka Gully Road" & quote & vbCrLf _
  10.         & "A30, 63, 0, 4, 1, 1, N," & quote & "Ophir" & quote & vbCrLf _
  11.         & "A100, 135, 0, 2, 2, 2, N," & quote & " Sweet Valentine Sauce" & quote & vbCrLf _
  12.         & "A100, 180, 0, 2, 2, 2, N," & quote & " Mild Thai Flavour" & quote & vbCrLf _
  13.         & "A100, 220, 0, 2, 2, 2, N," & quote & "10th January 2007" & quote & vbCrLf _
  14.         & "b100, 240, P, 400, 300, p100, 285, 30, f1, x3, y10, r60, l5, t0, o0," _
  15.         & quote & "Sweet Valentine Medium Thai Flavour" & quote & vbCrLf & "P1" & vbCrLf
  16.  
  17.     Open "c:\comout.txt" For Output As #1
  18.         Print #1, stOut
  19.     Close #1
  20.  
  21. End Sub
  22.  
produces this output:
Expand|Select|Wrap|Line Numbers
  1. N
  2. S4
  3. D10
  4. ZT
  5. A650, 2, 0, 4, 1, 1, N,"Test Label"
  6. A30, 3, 0, 4, 1, 1, N,"Sharkbites Sauce Co"
  7. A30, 33, 0, 4, 1, 1, N,"Manuka Gully Road"
  8. A30, 63, 0, 4, 1, 1, N,"Ophir"
  9. A100, 135, 0, 2, 2, 2, N," Sweet Valentine Sauce"
  10. A100, 180, 0, 2, 2, 2, N," Mild Thai Flavour"
  11. A100, 220, 0, 2, 2, 2, N,"10th January 2007"
  12. b100, 240, P, 400, 300, p100, 285, 30, f1, x3, y10, r60, l5, t0, o0,"Sweet Valentine Medium Thai Flavour"
  13. 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
Jan 10 '07 #14

alpnz
100+
P: 113
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
Jan 10 '07 #15

100+
P: 1,646
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:

Expand|Select|Wrap|Line Numbers
  1. "blah blah blah, " & var1 & ",  blah blah blah "
or

Expand|Select|Wrap|Line Numbers
  1. "blah blah blah, " & quote & var1 & quote & ", blah blah blah"
Jan 10 '07 #16

alpnz
100+
P: 113
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:

Expand|Select|Wrap|Line Numbers
  1. "blah blah blah, " & var1 & ",  blah blah blah "
or

Expand|Select|Wrap|Line Numbers
  1. "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
Jan 10 '07 #17

alpnz
100+
P: 113
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)
Jan 11 '07 #18

100+
P: 1,646
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
Jan 11 '07 #19

alpnz
100+
P: 113
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
Jan 11 '07 #20

alpnz
100+
P: 113
Well its printing OK, however I am struggling with getting the Variables in there.
This

Expand|Select|Wrap|Line Numbers
  1. Private Sub createLab_Click()
  2.     Dim var1 As String * 3
  3.     Dim var2 As String * 13
  4.     Dim var3 As String * 25
  5.  
  6.     Dim mydb As Database, myset As Recordset
  7.     Dim rst As String
  8.  
  9.  
  10.     rst = "SELECT * FROM qry_jlabel"
  11.  
  12.     Set mydb = CurrentDb()
  13.     Set myset = mydb.OpenRecordset(rst)
  14.  
  15.     var1 = myset![jb_id]
  16.     var2 = myset![jb_startdate]
  17.     var3 = myset![jb_shortdesc]
  18.     Const quote As String = """"
  19.  
  20.     Dim lab1 As String
  21.  
  22. lab1 = "N" & vbCrLf & "S4" & vbCrLf & "A650,2,0,4,1,1,N," & quote & "& var1 &" & quote & vbCrLf _
  23.         & "A30,3,0,4,1,1,N," & quote & "& var2 &" & quote & vbCrLf _
  24.         & "A30,33,0,4,1,1,N," & quote & "Manuka Gully Road" & quote & vbCrLf _
  25.         & "A30,63,0,4,1,1,N," & quote & "Ophir" & quote & vbCrLf _
  26.         & "A100,135,0,2,2,2,N," & quote & "& var3 &" & quote & vbCrLf _
  27.         & "A100,180,0,2,2,2,N," & quote & " Mild Thai Flavour" & quote & vbCrLf _
  28.         & "A100,220,0,2,2,2,N," & quote & "10th January 2007" & quote & vbCrLf _
  29.         & "b100,240,P,400,300,p100,285,30,f1,x3,y10,r60,l5,t0,o0," & quote & "Sweet Valentine Medium Thai Flavour" & quote & vbCrLf _
  30.         & "P1" & vbCrLf
  31.  
  32.  
  33.  
  34.  
  35. Open "COM2:" For Output As #1
  36.        Print #1, lab1
  37.  
  38. Close #1
  39.  
  40. End Sub
  41.  
  42.  
  43.  
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.
Jan 11 '07 #21

alpnz
100+
P: 113
OK this works for variables
Expand|Select|Wrap|Line Numbers
  1. Private Sub createLab_Click()
  2.     Dim var1 As String * 3
  3.     Dim var2 As String * 13
  4.     Dim var3 As String * 25
  5.  
  6.     Dim mydb As Database, myset As Recordset
  7.     Dim rst As String
  8.  
  9.  
  10.     rst = "SELECT * FROM qry_jlabel"
  11.  
  12.     Set mydb = CurrentDb()
  13.     Set myset = mydb.OpenRecordset(rst)
  14.  
  15.     var1 = myset![jb_id]
  16.     var2 = myset![jb_startdate]
  17.     var3 = myset![jb_shortdesc]
  18.     Const quote As String = """"
  19.  
  20.     Dim lab1 As String
  21.  
  22. lab1 = "N" & vbCrLf & "S4" & vbCrLf & "A650,2,0,4,1,1,N," & quote & [var1] & quote & vbCrLf _
  23.         & "A30,3,0,4,1,1,N," & quote & [var2] & quote & vbCrLf _
  24.         & "A30,33,0,4,1,1,N," & quote & "Manuka Gully Road" & quote & vbCrLf _
  25.         & "A30,63,0,4,1,1,N," & quote & "Ophir" & quote & vbCrLf _
  26.         & "A100,135,0,2,2,2,N," & quote & [var3] & quote & vbCrLf _
  27.         & "A100,180,0,2,2,2,N," & quote & " Mild Thai Flavour" & quote & vbCrLf _
  28.         & "A100,220,0,2,2,2,N," & quote & "10th January 2007" & quote & vbCrLf _
  29.         & "b100,240,P,400,300,p100,285,30,f1,x3,y10,r60,l5,t0,o0," & quote & "Sweet Valentine Medium Thai Flavour" & quote & vbCrLf _
  30.         & "P1" & vbCrLf
  31.  
  32.  
  33.  
  34.  
  35. Open "COM2:" For Output As #1
  36.        Print #1, lab1
  37.  
  38. Close #1
  39.  
  40.  
Note the bracketing, no quotes, just the const. However as I say, how would I put text and variable together.

John S
Jan 11 '07 #22

100+
P: 1,646
Expand|Select|Wrap|Line Numbers
  1.     Dim mydb As Database, myset As Recordset
  2.     Dim rst As String
  3.  
  4.     rst = "SELECT * FROM qry_jlabel"
  5.  
  6.     Set mydb = CurrentDb()
  7.     Set myset = mydb.OpenRecordset(rst)
  8.  
  9.     Const quote As String = """"
  10.  
  11.     Dim lab1 As String
  12.     Open "COM2:" For Output As #1
  13.     Do While Not myset.EOF
  14.  
  15.         lab1 = "N" & vbCrLf & "S4" & vbCrLf & "A650,2,0,4,1,1,N," & quote & myset![jb_id] & quote & vbCrLf _
  16.             & "A30,3,0,4,1,1,N," & quote & myset![jb_startdate] & quote & vbCrLf _
  17.             & "A30,33,0,4,1,1,N," & quote & "Manuka Gully Road" & quote & vbCrLf _
  18.             & "A30,63,0,4,1,1,N," & quote & "Ophir" & quote & vbCrLf _
  19.             & "A100,135,0,2,2,2,N," & quote & myset![jb_shortdesc] & quote & vbCrLf _
  20.             & "A100,180,0,2,2,2,N," & quote & " Mild Thai Flavour" & quote & vbCrLf _
  21.             & "A100,220,0,2,2,2,N," & quote & "10th January 2007" & quote & vbCrLf _
  22.             & "b100,240,P,400,300,p100,285,30,f1,x3,y10,r60,l5,t0  ,o0," & quote & "Sweet Valentine Medium Thai Flavour" & quote & vbCrLf _
  23.             & "P1" & vbCrLf
  24.         Print #1, lab1
  25.         myset.MoveNext
  26.     Loop
  27.     Close #1
Jan 11 '07 #23

alpnz
100+
P: 113
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
Jan 11 '07 #24

100+
P: 1,646
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
Jan 11 '07 #25

alpnz
100+
P: 113
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub create_eplab_Click()
  2. ' First sort out the variables. Including label content, quantity to print etc
  3.  
  4.  
  5.     Dim var1 As String
  6.     Dim var2 As String
  7.     Dim var3 As String
  8.     Dim var4 As String
  9.     Dim var5 As String
  10.  
  11. ' Sort out where the data is coming from. Aim for reusable code
  12.  
  13.     Dim mydb As Database, myset As Recordset
  14.     Dim sql_rst As String
  15.  
  16.  
  17.    sql_rst = "SELECT * FROM qry_elabel"
  18.  
  19.     Set mydb = CurrentDb
  20.     Set myset = mydb.OpenRecordset(sql_rst)
  21. ' Declare the variables values
  22.  
  23.     var1 = myset![var1]
  24.     var2 = myset![var2]
  25.     var3 = myset![var3]
  26.     var4 = myset![var4]
  27.     var5 = myset![var5]
  28.     Const quote As String = """"
  29.  
  30. ' Code each label combination required
  31.  
  32.     Dim lab1 As String
  33.  
  34. lab1 = "N" & vbCrLf _
  35.         & "S4" & vbCrLf _
  36.         & "A30,30,0,5,1,1,N," & quote & [var1] & quote & vbCrLf _
  37.         & "A30,70,0,4,1,1,N," & quote & [var2] & quote & vbCrLf _
  38.         & "A30,135,0,4,1,1,N," & quote & [var3] & quote & vbCrLf _
  39.         & "B560,425,1,E30,1,2,160,B," & quote & [var4] & quote & vbCrLf _
  40.         & "A300,300,0,4,1,1,N," & quote & [var5] & quote & vbCrLf _
  41.         & "P1" & vbCrLf
  42.  
  43.  
  44. ' Print the label to the printer.
  45.  
  46. Open "COM2:" For Output As #1
  47.        Print #1, lab1
  48.  
  49. Close #1
  50.  
  51. End Sub
  52.  
  53.  
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
Jan 14 '07 #26

100+
P: 1,646
And what, exactly, is the difference between your 'live' system and your 'test' system?
Jan 14 '07 #27

alpnz
100+
P: 113
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.
Jan 14 '07 #28

alpnz
100+
P: 113
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
Jan 14 '07 #29

100+
P: 1,646
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
Jan 14 '07 #30

alpnz
100+
P: 113
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
Jan 14 '07 #31

NeoPa
Expert Mod 15k+
P: 31,186
(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.
Jan 14 '07 #32

Post your reply

Sign in to post your reply or Sign up for a free account.