PrintOut not working as expected.

[ukdane]

I have written some code, which opens, and filters a report.
This seems to work fine, as when I see the report on the screen, the details are filtered accordingly.

However, when I then use the DoCmd.PrintOut to actually print the report, the details are no longer filtered, and ALL the details appear to be printed.

Here's the code, I'll explain it below.
Private Sub Bttn_ProcessOrder_Click()
Dim strOrderNr As String
Dim strLabelsNr As String
Dim strFilter As String
On Error GoTo Err_Bttn_ProcessOrder_Click


If IsNull(Me.labelstoprint.Value) Then
strLabelsNr = 1
Else
strLabelsNr = Me.labelstoprint.Value
End If
If IsNull(Me.selectordernr.Value) Then
MsgBox ("Select an order.")
Exit Sub
Else
strOrderNr = "=" & Me.selectordernr.Value & ""
End If
strFilter = "[saleorderid] " & strOrderNr
DoCmd.OpenReport "rptLabels", acViewPreview
With Reports![RptLabels]
.Filter = strFilter
.FilterOn = True
End With
If SysCmd(acSysCmdGetObjectState, acReport, "rptLabels") <> acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If

DoCmd.PrintOut acSelection, , , , strLabelsNr

DoCmd.Close acReport, "RptLabels"
labelstoprint.Value = ""
selectordernr.Value = ""
Exit_Bttn_ProcessOrder_Click:
Exit Sub
Err_Bttn_ProcessOrder_Click:
MsgBox Err.Description
Resume Exit_Bttn_ProcessOrder_Click
End Sub


So the printout is generated when the button Bttn_ProcessOrder_Click is printed.
strOrderNr is the number of an order, and is what I want to filter on the report, so that only a specific order is printed out. (If no order is selected, the code ends, and the user is asked to select an order).
strLabelsNr is the number of copies of the printout I require. (This is either 1 or what the user inputs.)

DoCmd.OpenReport "rptLabels", acViewPreview
opens the report.

With Reports![RptLabels]
.Filter = strFilter
.FilterOn = True
End With
Applies the filter.


DoCmd.PrintOut acSelection, , , , strLabelsNr
Should then print the filtered report, but it's printing the report with ALL records visible.

DoCmd.Close acReport, "RptLabels"
Then closes the reports, and the following code resets the variables, and error checks.

Any help as to how I achieve what I want, or what is worng with the code is greatly appreciated.

Thanks.

[OBP]

I would aproach this compeletely differently by filtering the Query that the Report is based upon. Thereby the Report is always filtered.
The filtering can be done either using simple Forms!formname!fieldname or by using a VBA SQL generated QueryDef like this.

Dynamic Report Query
Private Sub Command2_Click()
Dim rs As Object, sql As String, qdfNew As Object
With CurrentDb
.QueryDefs.Delete "NewQueryDef"

Set qdfNew = .CreateQueryDef("NewQueryDef", _
"SELECT * FROM Categories WHERE [CategoryID]> 10 ")
DoCmd.OpenReport "Categories Query", acViewPreview
End With
End Sub

[ukdane]

Hi,
Can you explain why my code won't work?
Is it possible to get mycode to work- why is it better to filter the query?

Did you copy your code from an existing work, I'm not sure whqat you are defining many of the variables as (for example rs and sql).

Also, in your code, what are the following:
Categories
CategoryID
Categoies Query

Thanks

Edit: I tried "plugging" your code into my code above, but without much success. Any more help greatly appreciated.

The code I generated looked like this:
Private Sub Bttn_ProcessOrder_Click()
Dim rs As Object, sql As String, qdfNew As Object
Dim strOrderNr As String
Dim strLabelsNr As String
If IsNull(Me.labelstoprint.Value) Then
strLabelsNr = 1
Else
strLabelsNr = Me.labelstoprint.Value
End If
If IsNull(Me.selectordernr.Value) Then
MsgBox ("Select an order.")
Exit Sub
Else
strOrderNr = Me.selectordernr.Value
End If
With CurrentDb
'.QueryDefs.Delete "NewQueryDef"

Set qdfNew = .CreateQueryDef("NewQueryDef", _
"SELECT * FROM QryLabels WHERE [saleorderid]=" & strOrderNr)
DoCmd.OpenReport "RptLabels", acViewPreview
DoCmd.PrintOut , , , , strLabelsNr
End With

Exit_Bttn_ProcessOrder_Click:
Exit Sub
Err_Bttn_ProcessOrder_Click:
MsgBox Err.Description
Resume Exit_Bttn_ProcessOrder_Click

End Sub

But the report was not filtered.
I also got a runtime error on the line .QueryDefs.Delete "NewQueryDef"

[OBP]

You get an eror becasue you do not already have the Query called "NewQueryDef", so it can't delete it. So rem out that line or create a Query called NewQueryDef.
The Report would only be Filtered if the Report is based on the new Query called NewQueryDef.

I don't know why your code doesn't work and I don't use Report Filters, so i can't really help.
I prefer to do it in the Query because you can test the Query to ensure that you are getting the correct results.

[ukdane]

OK, got you. I've now managed to get the code above to work.

I'm now trying to do the same with another report, which uses an sql string which is a little more complicated.

