Creating reports that show fields from more than one table

  • Thread starter Thread starter Anthony
  • Start date Start date
A

Anthony

Hello

I am trying to create a report that shows fields from more than one table.
All of the tables are linked by a field called "PatientID" and I want the
report to show the details for a single PatientID.

How do I create this report so that the message box "Enter Parameter Value"
does not appear for each primary/foreign key (e.g., PatientID).

Thanks in advance
Anthony
 
Anthony

Create a query that joins the tables, and include the fields you want in
your report. Base the report on the query.

Good luck

Jeff Boyce
<Access MVP>
 
Hi Jeff and thanks for your help.

I created the query as you suggested and was able to produce a report
showing all of the records. How do I go about limiting the number of records
shown in the report to the patient (thru the PatientID field) which I just
entered or viewed thru the forms. The code I am currently using for the
command button in the last form (to open the report for that particular
patient) is:

Private Sub Report_Click()
On Error GoTo Err_Report_Click

Dim stDocName As String
Dim strWhere As String

strWhere = "[PatientID]=" & Me!PatientID
stDocName = "Operation Report"
DoCmd.OpenReport stDocName, acPreview

Exit_Report_Click:
Exit Sub

Err_Report_Click:
MsgBox Err.Description
Resume Exit_Report_Click

End Sub

Any suggestions?
Thanks again
Anthony
 
Me again

I seemed to have solved this particular problem (i.e., limiting the number
of records displayed in the report) with the help of similar problems that
other people have posted about. I merely added "View" to "acPreview" and ",
, strWhere" to the OpenReport command to give the following:

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

This limits the records as required, but I have another problem with the
query on which the report is based in that it does not update automatically
to include new data entered thru the forms. Any suggestions?

Anthony
 
Anthony

The query doesn't "update" until you run it. Ditto for the report.

Good luck!

Jeff Boyce
<Access MVP>
 
Have you saved the data in the form before you open the report? Try adding the
line below to your current code for the print button.

Private Sub Report_Click()
On Error GoTo Err_Report_Click

Dim stDocName As String
Dim strWhere As String

'-------------- Save Current Record -----------------
If Me.Dirty = True then Me.Dirty = False
'-------------- Save Current Record -----------------

strWhere = "[PatientID]=" & Me!PatientID
stDocName = "Operation Report"
DoCmd.OpenReport stDocName, acPreview,,strWhere

Exit_Report_Click:
Exit Sub

Err_Report_Click:
MsgBox Err.Description
Resume Exit_Report_Click

End Sub
 
Thanks for your help Jeff and John. I managed to get it working as required.

Anthony

John Spencer (MVP) said:
Have you saved the data in the form before you open the report? Try adding the
line below to your current code for the print button.

Private Sub Report_Click()
On Error GoTo Err_Report_Click

Dim stDocName As String
Dim strWhere As String

'-------------- Save Current Record -----------------
If Me.Dirty = True then Me.Dirty = False
'-------------- Save Current Record -----------------

strWhere = "[PatientID]=" & Me!PatientID
stDocName = "Operation Report"
DoCmd.OpenReport stDocName, acPreview,,strWhere

Exit_Report_Click:
Exit Sub

Err_Report_Click:
MsgBox Err.Description
Resume Exit_Report_Click

End Sub


Me again

I seemed to have solved this particular problem (i.e., limiting the number
of records displayed in the report) with the help of similar problems that
other people have posted about. I merely added "View" to "acPreview" and ",
, strWhere" to the OpenReport command to give the following:

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

This limits the records as required, but I have another problem with the
query on which the report is based in that it does not update automatically
to include new data entered thru the forms. Any suggestions?

Anthony
Click to expand...
Click to expand...
 
Back
Top