G
gaugust
In Access 2003 I would like to format my subreport output so that the values
for a data field with medication names prints horizontally across the page
seperated by commas for one patient id/page:
value1, value2, value3, valuen
The report contains 3 other subreports that show other details related to a
patient.
The user of the report would like to be able to generate the report for one,
five or all patients in the database where each page would be a seperate
report for each patient. The problem I have is creating the subreport when
more than one patient is selected. I have tried the following two solutions
but neither have worked:
1. I created a crosstab query so that each record contains a patient id
followed by the names of the medications (could be 0, 1 or more) that patient
takes. I created a recordset based on the crosstab query and then used the
following code to populate an unbound text box in the subreport. I linked the
subreport to the report based on the master/child field screening id. I
expected that the values for the medications for one patient/page would print
due to the master/child relationship but I got an error message that you
can't use a non fixed column crosstab query as a record source for a
subreport.
If Not rst.BOF And Not rst.EOF Then
For Each fld In rst.Fields
If fld.Name <> "ScreeningId" Then
If Not IsNull(fld.Value) Then
strDrugs = strDrugs & fld.Value & ", "
End If
End If
Next fld
End If
rst.Close
Me.txtAsthmaDrugs = strDrugs
2. The other solution I tried was to use a select query as the source for
the recordset and loop through the records for each patient to generate the
string of medication names to populate an unbound text box on the subreport.
The problem with this solution is that since the user selected to run the
report for all patients the recordset contains all patient records. I don't
know of a way to pass a parameter into the code as a filter so that only the
records for the current patient id that the report will be printed for is
read to generate the string with the medication names.
Setting the master/child relationship on the subreport would not prevent
other patient records from being read.
Is there a change that I could make to either of these solutions to get this
to work or is there another solution that would work? Thanks.
for a data field with medication names prints horizontally across the page
seperated by commas for one patient id/page:
value1, value2, value3, valuen
The report contains 3 other subreports that show other details related to a
patient.
The user of the report would like to be able to generate the report for one,
five or all patients in the database where each page would be a seperate
report for each patient. The problem I have is creating the subreport when
more than one patient is selected. I have tried the following two solutions
but neither have worked:
1. I created a crosstab query so that each record contains a patient id
followed by the names of the medications (could be 0, 1 or more) that patient
takes. I created a recordset based on the crosstab query and then used the
following code to populate an unbound text box in the subreport. I linked the
subreport to the report based on the master/child field screening id. I
expected that the values for the medications for one patient/page would print
due to the master/child relationship but I got an error message that you
can't use a non fixed column crosstab query as a record source for a
subreport.
If Not rst.BOF And Not rst.EOF Then
For Each fld In rst.Fields
If fld.Name <> "ScreeningId" Then
If Not IsNull(fld.Value) Then
strDrugs = strDrugs & fld.Value & ", "
End If
End If
Next fld
End If
rst.Close
Me.txtAsthmaDrugs = strDrugs
2. The other solution I tried was to use a select query as the source for
the recordset and loop through the records for each patient to generate the
string of medication names to populate an unbound text box on the subreport.
The problem with this solution is that since the user selected to run the
report for all patients the recordset contains all patient records. I don't
know of a way to pass a parameter into the code as a filter so that only the
records for the current patient id that the report will be printed for is
read to generate the string with the medication names.
Setting the master/child relationship on the subreport would not prevent
other patient records from being read.
Is there a change that I could make to either of these solutions to get this
to work or is there another solution that would work? Thanks.