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

Printing from a query / recordset.

P: 30
I have a query which I view through a form. Due to problems with the label wizard printing to a dot-matrix printer I have some code to print out a single label when a command button is clicked. This works fine.

However I would like to print all the records from the query on to consecutive labels.

Below is the code for this function but it doesn't work (surprise, surprise). I probably don't need the form in the code as it is looking at the query. Can someone give me some advice please? Many thanks.

Expand|Select|Wrap|Line Numbers
  1. Public Function fcmdPrint3_Click()
  2. On Error GoTo Err_cmdPrint3_Click
  3. Dim t_module As String
  4. Dim t_Name As String
  5. Dim t_ADDRESS1 As String
  6. Dim t_ADDRESS2 As String
  7. Dim t_ADDRESS3 As String
  8. Dim t_POSTCODE
  9. Set t_module = [Forms]![Shipping Labels]![txtmodule]
  10. Set t_Name = [Forms]![Shipping Labels]![txtname]
  11. Set t_ADDRESS1 = [Forms]![Shipping Labels]![txtaddress1]
  12. Set t_ADDRESS2 = [Forms]![Shipping Labels]![txtaddress2]
  13. Set t_ADDRESS3 = [Forms]![Shipping Labels]![txtaddress3]
  14. Set t_POSTCODE = [Forms]![Shipping Labels]![txtPostcode]
  15. Dim rst As Recordset
  16. Set rst = db.openrecordset("Daily_Shipping_Query")
  17. With rst
  18. Do Until rst.EOF 
  19. Open "LPT1:" For Output As #1    
  20.         Print #1, t_module
  21.         Print #1, t_Name
  22.         Print #1, t_ADDRESS1
  23.         Print #1, t_ADDRESS2
  24.         Print #1, t_ADDRESS3
  25.         Print #1, t_POSTCODE                
  26. rst.MoveNext
  27. Loop
  28. Close #1
  29. rst.MoveFirst
  30. Close
  31. End With
  32. Exit_cmdPrint3_Click:
  33.     Exit Function
  34. Err_cmdPrint3_Click:
  35.     MsgBox Err.Description
  36.     Resume Exit_cmdPrint3_Click    
  37. End Function
Jul 24 '07 #1
Share this Question
Share on Google+
11 Replies


FishVal
Expert 2.5K+
P: 2,653
I have a query which I view through a form. Due to problems with the label wizard printing to a dot-matrix printer I have some code to print out a single label when a command button is clicked. This works fine.

However I would like to print all the records from the query on to consecutive labels.

Below is the code for this function but it doesn't work (surprise, surprise). I probably don't need the form in the code as it is looking at the query. Can someone give me some advice please? Many thanks.

Expand|Select|Wrap|Line Numbers
  1. Public Function fcmdPrint3_Click()
  2. On Error GoTo Err_cmdPrint3_Click
  3. Dim t_module As String
  4. Dim t_Name As String
  5. Dim t_ADDRESS1 As String
  6. Dim t_ADDRESS2 As String
  7. Dim t_ADDRESS3 As String
  8. Dim t_POSTCODE
  9. Set t_module = [Forms]![Shipping Labels]![txtmodule]
  10. Set t_Name = [Forms]![Shipping Labels]![txtname]
  11. Set t_ADDRESS1 = [Forms]![Shipping Labels]![txtaddress1]
  12. Set t_ADDRESS2 = [Forms]![Shipping Labels]![txtaddress2]
  13. Set t_ADDRESS3 = [Forms]![Shipping Labels]![txtaddress3]
  14. Set t_POSTCODE = [Forms]![Shipping Labels]![txtPostcode]
  15. Dim rst As Recordset
  16. Set rst = db.openrecordset("Daily_Shipping_Query")
  17. With rst
  18. Do Until rst.EOF 
  19. Open "LPT1:" For Output As #1    
  20.         Print #1, t_module
  21.         Print #1, t_Name
  22.         Print #1, t_ADDRESS1
  23.         Print #1, t_ADDRESS2
  24.         Print #1, t_ADDRESS3
  25.         Print #1, t_POSTCODE                
  26. rst.MoveNext
  27. Loop
  28. Close #1
  29. rst.MoveFirst
  30. Close
  31. End With
  32. Exit_cmdPrint3_Click:
  33.     Exit Function
  34. Err_cmdPrint3_Click:
  35.     MsgBox Err.Description
  36.     Resume Exit_cmdPrint3_Click    
  37. End Function
Hi.

Operator Set is used to set reference to object variable. For variables declared as strings it will certainly not work. (code lines 9-14)
What is Close (code line 30) supposed to close?
Jul 24 '07 #2

P: 30
Hi.