If I type this as an SQL query:
SELECT TblSaleOrder.saleorderid, TblSaleOrder.saleorderdate, TblSupplier.suppliername, TblConsignee.consigneename, TblConsignee.consigneetown, TblSaleOrder.saleordernumber, TblProduct.[Product Description], TblOrderPull.productid, TblSaleOrderLine.saleorderlineunitrequired, TblOrderPull.unitstopull, [unitstopull]/TblProduct![Units Per Colli] AS Colli, Round(TblOrderPull!unitstopull/(TblProduct![Units Per Colli]*TblProduct![Colli Per Pallet])) AS Pallets, ([unitstopull]-[Pallets]*TblProduct![Colli Per Pallet]*TblProduct![Units Per Colli])/TblProduct![Units Per Colli] AS [Additional Colli], TblProduct.[Packing Type], TblArea.areatype, [unitstopull]-[saleorderlineunitrequired] AS Difference
FROM TblSupplier INNER JOIN ((TblArea INNER JOIN (TblProduct INNER JOIN ((TblOrderPull INNER JOIN TblSaleOrder ON TblOrderPull.salesorderid = TblSaleOrder.saleorderid) INNER JOIN TblConsignee ON TblSaleOrder.consigneeid = TblConsignee.consigneeid) ON TblProduct.productid = TblOrderPull.productid) ON TblArea.areaid = TblOrderPull.areaid) INNER JOIN TblSaleOrderLine ON (TblSaleOrder.saleorderid = TblSaleOrderLine.saleorderid) AND (TblProduct.productid = TblSaleOrderLine.productid)) ON (TblSupplier.supplierid = TblSaleOrder.supplierid) AND (TblSupplier.supplierid = TblProduct.Supplier) AND (TblSupplier.supplierid = TblConsignee.supplierid);

and then look at hte query in Datasheet view, I get the desired result.

However if I use the corresponding code as a string in VBA
Set qdfNew = .CreateQueryDef("QryOrderConfirmation", _
"SELECT TblSaleOrder.saleorderid, TblSaleOrder.saleorderdate, TblSupplier.suppliername, TblConsignee.consigneename, TblConsignee.consigneetown, TblSaleOrder.saleordernumber, TblProduct.[Product Description], TblOrderPull.productid, TblSaleOrderLine.saleorderlineunitrequired, TblOrderPull.unitstopull, [unitstopull]/TblProduct![Units Per Colli] AS Colli, Round(TblOrderPull!unitstopull/(TblProduct![Units Per Colli]*TblProduct![Colli Per Pallet])) AS Pallets, ([unitstopull]-[Pallets]*TblProduct![Colli Per Pallet]*TblProduct![Units Per Colli])/TblProduct![Units Per Colli] AS [Additional Colli], TblProduct.[Packing Type], TblArea.areatype, [unitstopull]-[saleorderlineunitrequired] AS Difference" & _
"FROM TblSupplier INNER JOIN ((TblArea INNER JOIN (TblProduct INNER JOIN ((TblOrderPull INNER JOIN TblSaleOrder ON TblOrderPull.salesorderid = TblSaleOrder.saleorderid) INNER JOIN TblConsignee ON TblSaleOrder.consigneeid = TblConsignee.consigneeid) ON TblProduct.productid = TblOrderPull.productid) ON TblArea.areaid = TblOrderPull.areaid) INNER JOIN TblSaleOrderLine ON (TblSaleOrder.saleorderid = TblSaleOrderLine.saleorderid) AND (TblProduct.productid = TblSaleOrderLine.productid)) ON (TblSupplier.supplierid = TblSaleOrder.supplierid) AND (TblSupplier.supplierid = TblProduct.Supplier) AND (TblSupplier.supplierid = TblConsignee.supplierid)")


and run the code, I get a "Run-time error 3141. The Select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

How can it be correct one place and not the other- what do I need to change in order for the VBA string to work?


Thanks again.

[OBP]

Unfortunately the syntax of VBA generated SQl is not the same as the Query generated SQL, don't ask me why, it is another MS anomaly.
Try this form that I downloaded from the web that converts ordinary SQL to VBA SQL.
You paste the sql in and then convert it.

[Imdabaum]

Hi,
But the report was not filtered.
I also got a runtime error on the line .QueryDefs.Delete "NewQueryDef"


Usually I enclose my QueryDef.delete or Tabledef.delete with an

'You should know whether you want a querydef.delete or a tabledef.delete
'based on what you are trying to accomplish in that block of code. So you
'shouldn't need to test what type iName is. Just lock it down where you
'need it.
If TableOrQueryExists(iName) Then
Currentdb.querydef.delete(iName) 'if your iName is of a qdef type.
Currentdb.tabledef.delete(iName) ' if your iName is a tdef type.
End if

Function TableOrQueryExists(TName As String) As Boolean
Dim Db As Database, Found As Integer, test As String
Const NAME_NOT_IN_COLLECTION = 3265
' Assume the table or query does not exist.
Found = False
' Trap for any errors.
On Error Resume Next
Set Db = CurrentDb()

' See if the name is in the Tables collection.
test = Db.TableDefs(TName).Name
If Err <> NAME_NOT_IN_COLLECTION Then Found = True
' Reset the error variable.
Err = 0
' See if the name is in the Queries collection.
test = Db.QueryDefs(TName$).Name
If Err <> NAME_NOT_IN_COLLECTION Then Found = True
Db.Close
TableOrQueryExists = Found
End Function

[Imdabaum]

Unfortunately the syntax of VBA generated SQl is not the same as the Query generated SQL, don't ask me why, it is another MS anomaly.
Try this form that I downloaded from the web that converts ordinary SQL to VBA SQL.
You paste the sql in and then convert it.

As a side note that form is brilliant OBP!!:bow: :bow: