Syntax for assigning the querystring as the recordsource of the re

[Anand Vaidya]

I know how to create a query using vba programatically. I don't know the
syntax to use this query and open the report.I tried something like this but
didn't work.Ofcourse it was a wild guess -

reports.rptSearchedRecords.RecordSource=qrySearchedRecords
(I want to put this code in the mainform frmReport's "Generate Report" click
event)

I want the syntax for assigning the query(qrySearchedRecords) as the
recordsource for the report(rptSearchedRecords)

some details-
frmReport - Main form
Subcontainer - sub form
rptSearchedRecords - Report name
qrySearchedRecords - query string

I know that if there is a single search criteria , we can use the "where
condition" argument , something like this -
DoCmd.OpenReport "rptAll", acViewPreview, , "Received_Date
between #" & strfrom & "# and #" & strto & "#" ' order by [received_date]"
but I don't know how to assign the query to docmd.openreport() function(if
at all this is the right method)

 

[Duane Hookom]

You can assign a record source in the On Open event of the report.

Me.RecordSource = "SELECT...."
or
Me.RecordSource = "qselMySavedQuery"

 

[Brendan Reynolds]

In Access 2003, the OpenReport method has an OpenArgs argument. You could
pass the name of the query in this argument, then use code in the Open event
procedure of the report to retrieve it ...

Private Sub Command0_Click()

DoCmd.OpenReport "rptTest", acViewPreview, , , , "qryTest1"

End Sub

Private Sub Report_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.RecordSource = Me.OpenArgs
End If

End Sub

This was a relatively recent addition, though. I can't remember whether it
was in Access 2002 or not, but I'm almost certain it was not in Access 2000.
(In earlier versions, the OpenForm method had the OpenArgs argument, but the
OpenReport method did not). If you need a method that is compatible with
earlier versions of Access, you can store the query name somewhere that the
report can retrieve it. You could use a global variable, a hidden text box
on the calling form, or a public property of the calling form. Here's an
example that uses a public property ...

Private m_QueryName As String

Public Property Get QueryName()
QueryName = m_QueryName
End Property

Private Sub Command0_Click()

m_QueryName = "qryTest1"
DoCmd.OpenReport "rptTest", acViewPreview

End Sub

Private Sub Report_Open(Cancel As Integer)

If CurrentProject.AllForms("frmTest").IsLoaded Then
Me.RecordSource = Forms("frmTest").QueryName
End If

End Sub

 

[Anand Vaidya]

Duane , I forgot to mention that the query I am generating is a dynamic query
and not a "static savedquery".The main form(frmReport) has different report
labels which upon clicked will display concerned subform(Subcontainer) and
each subform has different fields( search criteria) where I could select
any/all of the search criteria in the subform(subcontainer) and click
"Generate Report" button placed on the main form to generate report.
-------------
Anand Vaidya
I'm here to know.

You can assign a record source in the On Open event of the report.

Me.RecordSource = "SELECT...."
or
Me.RecordSource = "qselMySavedQuery"

--
Duane Hookom
MS Access MVP
--

I know how to create a query using vba programatically. I don't know the
syntax to use this query and open the report.I tried something like this
but
didn't work.Ofcourse it was a wild guess -

reports.rptSearchedRecords.RecordSource=qrySearchedRecords
(I want to put this code in the mainform frmReport's "Generate Report"
click
event)

I want the syntax for assigning the query(qrySearchedRecords) as the
recordsource for the report(rptSearchedRecords)

some details-
frmReport - Main form
Subcontainer - sub form
rptSearchedRecords - Report name
qrySearchedRecords - query string

I know that if there is a single search criteria , we can use the "where
condition" argument , something like this -
DoCmd.OpenReport "rptAll", acViewPreview, , "Received_Date
between #" & strfrom & "# and #" & strto & "#" ' order by [received_date]"
but I don't know how to assign the query to docmd.openreport() function(if
at all this is the right method)

 

[Duane Hookom]

You don't need a saved query. You can use a table name, query name, or sql
statement. Your pseudo code did mention "qrySearchedRecords" which seems to
be a saved query.

--
Duane Hookom
MS Access MVP
--

Duane , I forgot to mention that the query I am generating is a dynamic
query
and not a "static savedquery".The main form(frmReport) has different
report
labels which upon clicked will display concerned subform(Subcontainer) and
each subform has different fields( search criteria) where I could select
any/all of the search criteria in the subform(subcontainer) and click
"Generate Report" button placed on the main form to generate report.
-------------
Anand Vaidya
I'm here to know.

You can assign a record source in the On Open event of the report.

Me.RecordSource = "SELECT...."
or
Me.RecordSource = "qselMySavedQuery"

--
Duane Hookom
MS Access MVP
--

I know how to create a query using vba programatically. I don't know the
syntax to use this query and open the report.I tried something like
this
but
didn't work.Ofcourse it was a wild guess -

reports.rptSearchedRecords.RecordSource=qrySearchedRecords
(I want to put this code in the mainform frmReport's "Generate Report"
click
event)

I want the syntax for assigning the query(qrySearchedRecords) as the
recordsource for the report(rptSearchedRecords)

some details-
frmReport - Main form
Subcontainer - sub form
rptSearchedRecords - Report name
qrySearchedRecords - query string

I know that if there is a single search criteria , we can use the
"where
condition" argument , something like this -
DoCmd.OpenReport "rptAll", acViewPreview, , "Received_Date
between #" & strfrom & "# and #" & strto & "#" ' order by
[received_date]"
but I don't know how to assign the query to docmd.openreport()
function(if
at all this is the right method)

 

[Anand Vaidya]

Thanks Brendan,it was Bull's Eye.
This was exactly what I was looking for.

(the only thing changed is - there should be no double quotes to the qryTest1)

 

[Brendan Reynolds]

I'm not sure what you're doing, Anand, but in the code I posted the double
quotes are absolutely required. The code would not compile, let alone
execute, without them.

 

[Duane Hookom]

The quotes would not be required if qryTest1 was a string variable that
contained either a sql statement or name of a query. For those of us who use
a naming convention, this would not make sense.