Operator Set is used to set reference to object variable. For variables declared as strings it will certainly not work. (code lines 9-14)
What is Close (code line 30) supposed to close?
Hi, that close is in there by error by the look of it. I've removed it and also changed the string to variant. When I run it it says "object required."

Here's how the code looks now:
Expand|Select|Wrap|Line Numbers
  1. Public Function fcmdPrint3_Click()
  2. On Error GoTo Err_cmdPrint3_Click
  3. Dim t_module, t_Name, t_ADDRESS1, t_ADDRESS2, t_ADDRESS3, t_POSTCODE
  4. Set t_module = [Forms]![Shipping Labels]![txtmodule]
  5. Set t_Name = [Forms]![Shipping Labels]![txtname]
  6. Set t_ADDRESS1 = [Forms]![Shipping Labels]![txtaddress1]
  7. Set t_ADDRESS2 = [Forms]![Shipping Labels]![txtaddress2]
  8. Set t_ADDRESS3 = [Forms]![Shipping Labels]![txtaddress3]
  9. Set t_POSTCODE = [Forms]![Shipping Labels]![txtPostcode]
  10. Dim rst As Recordset
  11. Set rst = db.openrecordset("Daily_Shipping_Query")
  12. With rst
  13. Do Until rst.EOF 
  14. Open "LPT1:" For Output As #1    
  15.         Print #1, t_module
  16.         Print #1, t_Name
  17.         Print #1, t_ADDRESS1
  18.         Print #1, t_ADDRESS2
  19.         Print #1, t_ADDRESS3
  20.         Print #1, t_POSTCODE                
  21. rst.MoveNext
  22. Loop
  23. Close #1
  24. rst.MoveFirst
  25. End With
  26. Exit_cmdPrint3_Click:
  27.     Exit Function
  28. Err_cmdPrint3_Click:
  29.     MsgBox Err.Description
  30.     Resume Exit_cmdPrint3_Click
  31.     End Function
Thanks for your help
Jul 24 '07 #3

FishVal
Expert 2.5K+
P: 2,653
Hi, that close is in there by error by the look of it. I've removed it and also changed the string to variant. When I run it it says "object required."

Here's how the code looks now:
Expand|Select|Wrap|Line Numbers
  1. Public Function fcmdPrint3_Click()
  2. On Error GoTo Err_cmdPrint3_Click
  3. Dim t_module, t_Name, t_ADDRESS1, t_ADDRESS2, t_ADDRESS3, t_POSTCODE
  4. Set t_module = [Forms]![Shipping Labels]![txtmodule]
  5. Set t_Name = [Forms]![Shipping Labels]![txtname]
  6. Set t_ADDRESS1 = [Forms]![Shipping Labels]![txtaddress1]
  7. Set t_ADDRESS2 = [Forms]![Shipping Labels]![txtaddress2]
  8. Set t_ADDRESS3 = [Forms]![Shipping Labels]![txtaddress3]
  9. Set t_POSTCODE = [Forms]![Shipping Labels]![txtPostcode]
  10. Dim rst As Recordset
  11. Set rst = db.openrecordset("Daily_Shipping_Query")
  12. With rst
  13. Do Until rst.EOF 
  14. Open "LPT1:" For Output As #1    
  15.         Print #1, t_module
  16.         Print #1, t_Name
  17.         Print #1, t_ADDRESS1
  18.         Print #1, t_ADDRESS2
  19.         Print #1, t_ADDRESS3
  20.         Print #1, t_POSTCODE                
  21. rst.MoveNext
  22. Loop
  23. Close #1
  24. rst.MoveFirst
  25. End With
  26. Exit_cmdPrint3_Click:
  27.     Exit Function
  28. Err_cmdPrint3_Click:
  29.     MsgBox Err.Description
  30.     Resume Exit_cmdPrint3_Click
  31.     End Function
Thanks for your help
I'd like to bring your back to my previous post.
Operator Set is used to set reference to object variable. For variables declared as strings it will certainly not work. (code lines 9-14)

If you want to assign to string variables (t_xxx in your code) values from form controls, then use the following syntax
Expand|Select|Wrap|Line Numbers
  1. t_Name = [Forms]![Shipping Labels]![txtname]
or if the code is located in [Shipping Labels] form module simply
Expand|Select|Wrap|Line Numbers
  1. t_Name = Me![txtname]
Jul 24 '07 #4

P: 30
I'd like to bring your back to my previous post.
Operator Set is used to set reference to object variable. For variables declared as strings it will certainly not work. (code lines 9-14)

If you want to assign to string variables (t_xxx in your code) values from form controls, then use the following syntax
Expand|Select|Wrap|Line Numbers
  1. t_Name = [Forms]![Shipping Labels]![txtname]
