filter a form (by record) to print preview or print report

[imrosie]

Hello,
I've gone through the tutorials, and searched. Still I can't find a clear solution to my issue.

I have a form with a subform that displays a listbox control called 'theOrderID'.
I have set it up (height 4") so that a long list of "saved" orders displays on the Form. The navigation works, so a person can scroll through the list of past orders.
I'm attempting to set up a way for the customer to search by date. (filter the past orders) and also to preview the order selected, and finally print.

I've got the code worked out for the listbox. The filter by date sort of worked out with a valuelist called 'filter' (today, this week, lastweek, etc.) and a macro.
I'm having the most difficulty with the preview and print.

The main form is bound to query "Invoices"; shows 3 fields (in valuelist), Customer Name, OrderID and OrderDate.

I have a Preview Button that calls a subroutine "Preview_Click---

Private Sub Preview_Click()
Private Sub Preview_Click()
    Dim strDocName As String
    Dim strLinkCriteria As String
 
On Error GoTo ErrorHandler
 
    If IsNull(OrderID) Then
        MsgBox "Please click an order in the list.", vbInformation
    Else
        Me.Visible = False
        strDocName = "Invoice"
        strLinkCriteria = "OrderID = " & OrderID
        DoCmd.OpenReport strDocName, acViewPreview, strLinkCriteria
    End If
 
    Exit Sub
 
ErrorHandler:
    MsgBox "Error#:  " & Err.Number & vbCrLf & Err.Description
End Sub
 

I can't get it to work. Invoice is a Report I created. thanks for any directions.

Rosie

[kcachaler]

Hello,
I've gone through the tutorials, and searched. Still I can't find a clear solution to my issue.

I have a form with a subform that displays a listbox control called 'theOrderID'.
I have set it up (height 4") so that a long list of "saved" orders displays on the Form. The navigation works, so a person can scroll through the list of past orders.
I'm attempting to set up a way for the customer to search by date. (filter the past orders) and also to preview the order selected, and finally print.

I've got the code worked out for the listbox. The filter by date sort of worked out with a valuelist called 'filter' (today, this week, lastweek, etc.) and a macro.
I'm having the most difficulty with the preview and print.

The main form is bound to query "Invoices"; shows 3 fields (in valuelist), Customer Name, OrderID and OrderDate.

I have a Preview Button that calls a subroutine "Preview_Click---

Private Sub Preview_Click()
Private Sub Preview_Click()
    Dim strDocName As String
    Dim strLinkCriteria As String
 
On Error GoTo ErrorHandler
 
    If IsNull(OrderID) Then
        MsgBox "Please click an order in the list.", vbInformation
    Else
        Me.Visible = False
        strDocName = "Invoice"
        strLinkCriteria = "OrderID = " & OrderID
        DoCmd.OpenReport strDocName, acViewPreview, strLinkCriteria
    End If
 
    Exit Sub
 
ErrorHandler:
    MsgBox "Error#:  " & Err.Number & vbCrLf & Err.Description
End Sub
 

I can't get it to work. Invoice is a Report I created. thanks for any directions.

Rosie

-----------------------------------------------------------------

Try this:

strLinkCriteria = "OrderID = " & Me.OrderID

instead of

strLinkCriteria = "OrderID = " & OrderID

[imrosie]

-----------------------------------------------------------------

Try this:

strLinkCriteria = "OrderID = " & Me.OrderID

instead of

strLinkCriteria = "OrderID = " & OrderID

Hello Kcachaler,

I think that may be one I did try, but I may be wrong....so I'll give it a try. thanks so much.
Rosie

[Scott Price]

Hello Kcachaler,

I think that may be one I did try, but I may be wrong....so I'll give it a try. thanks so much.
Rosie

You'll likely need to do the same thing to your If IsNull() line... i.e. reference it to Me.OrderID instead of just OrderID (unless OrderID is a variable that you have declared elsewhere and are simply referencing here?)

Regards,
Scott

[Scott Price]

Just noticed also that your lines 1 and 2 are duplicates, you'll need to delete one of them.

Regards,
Scott

p.s. if you have more trouble, please elaborate on the statement, "I can't get it to work"... It's a little broad! :-)

[imrosie]

Just noticed also that your lines 1 and 2 are duplicates, you'll need to delete one of them.

Regards,
Scott

p.s. if you have more trouble, please elaborate on the statement, "I can't get it to work"... It's a little broad! :-)

OK Scott, I'm back, like a nightmare....I still can't get this thing to open an invoice.
Elaboration:

My form called PrintPreviewInvoices(PPI), is unbound. It contains a Listbox called thelistbox, that is based on row source table/query called Orderlist, (thelistbox has 3 columns; customer's name, orderid, orderdate). The bound column is 2 (orderid). This shows a list of past Orders in the Window.
There are also 3 command buttons on the PPI form; preview, print and cancel.
The preview button opens a predefined report called 'Invoice'. The goal is to have someone select and order and then to preview or print the order in the 'invoice' format. Invoice Reprot is bound to a query (invoices) as follows:

Invoice query (SQL):

SELECT Customers.CompanyName, [FirstName] & " " & [LastName] AS [Customer Name], Orders.OrderID, Orders.OrderDate, Orders.PurchaseOrderNumber, Orders.ShipDate, Orders.ShipMethodID, Customers.CustomerID, Orders.FreightCharge, Orders.SalesTaxRate, Orders.PurchaseOrderNumber, Customers.BillingAddress, Customers.City, Customers.ZIPCode, Customers.StateOrProvince, Customers.Country, Customers.PhoneNumber, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipStateOrProvince, Orders.ShipCountry, Customers.ZIPCode, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, Products.ProductName, [Order Details].ProductID
 

The Preview button code:

Private Sub Preview_Click()
Dim strDocName As String
Dim strLinkCriteria As String
On Error GoTo ErrorHandler
 
If IsNull(Me.OrderID) Then
MsgBox "Please click an order in the list.", vbInformation
Else
Me.Visible = False
strDocName = "Invoice"
strLinkCriteria = "OrderID = " & Me.OrderID
DoCmd.OpenReport strDocName, acViewPreview, strLinkCriteria
End If
 
Exit Sub
ErrorHandler:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
 

Next is the Print button:

Private Sub Print_Click()
    Dim strDocName As String
    Dim strCriteria As String
    Const conUserCancelledPrint = 2501
 
On Error GoTo ErrorHandler
 
    If IsNull(Me.[Orderlist].OrderID) Then
        MsgBox "Please select an invoice from the list.", vbInformation
    Else
        Me.Visible = False
        strDocName = "Invoice"
        strCriteria = "[OrderID]= " & Me![OrderID]
       'strLinkCriteria = "OrderID = " & OrderID
        DoCmd.OpenReport strDocName, acViewNormal, , strCriteria
    End If
 
    Exit Sub
 
ErrorHandler:
    If Err.Number <> conUserCancelledPrint Then
        MsgBox "Error#:  " & Err.Number & vbCrLf & Err.Description
    End If
 

I'm a newbie, but I think somewhere I should have the term 'thelistbox' in there. maybe that's the confusion. thanks and I hope I haven't overwhelmed you with the "elaboration".
Rosie

[Scott Price]

SELECT Customers.CompanyName, [FirstName] & " " & [LastName] AS [Customer Name], Orders.OrderID, Orders.OrderDate, Orders.PurchaseOrderNumber, Orders.ShipDate, Orders.ShipMethodID, Customers.CustomerID, Orders.FreightCharge, Orders.SalesTaxRate, Orders.PurchaseOrderNumber, Customers.BillingAddress, Customers.City, Customers.ZIPCode, Customers.StateOrProvince, Customers.Country, Customers.PhoneNumber, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipStateOrProvince, Orders.ShipCountry, Customers.ZIPCode, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, Products.ProductName, [Order Details].ProductID

In this query, where are you setting the criteria that you want to see on the report?

like you say, it should capture the OrderID from the listbox that you are using to choose the order, right?

I think something along the lines of what you're looking for is a WHERE OrderID = thelistbox.Value statement to refine your query to just the one order you want to print (hope I'm understanding what you're wanting to do correctly!)

Regards,
Scott

[imrosie]

SELECT Customers.CompanyName, [FirstName] & " " & [LastName] AS [Customer Name], Orders.OrderID, Orders.OrderDate, Orders.PurchaseOrderNumber, Orders.ShipDate, Orders.ShipMethodID, Customers.CustomerID, Orders.FreightCharge, Orders.SalesTaxRate, Orders.PurchaseOrderNumber, Customers.BillingAddress, Customers.City, Customers.ZIPCode, Customers.StateOrProvince, Customers.Country, Customers.PhoneNumber, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipStateOrProvince, Orders.ShipCountry, Customers.ZIPCode, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, Products.ProductName, [Order Details].ProductID

In this query, where are you setting the criteria that you want to see on the report?

like you say, it should capture the OrderID from the listbox that you are using to choose the order, right?

I think something along the lines of what you're looking for is a WHERE OrderID = thelistbox.Value statement to refine your query to just the one order you want to print (hope I'm understanding what you're wanting to do correctly!)

Regards,
Scott

Yes Scott, you do....that's exactly what I want to do...I tried a where statement, but I don't think I set it right,,,,so I took it out. But now reading what you've written, makes sense....

I'll give that a try and post back. thanks
Rosie

[imrosie]

Yes Scott, you do....that's exactly what I want to do...I tried a where statement, but I don't think I set it right,,,,so I took it out. But now reading what you've written, makes sense....

I'll give that a try and post back. thanks
Rosie

Hi Scott,

I set it to this:

WHERE Orders.OrderID = thelistbox.Value ORDER BY Customers.CustomerID;

Now when I attempt to open the PPI (PrintPreviewInvoices),,,it's asking me to give it a value. Normally when I open the form, the list is already there and I can simply select an order...any suggestions?

[Scott Price]

Hi Scott,

I set it to this:

WHERE Orders.OrderID = thelistbox.Value ORDER BY Customers.CustomerID;

Now when I attempt to open the PPI (PrintPreviewInvoices),,,it's asking me to give it a value. Normally when I open the form, the list is already there and I can simply select an order...any suggestions?

the PPI you are referring to is the report, or the form? In your second sentence you mention form. You have these two based on separate queries or one query?

Just to clarify, base the form on a query without WHERE criteria. Base the report on a separate query (it can be an exact copy of the first if you like, but make sure it's separate) The WHERE clause will go in the query for the report, not the form.

Regards,
Scott

[Scott Price]

Just making sure also that you have the right syntax in place in your WHERE statement? If I remember right you said this is on your subform? This will require the whole name in your WHERE statement, something like this: Forms![YourMainForm].[YourSubform].Form![ListBoxName].Value

Regards,
Scott

[imrosie]

Just making sure also that you have the right syntax in place in your WHERE statement? If I remember right you said this is on your subform? This will require the whole name in your WHERE statement, something like this: Forms![YourMainForm].[YourSubform].Form![ListBoxName].Value

Regards,
Scott

Scott hi, a slip of the typing....I didn't mean subform, actually 'thelistbox' is a control on the PrintPreviewInvoice form, it's not a subform. thanks

Rosie

[imrosie]

the PPI you are referring to is the report, or the form? In your second sentence you mention form. You have these two based on separate queries or one query?

Just to clarify, base the form on a query without WHERE criteria. Base the report on a separate query (it can be an exact copy of the first if you like, but make sure it's separate) The WHERE clause will go in the query for the report, not the form.

Regards,
Scott

Hi Scott,
The 'PrintPreviewInvoices(PPI)' is the form with the listbox, this form isn't bound, however the 'listbox' control on it is bound to the query Orderlist. The Report is bound to an Invoices query. Here is the Select query (with the where clause I tried) on the Report query called Invoices:

SELECT Customers.CompanyName, [FirstName] & " " & [LastName] AS [Customer Name], Orders.OrderID, Orders.OrderDate, Orders.PurchaseOrderNumber, Orders.ShipDate, Orders.ShipMethodID, Customers.CustomerID, Orders.FreightCharge, Orders.SalesTaxRate, Orders.PurchaseOrderNumber, Customers.BillingAddress, Customers.City, Customers.ZIPCode, Customers.StateOrProvince, Customers.Country, Customers.PhoneNumber, Customers.ShipName, Customers.ShipAddress, Customers.ShipCity, Customers.ShipStateOrProvince, Customers.ShipCountry, Customers.ShipZIPCode, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, Products.ProductName, [Order Details].ProductID
FROM (Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID) INNER JOIN (Products INNER JOIN [Order Details] ON Products.ProductID=[Order Details].ProductID) ON Orders.OrderID=[Order Details].OrderID WHERE Orders.OrderID = thelistbox.Value ORDER BY Customers.CustomerID;
 

Obviously I'm doing something wrong....what's glaring at you? thanks
Rosie

[Scott Price]

Hi Scott,
The 'PrintPreviewInvoices(PPI)' is the form with the listbox, this form isn't bound, however the 'listbox' control on it is bound to the query Orderlist. The Report is bound to an Invoices query. Here is the Select query (with the where clause I tried) on the Report query called Invoices:

SELECT Customers.CompanyName, [FirstName] & " " & [LastName] AS [Customer Name], Orders.OrderID, Orders.OrderDate, Orders.PurchaseOrderNumber, Orders.ShipDate, Orders.ShipMethodID, Customers.CustomerID, Orders.FreightCharge, Orders.SalesTaxRate, Orders.PurchaseOrderNumber, Customers.BillingAddress, Customers.City, Customers.ZIPCode, Customers.StateOrProvince, Customers.Country, Customers.PhoneNumber, Customers.ShipName, Customers.ShipAddress, Customers.ShipCity, Customers.ShipStateOrProvince, Customers.ShipCountry, Customers.ShipZIPCode, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, Products.ProductName, [Order Details].ProductID
FROM (Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID) INNER JOIN (Products INNER JOIN [Order Details] ON Products.ProductID=[Order Details].ProductID) ON Orders.OrderID=[Order Details].OrderID WHERE Orders.OrderID = thelistbox.Value ORDER BY Customers.CustomerID;
 

Obviously I'm doing something wrong....what's glaring at you? thanks
Rosie

Hi Rosie,

Just to be clear in my mind here again...

The query that populates the listbox is the one that's giving you the message asking for a value?

Does that other query have the same WHERE criteria as the query you have posted here?

I think I understood you saying that opening the form w/ listbox populated by the separate query, now asks you for a value...

Does it also do so on opening the report query after making a selection in the listbox?

What is the SQL for the listbox query?

Cheers!

Scott

[imrosie]

Hi Rosie,

Just to be clear in my mind here again...

The query that populates the listbox is the one that's giving you the message asking for a value?

Does that other query have the same WHERE criteria as the query you have posted here?

I think I understood you saying that opening the form w/ listbox populated by the separate query, now asks you for a value...

Does it also do so on opening the report query after making a selection in the listbox?

What is the SQL for the listbox query?

Cheers!

Scott

Hi Scott,
No, the query giving me the message is the Invoices query that supports the Invoice Report. When I run the report, it's asking for a value for the listbox. I believe your last post suggested that's where to place the 'Where' statement.

Here is the query for the listbox, it's called Orderlist:

SELECT DISTINCTROW [FirstName] & " " & [LastName] AS [Customer Name], Orders.OrderID, Orders.OrderDate, Customers.CustomerID

That's all it does in filll the listbox with all the past Orders.
No, there is no 'Where' statement in it as you can see.The Orderlist query supports the PrintPreviewInvoices Form (PPI) Are you suggesting that I put it in there as well? The Preview cmd button on the PPI form, is a 'Click' event, as well as the Print cmd button.

Does that clear things up more?
Rosie

[Scott Price]

Hi Scott,
No, the query giving me the message is the Invoices query that supports the Invoice Report. When I run the report, it's asking for a value for the listbox. I believe your last post suggested that's where to place the 'Where' statement.

Here is the query for the listbox, it's called Orderlist:

SELECT DISTINCTROW [FirstName] & " " & [LastName] AS [Customer Name], Orders.OrderID, Orders.OrderDate, Customers.CustomerID

That's all it does in filll the listbox with all the past Orders.
No, there is no 'Where' statement in it as you can see.The Orderlist query supports the PrintPreviewInvoices Form (PPI) Are you suggesting that I put it in there as well? The Preview cmd button on the PPI form, is a 'Click' event, as well as the Print cmd button.

Does that clear things up more?

Rosie

Thanks, Rosie, yes that clears things a little more...

No need to put the WHERE criteria in the listbox query. That would just break something else that we'd have to fix later :-)

Make sure in this line of your report query that you refer to thelistbox using it's full name: Forms![YourFormName].thelistbox.value

WHERE Orders.OrderID = thelistbox.Value

Should be:

WHERE Orders.OrderID = Forms![YourFormName].thelistbox.Value

Let me know if this works!

Regards,
Scott

[imrosie]

Thanks, Rosie, yes that clears things a little more...

No need to put the WHERE criteria in the listbox query. That would just break something else that we'd have to fix later :-)

Make sure in this line of your report query that you refer to thelistbox using it's full name: Forms![YourFormName].thelistbox.value

WHERE Orders.OrderID = thelistbox.Value

Should be:

WHERE Orders.OrderID = Forms![YourFormName].thelistbox.Value

Let me know if this works!

Regards,
Scott

Scott this is why you're the expert....YES it works....thanks for sticking with me..
Rosie

[Scott Price]

Scott this is why you're the expert....YES it works....thanks for sticking with me..
Rosie

Glad it works Rosie :-)

Good luck on the rest of your app.

Regards,
Scott