Passing parameter to a sub report

[byurow]

I have a report for patient assessment data. This report is filtered based on the AssessmentID and PatientID that the user selects on a form (They select the Assessment date the rest is done behind the scenes). I need to somehow get any hospitalization data that occurred between the date selected and the previous assessment. I am currently able to do this on the form (thanks to help from the query forum), but am having trouble translating this to the report. On the form, I have a list box that gets populated through a query with a parameter. The query is actually a query built on a query. The queries are as follows:

QryAssessmentRange:

SELECT A.PatientID, A.AssessmentDateTime AS LastDT, (Select MAX(B.AssessmentDateTime) FROM tblAssessment B Where B.PatientID = A.PatientID AND
B.AssessmentDateTime < A.AssessmentDateTime) AS PrevDT
FROM tblAssessment AS A;


QryFindCurrentHospitalVisit:

PARAMETERS [AssessDTStart] DateTime;
SELECT H.PatientID, H.HospitalVisitDateTime, H.HospitalVisitID, H.HospitalID, H.HospitalVisitReason, H.HospitalDiagnosis, H.HospitalTreatingPhysician, H.HospitalDischargeDateTime, H.HospitalTreatment, H.HospitalComment, H.EnteredBy, H.EnteredDateTime, TblHospital.HospitalName
FROM TblHospital INNER JOIN (TblHospitalVisit AS H INNER JOIN QryAssessmentRange AS A ON H.PatientID = A.PatientID) ON TblHospital.HospitalID = H.HospitalID
WHERE (((H.HospitalVisitDateTime) Between [A].[PrevDT] And [A].[LastDT]) AND ((A.LastDT)=[AssessDTStart]));


Is it possible to pass the parameter (AssessDTStart) to the subreport? If yes, how do I do that?????????

Hope this makes sense!

Thanks,

Brenda

 

[Steve101]

If the parameter is entered by the user on a control on a form, then providing the form REMAINS OPEN at the time (though it can be hidden), the control can be referenced in a query or a report (or subreport), using the syntax:

Forms!frmYourForm!YourControl

In your report, you could simply reference the above by code, or make it the controlsource of a report's control; in this case you would need to prefix it with an = symbol.

Hope this helps,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[byurow]

Hey Steve,

Thanks for the reply. It is clear that I am trying to make this much more complicated than it needs to be!

However, after having spent a couple of hours trying to figure out where to put the reference, I still cannot get it to work!

The form that calls the report does remain open when the client prints the report. My question is: Where do I reference the parameter? I think I am just being stupid or hard headed or both! (Maybe I am just frustrated with this whole project and am ready to finish it!!!!!!) Regardless, I can't seem to find the location of where to put the reference! The form I am using to call the report is an unbound form. The code to call the report is as follows:

Dim stDocName As String

stDocName = &quot;RtpNursingAssessment&quot;
DoCmd.OpenReport stDocName, acPreview, _
WhereCondition:=&quot;AssessmentID = &quot; & Me![NumAssessmentID]

The report and it's subreport are bound reports. I am linking the reports based on the PatientID. Where should I reference the parameter? I am really lost on this and feel so stupid as I know I am missing the obvious!

Please help before I throw my computer out of the window!!!!

Thanks!

Brenda

 

[Steve101]

Brenda, I would suggest that you put the form control reference in the underlying recordsource of the main report. If this is a query, it should look something like this:

SELECT *
FROM tblYourTable
WHERE AssessmentId = Forms!frmYourForm!NumAssessmentId

If there is already a WHERE clause in the SQL, then simply add this other constraint.

Hope this helps; otherwise post the recordsource SQL for the main report, and the name of the form, and its control that you're using to constrain the records, and I'll take a further look.



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[byurow]

Steve,
Sorry for the delay in responding, my cable modem has been down Anyway, I still can't get it to work. I tried putting the where clause on both the main report and on the subreport and it still prompts me for the parameter. In fact, when I put the where clause on the main report, my entire report blows up. The report no longer fills any data. The following are the queries I am using:

The query that powers the main report is as follows:

