Where clause for opening a report

[jamjawaller]

I'm sure this has been discussed before but I'm relatively new to VB scripting. I need to open a report from a form with a where clause. Here is what I have but I'm not sure how to word the where clause or where it goes. I want the report to open where the [Index] on the form is equal to the [Index] on the report.

Private Sub cmdPrintProposal_Click()
Dim rptContract As Access.Report
Dim strReportName As String
Dim aoAccessObj As AccessObject
DoCmd.OpenReport "rptContract", acViewPreview, WHERE
Forms![frmContract]![Index] = [Index]
End Sub

...please feel to make inputs on the proper code. Thanks in advance.

JAM

 

[THWatson]

JAM
You're close. One thing you need is an extra comma after acViewPreview (that omits a filter, unless you have one). Also, the Index comes first in the clause.

Here's the structure, from one of my forms. The last part in quotation marks is the WHERE clause.
DoCmd.OpenReport stDocName, acPreview, , "[RecordID]=[Forms]![frmMiscellaneousDonations]![RecordID]"

So in your case, it would be something such as...
DoCmd.OpenReport "rptContract", acViewPreview, "[Index] = Forms![frmContract]![Index]"

Or it might be...
DoCmd.OpenReport "rptContract", acViewPreview, "[Index] = " & Forms![frmContract]![Index] & ""

Just thinking about the word [Index]. Is that the actual field name? I'm wondering if Index is a reserved word in Access. If so, maybe you should think about renaming it.

Hope that helps.

Tom

 

[MajP]

What THWatson said is true. But if you did not catch it, you use a sql where clause without the "Where". Also it is a string as his example shows with the " ". His first example "such as" is wrong because the string would be:
[Index] = Forms![frmContract]![Index]
You do not have a index value that is equal to a string Forms![frmContract]![Index]. His second is right because the string would evaluate to something like:
[Index] = 123

From the helpfile:

Syntax

DoCmd.OpenReport reportname[, view][, filtername][, wherecondition]

The OpenReport method has the following arguments.

Argument Description

reportname A string expression that's the valid name of
a report in the current database.

view One of the following intrinsic constants:
acViewDesign
acViewNormal (default)
acViewPreview

filtername A string expression that's the valid name of
a query in the current database.

wherecondition A string expression that's a valid SQL WHERE
clause without the word WHERE.