or if the code is located in [Shipping Labels] form module simply
Expand|Select|Wrap|Line Numbers
  1. t_Name = Me![txtname]
OK thanks for that. I'll make some changes and come back to you.
Jul 24 '07 #5

P: 30
I'd like to bring your back to my previous post.
Operator Set is used to set reference to object variable. For variables declared as strings it will certainly not work. (code lines 9-14)

If you want to assign to string variables (t_xxx in your code) values from form controls, then use the following syntax
Expand|Select|Wrap|Line Numbers
  1. t_Name = [Forms]![Shipping Labels]![txtname]
or if the code is located in [Shipping Labels] form module simply
Expand|Select|Wrap|Line Numbers
  1. t_Name = Me![txtname]
I've changed the code so it now looks like this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Ship_labels()
  2. Dim db As DAO.Database
  3. Dim rs As DAO.Recordset
  4. Set db = CurrentDb()
  5. Set rs = db.OpenRecordset("Daily Shipping Query", dbOpenDynaset)
  6. rs.MoveFirst
  7. Do
  8. Open "LPT1:" For Output As #1
  9.         Print #1, rs![Module]
  10.         Print #1, ""
  11.         Print #1, rs![Name]
  12.         Print #1, rs![Address 1]
  13.         Print #1, rs![Address 2]
  14.         Print #1, rs![Address 3]
  15.         Print #1, rs![Postcode]
  16.         Print #1, ""
  17.         Print #1, ""
  18. Close #1
  19. rs.MoveNext
  20. Loop Until rs.EOF
  21. End Sub
I'm now getting the following error message:
Run-time error '52':
Bad file name or number

When I debug it highlights 'Open "LPT1:" For Output As #1'
Any idea how I can stop this?
Jul 26 '07 #6

FishVal
Expert 2.5K+
P: 2,653
I've changed the code so it now looks like this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Ship_labels()
  2. Dim db As DAO.Database
  3. Dim rs As DAO.Recordset
  4. Set db = CurrentDb()
  5. Set rs = db.OpenRecordset("Daily Shipping Query", dbOpenDynaset)
  6. rs.MoveFirst
  7. Do
  8. Open "LPT1:" For Output As #1
  9.         Print #1, rs![Module]
  10.         Print #1, ""
  11.         Print #1, rs![Name]
  12.         Print #1, rs![Address 1]
  13.         Print #1, rs![Address 2]
  14.         Print #1, rs![Address 3]
  15.         Print #1, rs![Postcode]
  16.         Print #1, ""
  17.         Print #1, ""
  18. Close #1
  19. rs.MoveNext
  20. Loop Until rs.EOF
  21. End Sub
I'm now getting the following error message:
Run-time error '52':
Bad file name or number

When I debug it highlights 'Open "LPT1:" For Output As #1'
Any idea how I can stop this?
Hmm.

On my computer "LPT1:" is being opened without error. I can't check whether i can send to LPT1 bkz I have no printer connected to parallel interface.

Well, I will ask community to help you.
Jul 26 '07 #7

NeoPa
Expert Mod 15k+
P: 31,299
Isn't #1 already taken as a standard port.
The recommended way to get a number is to use FreeFile(). This may not be causing your problem but it's worth doing anyway (and maybe it is).
Jul 26 '07 #8

P: 30
Isn't #1 already taken as a standard port.
The recommended way to get a number is to use FreeFile(). This may not be causing your problem but it's worth doing anyway (and maybe it is).
Ok. I haven't come across FreeFile() before but I'll look into it and let you know how I get on. Thanks for your help.
Jul 31 '07 #9

NeoPa
Expert Mod 15k+
P: 31,299
No problem.
I hope it helps but at worst it's just the proper way of finding file numbers.
More info can be found by typing FreeFile & pressing F1. You can even do this in the Immediate Pane (Ctrl-G) if you like.
Jul 31 '07 #10

P: 30
No problem.
I hope it helps but at worst it's just the proper way of finding file numbers.
More info can be found by typing FreeFile & pressing F1. You can even do this in the Immediate Pane (Ctrl-G) if you like.
It's only taken a month but I've finally got around to replying.

I used Freefile as you suggested and it's all working perfectly now so thanks for your help.
Aug 22 '07 #11

NeoPa
Expert Mod 15k+
P: 31,299
It's only taken a month but I've finally got around to replying.

I used Freefile as you suggested and it's all working perfectly now so thanks for your help.
No problem ;)
I'm a little surprised (but pleased) that it actually fixed the problem, but it is certainly worth doing anyway.

Strangely, since I got involved in this thread I've used the file accessing system within VBA to develop a nifty little app for updating my CivIII save files (Sshh - don't tell anyone).
Aug 22 '07 #12

Post your reply

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