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

Manipulate an Access Query recordset in code.

P: 9
Hi, any ideas for this problem would be appreciated.

- I am coding to enable me to use a receipt printer as the Access Report functionality does not give the correct working function.

- I have a query - 'Query1' which has the recordset result that i am interested in, this has been done through the Access 'Create Query'.

The query contains:

ORDERID, PRODUCTIS, UNITPRICE, QTY


I want to be able to print each line of this recordset in code.

I have some code which I think should work however cannot figure out some essential code.


Expand|Select|Wrap|Line Numbers
  1. Private Sub Command8_Click()
  2.  
  3. Dim db As Database
  4. Dim rst As Recordset
  5.  
  6. Set db = CurrentDb
  7. Set rst = db.OpenRecordset("Query1")
  8.  
  9. 'Open printer
  10. Open "LPT1:" For Output As #1
  11.  
  12. rst.MoveFirst
  13. Do While rst.EOF = False
  14. rst.MoveNext
  15.  
  16. '=== UNSURE OF WHAT CODE GOES HERE, I WANT TO BE ABLE TO 
  17. 'DO SOMETHING LIKE 
  18. 'Print #1, print line 1 of the recordset
  19.  
  20. Loop
  21. Close #1
-------
Your help is appreciated.
Saj
Dec 21 '06 #1
Share this Question
Share on Google+
9 Replies


Expert 5K+
P: 8,434
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command8_Click()
  2.  
  3. Dim db As Database
  4. Dim rst As Recordset
  5.  
  6. Set db = CurrentDb
  7. Set rst = db.OpenRecordset("Query1")
  8.  
  9. 'Open printer
  10. Open "LPT1:" For Output As #1
  11. rst.MoveFirst
  12. Do While rst.EOF = False
  13. rst.MoveNext
  14.  
  15. '=== UNSURE OF WHAT CODE GOES HERE, I WANT TO BE ABLE TO 
  16. 'DO SOMETHING LIKE 
  17. 'Print #1, print line 1 of the recordset
  18.  
  19. Loop
  20. Close #1
I'd be a little surprised if you can get away with treating LPT1 this way nowadays, but assuming you know what you're doing there...

Maybe something along these lines
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command8_Click()
  2.  
  3.   Dim db As Database
  4.   Dim rst As Recordset
  5.  
  6.   Set db = CurrentDb
  7.   Set rst = db.OpenRecordset("Query1")
  8.  
  9.   'Open printer
  10.   Open "LPT1:" For Output As #1
  11.  
  12.   rst.MoveFirst
  13.   Do While rst.EOF = False
  14.  
  15.     Print #1, "ORDERID:" & rst("ORDERID") & _
  16.       "   PRODUCTIS:" & rst("PRODUCTIS") and so on.
  17.  
  18.     ' You had this statement too early - would have missed the
  19.     ' first record, and possibly bombed on the last.
  20.     rst.MoveNext
  21.   Loop
  22.   Close #1
Dec 22 '06 #2

nico5038
Expert 2.5K+
P: 3,072
Much work, why not use the statement:

docmd.TransferText acExportFixed,,"Query1","LPT1:",true

Nic;o)
Dec 22 '06 #3

P: 9
Hey Killer42,

Thanks your suggestion allowed me to see through the fog.
Direct printing to LPT1 is still the best way for Receipt printers & allows useage of the ESC codes.

ps Nico5038 - The code you say would be ok for normal A4 printers, however the nature of continuous roll paper for Receipt printers needs fine control in Code.

