Report based on Recordset

[Andre Delahaye]

Hello,

For an MDB database, I would like to base a report based
on Query 'PatientFollowUp-Dev':

PARAMETERS dteBegin DateTime, dteEnd DateTime;
SELECT WorkDay.ChiroID, [Chiro Name].ChiroName,
WorkDay.WorkDay, WorkDay.PatientID, PatientList.LastName
FROM (WorkDay INNER JOIN PatientList ON WorkDay.PatientID
= PatientList.PatientID) INNER JOIN [Chiro Name] ON
WorkDay.ChiroID = [Chiro Name].ChiroID
WHERE (((WorkDay.WorkDay) Between [dteBegin] And
[dteEnd]))
GROUP BY WorkDay.ChiroID, [Chiro Name].ChiroName,
WorkDay.WorkDay, WorkDay.PatientID, PatientList.LastName
ORDER BY WorkDay.ChiroID, WorkDay.PatientID;

The report has the following code on the OnOpen Event:

Option Compare Database
Option Explicit

Dim dbsReport As Database
Dim rstReport As Recordset

Private Sub Report_Close()
rstReport.Close
End Sub

Private Sub Report_Open(Cancel As Integer)
Dim qdf As QueryDef
Dim prmBegin As Parameter
Dim prmEnd As Parameter

Set dbsReport = CurrentDb
Set qdf = dbsReport.QueryDefs("PatientFollowUp-Dev")

Set prmBegin = qdf.Parameters("[dteBegin]")
Set prmEnd = qdf.Parameters("[dteEnd]")
prmBegin = #3/1/2004#
prmEnd = #3/7/2004#

Set rstReport = qdf.OpenRecordset()
End Sub

The Recordsource property of the Report is set
to 'PatientFollowUp-Dev'.

When I try to open the report, the Recordset shows the
correct quantity of records (checked in Debug mode), but
the report requests the 2 parameters dteBegin and dteEnd
via a dialog box nonetheless.

I would really appreciate any help as I use nearly the
same code for parameters based on Form controls and that
works fine.

Thank you very much.

Andre

 

[Duane Hookom]

Can you explain why you would need to go through all this code when there
are easier, standard methods of filtering reports? You could just set the
filter property.

 

[Andre Delahaye]

Duane,

My real problem is more complex than what I have
explained here, but if I can get this code working, then
I should be fine for the rest hopefully.
Later, the report will be actually a sub-report and the
parameters based on 2 controls in the main report. There
is also a third parametre that I need to introduce, and
that would make the filter not possible.

Any suggestion on my code would be really appreciated.

Andre

-----Original Message-----
Can you explain why you would need to go through all this code when there
are easier, standard methods of filtering reports? You could just set the
filter property.

--
Duane Hookom
MS Access MVP
--

Hello,

For an MDB database, I would like to base a report based
on Query 'PatientFollowUp-Dev':

PARAMETERS dteBegin DateTime, dteEnd DateTime;
SELECT WorkDay.ChiroID, [Chiro Name].ChiroName,
WorkDay.WorkDay, WorkDay.PatientID, PatientList.LastName
FROM (WorkDay INNER JOIN PatientList ON WorkDay.PatientID
= PatientList.PatientID) INNER JOIN [Chiro Name] ON
WorkDay.ChiroID = [Chiro Name].ChiroID
WHERE (((WorkDay.WorkDay) Between [dteBegin] And
[dteEnd]))
GROUP BY WorkDay.ChiroID, [Chiro Name].ChiroName,
WorkDay.WorkDay, WorkDay.PatientID, PatientList.LastName
ORDER BY WorkDay.ChiroID, WorkDay.PatientID;

The report has the following code on the OnOpen Event:

Option Compare Database
Option Explicit

Dim dbsReport As Database
Dim rstReport As Recordset

Private Sub Report_Close()
rstReport.Close
End Sub

Private Sub Report_Open(Cancel As Integer)
Dim qdf As QueryDef
Dim prmBegin As Parameter
Dim prmEnd As Parameter

Set dbsReport = CurrentDb
Set qdf = dbsReport.QueryDefs("PatientFollowUp- Dev")

Set prmBegin = qdf.Parameters("[dteBegin]")
Set prmEnd = qdf.Parameters("[dteEnd]")
prmBegin = #3/1/2004#
prmEnd = #3/7/2004#

Set rstReport = qdf.OpenRecordset()
End Sub

The Recordsource property of the Report is set
to 'PatientFollowUp-Dev'.

When I try to open the report, the Recordset shows the
correct quantity of records (checked in Debug mode), but
the report requests the 2 parameters dteBegin and dteEnd
via a dialog box nonetheless.

I would really appreciate any help as I use nearly the
same code for parameters based on Form controls and that
works fine.

Thank you very much.

Andre

.

 

[Duane Hookom]

I don't think you understand that setting the record source of a subreport
adds even more issues. You are much better off hard-coding the references to
form or report controls into your subreport's record source.

--
Duane Hookom
MS Access MVP

Duane,

My real problem is more complex than what I have
explained here, but if I can get this code working, then
I should be fine for the rest hopefully.
Later, the report will be actually a sub-report and the
parameters based on 2 controls in the main report. There
is also a third parametre that I need to introduce, and
that would make the filter not possible.

Any suggestion on my code would be really appreciated.

Andre

-----Original Message-----
Can you explain why you would need to go through all this code when there
are easier, standard methods of filtering reports? You could just set the
filter property.

--
Duane Hookom
MS Access MVP
--

Hello,

For an MDB database, I would like to base a report based
on Query 'PatientFollowUp-Dev':

PARAMETERS dteBegin DateTime, dteEnd DateTime;
SELECT WorkDay.ChiroID, [Chiro Name].ChiroName,
WorkDay.WorkDay, WorkDay.PatientID, PatientList.LastName
FROM (WorkDay INNER JOIN PatientList ON WorkDay.PatientID
= PatientList.PatientID) INNER JOIN [Chiro Name] ON
WorkDay.ChiroID = [Chiro Name].ChiroID
WHERE (((WorkDay.WorkDay) Between [dteBegin] And
[dteEnd]))
GROUP BY WorkDay.ChiroID, [Chiro Name].ChiroName,
WorkDay.WorkDay, WorkDay.PatientID, PatientList.LastName
ORDER BY WorkDay.ChiroID, WorkDay.PatientID;

The report has the following code on the OnOpen Event:

Option Compare Database
Option Explicit

Dim dbsReport As Database
Dim rstReport As Recordset

Private Sub Report_Close()
rstReport.Close
End Sub

Private Sub Report_Open(Cancel As Integer)
Dim qdf As QueryDef
Dim prmBegin As Parameter
Dim prmEnd As Parameter

Set dbsReport = CurrentDb
Set qdf = dbsReport.QueryDefs("PatientFollowUp- Dev")

Set prmBegin = qdf.Parameters("[dteBegin]")
Set prmEnd = qdf.Parameters("[dteEnd]")
prmBegin = #3/1/2004#
prmEnd = #3/7/2004#

Set rstReport = qdf.OpenRecordset()
End Sub

The Recordsource property of the Report is set
to 'PatientFollowUp-Dev'.

When I try to open the report, the Recordset shows the
correct quantity of records (checked in Debug mode), but
the report requests the 2 parameters dteBegin and dteEnd
via a dialog box nonetheless.

I would really appreciate any help as I use nearly the
same code for parameters based on Form controls and that
works fine.

Thank you very much.

Andre

.