Printing from a query / recordset.

[fieldling]

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.

Public Function fcmdPrint3_Click()
On Error GoTo Err_cmdPrint3_Click
Dim t_module As String
Dim t_Name As String
Dim t_ADDRESS1 As String
Dim t_ADDRESS2 As String
Dim t_ADDRESS3 As String
Dim t_POSTCODE
Set t_module = [Forms]![Shipping Labels]![txtmodule]
Set t_Name = [Forms]![Shipping Labels]![txtname]
Set t_ADDRESS1 = [Forms]![Shipping Labels]![txtaddress1]
Set t_ADDRESS2 = [Forms]![Shipping Labels]![txtaddress2]
Set t_ADDRESS3 = [Forms]![Shipping Labels]![txtaddress3]
Set t_POSTCODE = [Forms]![Shipping Labels]![txtPostcode]
Dim rst As Recordset
Set rst = db.openrecordset("Daily_Shipping_Query")
With rst
Do Until rst.EOF 
Open "LPT1:" For Output As #1    
        Print #1, t_module
        Print #1, t_Name
        Print #1, t_ADDRESS1
        Print #1, t_ADDRESS2
        Print #1, t_ADDRESS3
        Print #1, t_POSTCODE                
rst.MoveNext
Loop
Close #1
rst.MoveFirst
Close
End With
Exit_cmdPrint3_Click:
    Exit Function
Err_cmdPrint3_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrint3_Click    
End Function

[FishVal]

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.

Public Function fcmdPrint3_Click()
On Error GoTo Err_cmdPrint3_Click
Dim t_module As String
Dim t_Name As String
Dim t_ADDRESS1 As String
Dim t_ADDRESS2 As String
Dim t_ADDRESS3 As String
Dim t_POSTCODE
Set t_module = [Forms]![Shipping Labels]![txtmodule]
Set t_Name = [Forms]![Shipping Labels]![txtname]
Set t_ADDRESS1 = [Forms]![Shipping Labels]![txtaddress1]
Set t_ADDRESS2 = [Forms]![Shipping Labels]![txtaddress2]
Set t_ADDRESS3 = [Forms]![Shipping Labels]![txtaddress3]
Set t_POSTCODE = [Forms]![Shipping Labels]![txtPostcode]
Dim rst As Recordset
Set rst = db.openrecordset("Daily_Shipping_Query")
With rst
Do Until rst.EOF 
Open "LPT1:" For Output As #1    
        Print #1, t_module
        Print #1, t_Name
        Print #1, t_ADDRESS1
        Print #1, t_ADDRESS2
        Print #1, t_ADDRESS3
        Print #1, t_POSTCODE                
rst.MoveNext
Loop
Close #1
rst.MoveFirst
Close
End With
Exit_cmdPrint3_Click:
    Exit Function
Err_cmdPrint3_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrint3_Click    
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?

[fieldling]

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:

Public Function fcmdPrint3_Click()
On Error GoTo Err_cmdPrint3_Click
Dim t_module, t_Name, t_ADDRESS1, t_ADDRESS2, t_ADDRESS3, t_POSTCODE
Set t_module = [Forms]![Shipping Labels]![txtmodule]
Set t_Name = [Forms]![Shipping Labels]![txtname]
Set t_ADDRESS1 = [Forms]![Shipping Labels]![txtaddress1]
Set t_ADDRESS2 = [Forms]![Shipping Labels]![txtaddress2]
Set t_ADDRESS3 = [Forms]![Shipping Labels]![txtaddress3]
Set t_POSTCODE = [Forms]![Shipping Labels]![txtPostcode]
Dim rst As Recordset
Set rst = db.openrecordset("Daily_Shipping_Query")
With rst
Do Until rst.EOF 
Open "LPT1:" For Output As #1    
        Print #1, t_module
        Print #1, t_Name
        Print #1, t_ADDRESS1
        Print #1, t_ADDRESS2
        Print #1, t_ADDRESS3
        Print #1, t_POSTCODE                
rst.MoveNext
Loop
Close #1
rst.MoveFirst
End With
Exit_cmdPrint3_Click:
    Exit Function
Err_cmdPrint3_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrint3_Click
    End Function

Thanks for your help

[FishVal]

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:

Public Function fcmdPrint3_Click()
On Error GoTo Err_cmdPrint3_Click
Dim t_module, t_Name, t_ADDRESS1, t_ADDRESS2, t_ADDRESS3, t_POSTCODE
Set t_module = [Forms]![Shipping Labels]![txtmodule]
Set t_Name = [Forms]![Shipping Labels]![txtname]
Set t_ADDRESS1 = [Forms]![Shipping Labels]![txtaddress1]
Set t_ADDRESS2 = [Forms]![Shipping Labels]![txtaddress2]
Set t_ADDRESS3 = [Forms]![Shipping Labels]![txtaddress3]
Set t_POSTCODE = [Forms]![Shipping Labels]![txtPostcode]
Dim rst As Recordset
Set rst = db.openrecordset("Daily_Shipping_Query")
With rst
Do Until rst.EOF 
Open "LPT1:" For Output As #1    
        Print #1, t_module
        Print #1, t_Name
        Print #1, t_ADDRESS1
        Print #1, t_ADDRESS2
        Print #1, t_ADDRESS3
        Print #1, t_POSTCODE                
rst.MoveNext
Loop
Close #1
rst.MoveFirst
End With
Exit_cmdPrint3_Click:
    Exit Function
Err_cmdPrint3_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrint3_Click
    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

t_Name = [Forms]![Shipping Labels]![txtname]

or if the code is located in [Shipping Labels] form module simply

t_Name = Me![txtname]

[fieldling]

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

t_Name = [Forms]![Shipping Labels]![txtname]

or if the code is located in [Shipping Labels] form module simply

t_Name = Me![txtname]

OK thanks for that. I'll make some changes and come back to you.

[fieldling]

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

t_Name = [Forms]![Shipping Labels]![txtname]

or if the code is located in [Shipping Labels] form module simply

t_Name = Me![txtname]

I've changed the code so it now looks like this:

Private Sub Ship_labels()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Daily Shipping Query", dbOpenDynaset)
rs.MoveFirst
Do
Open "LPT1:" For Output As #1
        Print #1, rs![Module]
        Print #1, ""
        Print #1, rs![Name]
        Print #1, rs![Address 1]
        Print #1, rs![Address 2]
        Print #1, rs![Address 3]
        Print #1, rs![Postcode]
        Print #1, ""
        Print #1, ""
Close #1
rs.MoveNext
Loop Until rs.EOF
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?

[FishVal]

I've changed the code so it now looks like this:

Private Sub Ship_labels()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Daily Shipping Query", dbOpenDynaset)
rs.MoveFirst
Do
Open "LPT1:" For Output As #1
        Print #1, rs![Module]
        Print #1, ""
        Print #1, rs![Name]
        Print #1, rs![Address 1]
        Print #1, rs![Address 2]
        Print #1, rs![Address 3]
        Print #1, rs![Postcode]
        Print #1, ""
        Print #1, ""
Close #1
rs.MoveNext
Loop Until rs.EOF
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.

[NeoPa]

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).

[fieldling]

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.

[NeoPa]

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.

[fieldling]

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.

[NeoPa]

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).