Te following code works a treat!

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command20_Click()
  2.  
  3. Me.Requery
  4. Me.Refresh
  5.  
  6.        Dim strOrderId As String * 8 'specifies width of 8 characters
  7.        Dim strProductDescription As String * 20  'specifies width of 20 characters
  8.        Dim strQty As String * 4    'specifies width of 4 characters
  9.        Dim strUnitPrice As String * 8    'specifies width of 8 characters
  10.        Dim mydb As Database, myset As Recordset
  11.        Dim strsql As String
  12.  
  13.  
  14. strsql = "SELECT * FROM Query1 WHERE [OrderId]" & " = " & Forms!form1!OrderId & ";"
  15.  
  16.        Set mydb = CurrentDb()
  17.        Set myset = mydb.OpenRecordset(strsql)
  18.  
  19. 'Open printer
  20. Open "LPT1:" For Output As #1
  21.  
  22. Print #1, "Company Name"
  23. Print #1, "Company Address1"
  24. Print #1, "Company Address2"
  25. Print #1, "Company Address3"
  26. Print #1,
  27. Print #1, "Telephone No"
  28. Print #1, "Web Address"
  29. Print #1,
  30. Print #1, "Receipt No: " & OrderId
  31. Print #1, Date
  32. Print #1,
  33.  
  34.  
  35. 'Titles
  36.        LSet strOrderId = "OrderId"
  37.        LSet strProductDescription = "ProductDesciption"
  38.        LSet strQty = "Qty"
  39.        LSet strUnitPrice = "UnitPrice"
  40.  
  41. Print #1, strOrderId & strProductDescription & strQty & strUnitPrice
  42. Print #1,
  43.  
  44. 'Print each line of recordset
  45.  
  46.         myset.MoveFirst
  47.         Do Until myset.EOF
  48.             LSet strOrderId = myset![OrderId]
  49.             LSet strProductDescription = myset![ProductDesciption]
  50.             LSet strQty = myset![Qty]
  51.             LSet strUnitPrice = myset![UnitPrice]
  52.  
  53. 'Concatenate all of the variables together as in the following:
  54. Print #1, strOrderId & strProductDescription & strQty & strUnitPrice
  55.  
  56.             myset.MoveNext
  57.  
  58. Loop
  59.  
  60. Print #1,
  61. Print #1,
  62. Print #1,
  63.  
  64. 'Printer Autocutter
  65. Print #1, Chr$(27) & Chr$(100)
  66. 'Cashdraw Open
  67. Print #1, Chr$(7)
  68.  
  69. 'Close LPT1
  70. Close #1
  71.  
  72.         myset.Close
  73.         mydb.Close
  74.  
  75.         MsgBox "Receipt printed"
  76.  
  77. End Sub
Dec 22 '06 #4

Expert 5K+
P: 8,434
Excellent! Glad we could help.
Dec 23 '06 #5

alpnz
100+
P: 113
Excellent! Glad we could help.
Sajuk,
Do you think it would be possible to Send EPL/ZPL language to the printer in this manner.?
I ask as you may have dealt with Thermal Label printers in the past, and indeed achieved fast Label printing using the Eltron, or Zebra Print Commands.
Dec 23 '06 #6

nico5038
Expert 2.5K+
P: 3,072
Hi alpnz,

When the printer accepts commands in the send string it will work.
The code above uses the CHR(27) being the ESCape character to direct the command to the printer and all you need is to make sure that the passed string holds the correct ESCape string to direct to the printer.

Nic;o)
Dec 25 '06 #7

alpnz
100+
P: 113
Hi alpnz,

When the printer accepts commands in the send string it will work.
The code above uses the CHR(27) being the ESCape character to direct the command to the printer and all you need is to make sure that the passed string holds the correct ESCape string to direct to the printer.

Nic;o)
I finally have had time to have a dabble at this sort of solution for labels.
One interesting aspect of Thermal printers, is the Command Code set they usually employ.
Essentially it would be possible to
Open a txt file, and print each line of commands, then cmd.exe copy the txt file to the COM port.
The txt file method in my view should make setting variables much more logical, often the printers limit you to a certain number of variables. I have managed a static text file, and the print speed is lightning fast compared to whatever M$ do with data in the print spooler. (1 second delay between click and print) compared to about 20 seconds using the spooler.
The ability to setup the text file is not entirely clear to me. I presumed you simply open a file As #1 and then print each line to #1 however I am sure, that in your line of business, you have a better way,
is that So?
Jan 4 '07 #8

Expert 5K+
P: 8,434
... the print speed is lightning fast compared to whatever M$ do with data in the print spooler. (1 second delay between click and print) compared to about 20 seconds using the spooler. ...
To be fair, there are various options that let you determine how the spooling works, and they probably affect the speed. But even so, yes it does tend to be a bit slow.
Jan 4 '07 #9

alpnz
100+
P: 113
To be fair, there are various options that let you determine how the spooling works, and they probably affect the speed. But even so, yes it does tend to be a bit slow.
Yep sure is, and when you are printing possibly 1000 labels through a not so industrial printer, it gets a bit frustrating ...

At the moment I have tried
Expand|Select|Wrap|Line Numbers
  1. Dim txt1 As String
  2. txt1 = "Meaningless text"
  3. Open "COM2:" For Output As #1
  4.  
  5. Print#1, "N"
  6. Print#1, "A505,135,1,1,1,0,3,N," & txt1
  7. Print#1, "P3"
  8.  
The text in the Quotes is EPL command codes for the printer. It essentially tells the printer where to print what, the last line triggers printing 3 copies. It allows for variables etc, and in fact most allow uploading a form or template, with variables in it, and then recall the template, and feed the variables line by line to the template. Total amount of data move "B&*()^ All", compared to M$ uploading fat and bloated font and graphics files, each print run.

Am I on the right track. I invisaged a situation where I upload the templates to the printer, as an open event on the splash screen of the application.

???
Jan 5 '07 #10

Post your reply

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