command button to preview filtered report?

[Guest]

I want a command button on an orders form to preview the invoice report for
just that specific order. Using the cmd button wizard I get a button that
opens ALL invoices.

I tried adapting code (along with a new qryInvoiceFilter query) modelled on
the Print Invoice cmd button on the Northwind Orders form, but to no avail. I
got a Compile Error "Variable not defined" which highlighted
"QryInvoiceFilter" in the code.

All I know about VBA is what I'm finding out by trial and error, and I don't
really know what to try next. I could post the offending code if that would
help.

 

[Keith Wilby]

I want a command button on an orders form to preview the invoice report for
just that specific order. Using the cmd button wizard I get a button that
opens ALL invoices.

I tried adapting code (along with a new qryInvoiceFilter query) modelled
on
the Print Invoice cmd button on the Northwind Orders form, but to no
avail. I
got a Compile Error "Variable not defined" which highlighted
"QryInvoiceFilter" in the code.

All I know about VBA is what I'm finding out by trial and error, and I
don't
really know what to try next. I could post the offending code if that
would
help.

You need a way to uniquely identify the current record. Assuming you have a
field named "ID" as your unique identifier with a control "txtID" bound to
it on your form, try:

DoCmd.OpenReport "rptMyReport", acViewPreview, , "[ID] = " & Me.txtID

HTH - Keith.
www.keithwilby.com

 

[Guest]

Please post the offending code.

 

[Guest]

Posting the code may help, but here are some pointers that may help.
First, to accomplish what you are wanting to do you can use the Where
argument of the OpenReport method. The Where argument is basically and SQL
WHERE statement without the word WHERE. The basic idea is:
Field Name in the report's recordset compared to some value. For example,
lets say the field in the report's recordset is named [ORDER_NUMBER] and that
name of the control on your form where the Order Number is entered is
txtOrderNumber.
Now, we want the report to know to only include rows where the field
[ORDER_NUMBER] is that same value as the text box txtOrderNumber on the form.

The syntax to do that is:

If the field [ORDER_NUMBER] is defined as Text:
strWhere = "[ORDER_NUMBER] = '" & Me.txtOrderNumber & "'"

If the field [ORDER_NUMBER] is defined as numeric:
strWhere = "[ORDER_NUMBER] = " & Me.txtOrderNumber

Now we have a Where Condtion the report can understand. So we open the
report named InvoiceReport in Preview:

DoCmd.OpenReport "InvoiceReport", acViewPreview, , strWhere

 

[Guest]

OK, following what I can from the code you've suggested, Klatuu, this is how
I have now attempted to adapt the On Click event code created by the command
button wizard:

Private Sub cmdViewInvoice_Click()
On Error GoTo Err_cmdViewInvoice_Click

Dim stDocName As String

stDocName = "rptInvoice"
DoCmd.OpenReport stDocName, acPreview, , strWhere = "[OrderID] = " &
Me.txtOrderID


Exit_cmdViewInvoice_Click:
Exit Sub

Err_cmdViewInvoice_Click:
MsgBox Err.Description
Resume Exit_cmdViewInvoice_Click

End Sub

When I try it, I get a Compile Error "Variable not defined" which highlights
"strWhere" in the code. How close am I to getting it right?

 

[Guest]

You are very close. The error occured because strWhere has not been Dimmed.
If you look at the very top of your module, there will be a line that says:
Option Explicit
That means you must declare variables before you can use them. This is a
good thing. It is possible to omit that line and be able to create variables
without declaring them. This is a bad thing. It will cause you
unmentionable headaches, particularly if your typing is as bad as mine.
Using the Option Explicit (If it isn't there, it should be) lets me know I
have typed in a variable name incorreclty.

The fix is to put this line:
Dim strWhere as String
Right below this line in the code:
Dim stDocName As String

stDocName = "rptInvoice"

This is incorrect.
DoCmd.OpenReport stDocName, acPreview, , strWhere = "[OrderID] = " &
Me.txtOrderID

It should be:

strWhere = "[OrderID] = " & Me.txtOrderID
DoCmd.OpenReport stDocName, acPreview, , strWhere


Now, one other thing to remind you of. Since the Where condition conforms
to SQL rules, how you pass the value to compare depends on the data type of
the table or query field you are comparing against. If [OrderID] is a
numeric field, then the syntax is correct; however, if it is a text field, it
should be this:
strWhere = "[OrderID] = '" & Me.txtOrderID & "'"
(Notice the Single Quotes around Me.txtOrderID)

 

[Guest]

Thanks so much, Klatuu. Works beautifully! And even better, I'm beginning to
understand why. Really liked your clarifications.

You are very close. The error occured because strWhere has not been Dimmed.
If you look at the very top of your module, there will be a line that says:
Option Explicit
That means you must declare variables before you can use them. This is a
good thing. It is possible to omit that line and be able to create variables
without declaring them. This is a bad thing. It will cause you
unmentionable headaches, particularly if your typing is as bad as mine.
Using the Option Explicit (If it isn't there, it should be) lets me know I
have typed in a variable name incorreclty.

The fix is to put this line:
Dim strWhere as String
Right below this line in the code:
Dim stDocName As String

stDocName = "rptInvoice"

This is incorrect.
DoCmd.OpenReport stDocName, acPreview, , strWhere = "[OrderID] = " &
Me.txtOrderID

It should be:

strWhere = "[OrderID] = " & Me.txtOrderID
DoCmd.OpenReport stDocName, acPreview, , strWhere


Now, one other thing to remind you of. Since the Where condition conforms
to SQL rules, how you pass the value to compare depends on the data type of
the table or query field you are comparing against. If [OrderID] is a
numeric field, then the syntax is correct; however, if it is a text field, it
should be this:
strWhere = "[OrderID] = '" & Me.txtOrderID & "'"
(Notice the Single Quotes around Me.txtOrderID)

OK, following what I can from the code you've suggested, Klatuu, this is how
I have now attempted to adapt the On Click event code created by the command
button wizard:

Private Sub cmdViewInvoice_Click()
On Error GoTo Err_cmdViewInvoice_Click

Dim stDocName As String

stDocName = "rptInvoice"
DoCmd.OpenReport stDocName, acPreview, , strWhere = "[OrderID] = " &
Me.txtOrderID


Exit_cmdViewInvoice_Click:
Exit Sub

Err_cmdViewInvoice_Click:
MsgBox Err.Description
Resume Exit_cmdViewInvoice_Click

End Sub

When I try it, I get a Compile Error "Variable not defined" which highlights
"strWhere" in the code. How close am I to getting it right?