Open Report with Form

[TinaCa]

I've used the code in various responses but it's not working for me. I keep getting all records not just the one I'm requesting.



Dim strReportName As String
Dim strCriteria As String

strReportName = "rptCompanyHistory"
strCriteria = "[CompanyAccountNo] =" & Me.CompanyAccountNo

DoCmd.OpenReport strReportName, acViewPreview, strCriteria

[ajetrumpet]

is the criteria in the filter section? make sure it's in the "WHERE" clause section.

[TinaCa]

Dim strReportName As String
Dim AcctNo As Integer

strReportName = "rptCompanyHistory"
AcctNo = Me.CompanyAccountNo
strCriteria = "[CompanyAccountNo] In (" & AcctNo & ")"

DoCmd.OpenReport strReportName, acViewPreview, strCriteria

Still not getting just the one I want. Also, the other reports don't have anything in the Filter section of the report. I use this query in other places so I do not want to hard code into the query.

[RuralGuy]

From VBA Help:
expression.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)
If [CompanyAccountNo] is a string instead of a number (which is what it should be) then you need to delimit with quotes:
strReportName = "rptCompanyHistory"
strCriteria = "[CompanyAccountNo] ='" & Me.CompanyAccountNo & "'"

DoCmd.OpenReport strReportName, acViewPreview, , strCriteria
...and notice I moved to the WhereCondition argument.

[TinaCa]

unfortunately, the companyaccountNo is a primary key so must be integer. do i need to use ' instead "

[RuralGuy]

A Primary Key does not need to be an integer unless it is an AutoNumber in which case a number is just fine. Simply remove the single quotes.
strCriteria = "[CompanyAccountNo] =" & Me.CompanyAccountNo

[TinaCa]

I was missing a comma. Thanks folks

[RuralGuy]

Yep! Glad we could help.