SELECT [TblAssessment].[AssessmentID], [TblAssessment].[PatientID], [TblPatientInfo].[PatientLastName]+&quot;, &quot;+[TblPatientInfo].[PatientFirstName]+&quot; &quot;+Nz([TblPatientInfo].[PatientMiddleName]) AS PatientName, [TblPatientInfo].[PatientDOB], [TblPatientInfo].[PatientGender], [TblPatientInfo].[AdmissionDate], [TblFacilityInfo].[FacilityName], [TblFacilityInfo].[FacilityAddress1]+&quot; &quot;+Nz([TblFacilityInfo].[FacilityAddress2]) AS FacilityAddress, [TblFacilityInfo].[FacilityCity]+&quot;, &quot;+[TblFacilityInfo].[FacilityState]+&quot; &quot;+[TblFacilityInfo].[FacilityZip] AS FacilityCityStateZip, [TblFacilityInfo].[FacilityPhonePrimary], [TblFacilityInfo].[FacilityFax], [TblFacilityInfo].[FacilityALM], [TblFacilityInfo].[ALMPhone], [TblAssessment].[AssessmentDateTime], [TblAssessment].[Weight], [TblAssessment].[BloodPressure], [TblAssessment].[Temperature], [TblAssessment].[Pulse], [TblAssessment].[Respiration], [TblAssessment].[Face], [TblAssessment].[Scalp], [TblAssessment].[RightShoulder], [TblAssessment].[LeftShoulder], [TblAssessment].[RightArm], [TblAssessment].[LeftArm], [TblAssessment].[RightHand], [TblAssessment].[LeftHand], [TblAssessment].[RightUpperLeg], [TblAssessment].[LeftUpperLeg], [TblAssessment].[RightLowerLeg], [TblAssessment].[LeftLowerLeg], [TblAssessment].[RightFoot], [TblAssessment].[LeftFoot], [TblAssessment].[Chest], [TblAssessment].[Stomach], [TblAssessment].[Back], [TblAssessment].[Buttocks], [TblAssessment].[PhysicalStatusComments], [TblCareLevel].[CareLevel]
FROM (TblFacilityInfo INNER JOIN TblPatientInfo ON [TblFacilityInfo].[FacilityID]=[TblPatientInfo].[FacilityID]) INNER JOIN ((TblCareLevel INNER JOIN QryMaxCareLevel ON [TblCareLevel].[CareLevelChangeDate]=[QryMaxCareLevel].[MaxOfCareLevelChangeDate]) INNER JOIN TblAssessment ON [TblCareLevel].[PatientID]=[TblAssessment].[PatientID]) ON [TblPatientInfo].[PatientID]=[TblAssessment].[PatientID];


The query that powers the subreport is as follows:

SELECT [QryFindCurrentHospitalVisit].[PatientID], [QryFindCurrentHospitalVisit].[HospitalVisitDateTime], [QryFindCurrentHospitalVisit].[HospitalVisitReason], [QryFindCurrentHospitalVisit].[HospitalDiagnosis], [QryFindCurrentHospitalVisit].[HospitalTreatingPhysician], [QryFindCurrentHospitalVisit].[HospitalDischargeDateTime], [QryFindCurrentHospitalVisit].[HospitalTreatment], [QryFindCurrentHospitalVisit].[HospitalComment], [QryFindCurrentHospitalVisit].[EnteredBy], [QryFindCurrentHospitalVisit].[EnteredDateTime], [QryFindCurrentHospitalVisit].[HospitalName] FROM QryFindCurrentHospitalVisit;

QryFindCurrentHospitalVisit is as follows:

PARAMETERS [AssessDTStart] DateTime;
SELECT H.PatientID, H.HospitalVisitDateTime, H.HospitalVisitID, H.HospitalID, H.HospitalVisitReason, H.HospitalDiagnosis, H.HospitalTreatingPhysician, H.HospitalDischargeDateTime, H.HospitalTreatment, H.HospitalComment, H.EnteredBy, H.EnteredDateTime, TblHospital.HospitalName
FROM TblHospital INNER JOIN (TblHospitalVisit AS H INNER JOIN QryAssessmentRange AS A ON H.PatientID = A.PatientID) ON TblHospital.HospitalID = H.HospitalID
WHERE (((H.HospitalVisitDateTime) Between [A].[PrevDT] And [A].[LastDT]) AND ((A.LastDT)=[AssessDTStart]));

And finally, query QryAssessmentRange is as follows:

SELECT A.PatientID, A.AssessmentDateTime AS LastDT, (Select MAX(B.AssessmentDateTime) FROM tblAssessment B Where B.PatientID = A.PatientID AND
B.AssessmentDateTime < A.AssessmentDateTime) AS PrevDT
FROM tblAssessment AS A;


The parameter: AssessDTStart should get it's data from: FrmEditViewNursingAssessment CboLastAssessmentDateTime.Column(1) I even created a text box called AssessmentDateTime on the form and had it fill in with the date the user selected then tried to reference that from the report. No luck at all! The where clause I tried using was:

WHERE ((([AssessDTStart])=[Forms]![FrmEditViewNursingAssessment]!AssessmentDateTime));

I tried the clause on both the main report and the subreport (both independently and at the same time) with no luck. I got a syntax error when trying to reference the combo box.

Thanks again for all your help, I really appreciate it!

Brenda

 

[Steve101]

Brenda, thats quite a handful you've posted above; Whilst Im sure that the problem will end up being quite minor, I can't resolve it right now. If its not too big, zip up a small version of the database (remove any sensitive data) email it to me and I'll have a look.



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[byurow]

Steve,

I sent you the db. I have deleted all of the forms and reports that are not needed for this problem. Unfortunately, zipped, the file is still just above 2 mb. Hope that's not a problem. If need be, I can delete all of the tables and queries that aren't needed also. Let me know if you need me to.

Thanks again for the help. I really do appreciate it!

Brenda