Applying a parameter query to several queries to create a report.

[mnsemple83]

Good evening to everyone!



I am currently working on a database system and I am trying to figure out a way to allow a user to create a report based on two values: a patient's unique identifier and their date of visit to the hospital. The purpose of this report will allow to view all patient data for that particular date.

I already created a report that consists of several subreports that were generated by queries. However, not all queries function the same. For example, I created three queries that display the 4 most recent entries prior to a date value submitted by the user. For another query, I programmed it to return all entries for a specified date and beyond.

My solution was to create a parameter query that will instruct the database to create a report based on the patient's identifier and specified date value. I just need to know how I can apply one date value in a query to several queries at the same time. (I highlighted that last sentence in bold because that is the main problem).

[June7]

Queries cannot refer to each other's input paramters. Input parameters obtained by prompting the user are next to impossible to validate. Bad input and the report still tries to run but results will be unsatisfactory.

Have user select/enter values in controls on a form. Queries refer to the controls on form as their input parameters.

[mnsemple83]

I designed a form that allows the user to enter those values. I am thinking about designing a query that will allow the user to apply those values to several fields within that query. Is that possible and how would that work?

[June7]

The user input on form is for filter criteria? In the criteria cell for each field, reference the appropriate control on the form. Syntax like:
Forms!formname!controlname

[mnsemple83]

I created my parameter query with a combo box and two buttons, applied it to the criteria section for the field I am interested in querying. However, when I run the query, the form does not open up like it should. Here is my code:

[Forms]![frmGetPatientMRN]![MRN]

What am I doing wrong?

[June7]

You have a form with a combobox and two buttons where users enter criteria and button click opens another form bound to the query with the combobox as parameter? Show the query SQL statement for analysis.

[mnsemple83]

Here is the SQL query I used:

[Forms]![frmGetPatientMRN]![MRN]

Now, I have decided that I'm going to use this form to obtain a patient's MRN before I print a report. In the database, the user is going to click a button called "Print Report", this will open the form that will prompt the user to select an MRN. After the user selects the MRN, several prompts will appear asking the user to enter parameters that will be used to create the report. Once the user has entered all the parameters, the report will appear in preview form and the user will be allowed to print.

What I want to do is to use the 'frmGetPatientFrm' to select an MRN to be used in the report. The problem I am having is getting it to retrieve the MRN from the Patient's table, which is what the report is based on. The MRN is shared by the other subreports that were created from the queries.

What methods do you suggest for accomplishing this? I already tried several.

[mnsemple83]

Here's a better understanding of what I am working with:

tblPatients - the source of the MRN (a unique identifider for each patient).
frmGetPatientMRN - the form that is used to select the MRN from tblPatients to be used in the report.
rptFullPatient - uses MRN from frmGetPatient to display data related to that patient.

I know that I have to write the code for the form in the Open event of the report. It's just a matter of trying to get it to work correctly. I will let you know if I have any other problems.

[June7]

No, I don't think Open event of report is best way to open filtered report (I have used that only once). Following are the two ways I routinely use to filter report, and No. 1 is my preference.

1. DoCmd.OpenReport "reportname here", , , "MRN=" & Me.MRN
This code would be behind an event of frmGetPatientMRN, perhaps a button click.
This code utilizes the WHERE argument of DoCmd.OpenReport and will restrict the report to retrieve records that only meet the criteria.

2. Set input parameters in the query that the report is bound to. In the criteria row of query in design view, refer to the control on frmGetPatientMRN with: Forms!frmGetPatientMRN!controlname
Now either open the report manually from the navigation pane or with code in event, such as button click.

[Forms]![frmGetPatientMRN]![MRN] is not an SQL statement, it is only a reference to form control/field. A very basic SQL statement would look like: SELECT * FROM tablename;

If you want to post an extract of relevant objects from your project (no actual data needed) I will look at.

Here is another recent thread on this topic https://www.accessforums.net/forms/r...ies-15956.html

[mnsemple83]

I changed a few things since my last post:

1. The form is no longer called "frmGetPatientMRN"; it is now called "frmReportData" because it's more descriptive.
2. The form will not only get a patient's MRN but allow the user to enter a specific date for the data that they are interested in. This date value will be used in two subqueries.

Also, the report I have created is not based on a query, but on the Patient's table ("tblPatients"). The MRN field in that table is obtained by the subqueries that are used to create the subreports. Should I be using a query instead or will this work with the tables as well?

[June7]

Option 1 can work with table as report RecordSource.

Option 2 requires a query as report RecordSource.

[mnsemple83]

I was able to get the report to display data based on the MRN that was submitted through the form, but now I am trying to get the queries for the subreports to display data based on the date value entered on the form. I tried manipulating the subreport by writing an SQL query in the VBA for the form and that did not work. What I am trying to say is that I want to use this value in the queries that create the subreports within the main report.

Any ideas?

[mnsemple83]

I have discovered a solution to the problem.

What I did with the date value is that I entered the following code in the date field for several queries:

[Forms]![frmReportData]![DateOfVisit]

Now, when I enter a date value in the form, it applies to all the queries. I wonder why it didn't work before.

Thanks June7 for your advice. It defintiely helped!

- mnsemple83

[June7]

How did you envision manipulating subreport by writing an SQL query in VBA?

[mnsemple83]

How did you envision manipulating subreport by writing an SQL query in VBA?

I was planning on writing a SELECT query inside the OnClick Event for the "OK" button. That way, the data will be retrieved by the database and used in the report.