OpenReport method with a Form criteria

[Guest]

Need help please. I am trying to open a report that is based off of a query.
I want to open the report and pass the query for that report its criteria
from 2 fields on my form. The form name is Form1, the two fields are FName
and LName. The report is called Search.

This is what I have so far when I run the report using a macro and set the
Where condition:
[dbo_vDocProperty Query]![PrincipalFirstName] Like [Forms]![Form1]![FName]
And [dbo_vDocProperty Query]![lastname] Like [Forms]![Form1]![LName] And
[dbo_vDocProperty Query]![cparty] = "B"

How would I set the up using the DoCmd.OpenReport method instead of the macro.

Thanks for you help.

 

[Ken Snell \(MVP\)]

Watch for line wrapping - this should be one line of code:

DoCmd.OpenReport "ReportName", , , "[dbo_vDocProperty
Query]![PrincipalFirstName] Like [Forms]![Form1]![FName]
And [dbo_vDocProperty Query]![lastname] Like [Forms]![Form1]![LName] And
[dbo_vDocProperty Query]![cparty] = 'B'"

 

[Guest]

Why is would it be that when this criteria is sent from the form to the
report it literally take 10 minutes to pull the report. When I put the
criteria directly into the query, it takes about 3 seconds to display the
query results.

Watch for line wrapping - this should be one line of code:

DoCmd.OpenReport "ReportName", , , "[dbo_vDocProperty
Query]![PrincipalFirstName] Like [Forms]![Form1]![FName]
And [dbo_vDocProperty Query]![lastname] Like [Forms]![Form1]![LName] And
[dbo_vDocProperty Query]![cparty] = 'B'"

--

Ken Snell
<MS ACCESS MVP>

Need help please. I am trying to open a report that is based off of a
query.
I want to open the report and pass the query for that report its criteria
from 2 fields on my form. The form name is Form1, the two fields are FName
and LName. The report is called Search.

This is what I have so far when I run the report using a macro and set the
Where condition:
[dbo_vDocProperty Query]![PrincipalFirstName] Like [Forms]![Form1]![FName]
And [dbo_vDocProperty Query]![lastname] Like [Forms]![Form1]![LName] And
[dbo_vDocProperty Query]![cparty] = "B"

How would I set the up using the DoCmd.OpenReport method instead of the
macro.

Thanks for you help.

 

[Guest]

Look at "Applying Filter" thread that was posted yesterday you can see my
answer and I think it will help you with your problem. Let me know if you
need more help.

Why is would it be that when this criteria is sent from the form to the
report it literally take 10 minutes to pull the report. When I put the
criteria directly into the query, it takes about 3 seconds to display the
query results.

Watch for line wrapping - this should be one line of code:

DoCmd.OpenReport "ReportName", , , "[dbo_vDocProperty
Query]![PrincipalFirstName] Like [Forms]![Form1]![FName]
And [dbo_vDocProperty Query]![lastname] Like [Forms]![Form1]![LName] And
[dbo_vDocProperty Query]![cparty] = 'B'"

--

Ken Snell
<MS ACCESS MVP>

Need help please. I am trying to open a report that is based off of a
query.
I want to open the report and pass the query for that report its criteria
from 2 fields on my form. The form name is Form1, the two fields are FName
and LName. The report is called Search.

This is what I have so far when I run the report using a macro and set the
Where condition:
[dbo_vDocProperty Query]![PrincipalFirstName] Like [Forms]![Form1]![FName]
And [dbo_vDocProperty Query]![lastname] Like [Forms]![Form1]![LName] And
[dbo_vDocProperty Query]![cparty] = "B"

How would I set the up using the DoCmd.OpenReport method instead of the
macro.

Thanks for you help.

 

[Ken Snell \(MVP\)]

Difference probably is this:

When you put the criteria in the query, you filter the results of that
query. When you put the criteria in the call to the report, the full query
runs, then the report filters the query's results. Thus, the query returns
all records and then is filtered, rather than filtering the records and then
returning records from the query.

--

Ken Snell
<MS ACCESS MVP>

Why is would it be that when this criteria is sent from the form to the
report it literally take 10 minutes to pull the report. When I put the
criteria directly into the query, it takes about 3 seconds to display the
query results.

Watch for line wrapping - this should be one line of code:

DoCmd.OpenReport "ReportName", , , "[dbo_vDocProperty
Query]![PrincipalFirstName] Like [Forms]![Form1]![FName]
And [dbo_vDocProperty Query]![lastname] Like [Forms]![Form1]![LName] And
[dbo_vDocProperty Query]![cparty] = 'B'"

--

Ken Snell
<MS ACCESS MVP>

Need help please. I am trying to open a report that is based off of a
query.
I want to open the report and pass the query for that report its
criteria
from 2 fields on my form. The form name is Form1, the two fields are
FName
and LName. The report is called Search.

This is what I have so far when I run the report using a macro and set
the
Where condition:
[dbo_vDocProperty Query]![PrincipalFirstName] Like
[Forms]![Form1]![FName]
And [dbo_vDocProperty Query]![lastname] Like [Forms]![Form1]![LName]
And
[dbo_vDocProperty Query]![cparty] = "B"

How would I set the up using the DoCmd.OpenReport method instead of the
macro.

Thanks for you help.

 

[Guest]

Below I have it pass to only one report, what if I want to pass it to all
reports?

Public Sub List0_DblClick(Cancel As Integer)
mychoice = List0.Value
If Not (IsNull(Me![Beginning Entry Date])) Then
mystart = Me![Beginning Entry Date]
End If
If Not (IsNull(Me![Ending Entry Date])) Then
mystop = Me![Ending Entry Date]
End If
DoCmd.OpenReport List0.Value, acViewPreview
If mystart = "" Or mystop = "" Then
Reports![Cingular by Division].Filter = ""
Else
If mystart > mystop Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Entry Date"
Exit Sub
End If

Reports![Cingular by Division].Filter = "[period end date] between cdate('"
& mystart & "') AND cdate('" & mystop & "')"
End If

DoCmd.Close acForm, "Range Filter"

End Sub

 

[Ken Snell \(MVP\)]

I'm sorry, I'm not understanding your question. Pass what to which reports?
The values of mystart and mystop variables to the reports? Where do you get
the list of reports to be opened?

 

[Ken Snell \(MVP\)]

An EXCEL file must be open if you wish to run a macro within that EXCEL
file.

Here is a generic VBA subroutine that shows how to run such a macro:

Public Sub RunAnExcelMacro()
Dim xls As Object, xwkb As Object
Dim strFile As String, strMacro As String
strFile = "ExcelFilename.xls"
strMacro = "MacroName"
Set xls= CreateObject("Excel.Application")
xls.Visible = True
Set xwkb = xls.Workbooks.Open("C:\" & strFile)
xls.Run strFile & "!" & strMacro
xwkb.Close False
Set xwkb = Nothing
xls.Quit
Set xls = Nothing
End Sub