Help!! I'm running around in circles!

[CathyA]

I haven't used Access for since 1996 very much and have just undertaken a
project at work to computerise recording measurements on patients. Basically
what will need to happen is:
1 - To add a new patient
2 - To add the measurements
3 - To print a report
I can do the first step ok - I can do it either by itself - a simple add new
record - or I can do it as part of the process of recording measurements.
The second step makes the database a little more complicated - there is a
different set of measurements to be taken depending on which part of the
patient is being treated - therefore a variety of separate forms/tables are
needed to compensate. I can set these up ok.
The problem is this - I can only work out how to generate the report if I
already know the treatment area of the patient. I want to be able to do a
search of all patients as I can't assume that I or others will remember
patient specifics, then have the report generated from the appropriate
treatment information.
Does this make sense??
My questions are this:
1 - Is there a way of generating a report like this?
2 - Have I even set up the database in the most efficient way in the first
place?
Thanks

 

[Ken Snell [MVP]]

Not knowing the details of your data, the answer to (1) is "probably". If
your "measurements" data are in a separate table that is related to the
patients table, then a query could be written that returns all the
measurements for a patient. But you'll need to give us more details about
your data and table structures. And what the report is supposed to produce.

 

[CathyA]

The only thing I need to know about patients in this particular database is:
Name, ID Number and area to be treated. The rest of the information about
treatment measurements etc is in the different tables - things like position
of body, bed height etc. When I run the report I want a prompt to open to
ask for the patient ID number and then I want the report to list everything -
name, number and all treatment measurements. I've worked out how to do this
using a query related to a specific treatment table (but I didn't have a
separate patient table at the time) but this would mean that the user would
have to know which area was being treated in the first place and they may not
always know this. So I want Access to be able to search all patient records
- that's why I've now separated out the patient details - and then print the
associated treatment details.
Thanks

 

[Ed Warren]

If you are from the United States,

You might want to consider HIPAA requirements here.
Recording patient data in a database is not for the faint hearted.

(tying Pt ID , Name ) are key identifiers!

The information lacking for to provide help is the data structure of the
downstream "treatment tables". Without knowing how these are organized we
cannot provide a path to enlightnment.

Example1:
tblPatient : Primary key on PtID
tblTreatment: Primary key on TreatmentID
tblPatient (PtID(K1), firstname, lastname, treatmentArea) 1 -->M
tblTreatment (TreatmentID
(K1),PtID(F1),treatmentArea,DateTime,MeasurementType,MeasurementValue)

Leads to one query

Whereas

Example2:
tblPatient : Primary key on PtID
tblTreatmentArea1: Primary key on Treatment1_ID
tblTreatmentArea2: Primary key on Treatment2_ID
tblTreatmentArea3: Primary key on Treatment3_ID

tblPatient (PtID(K1), firstname, lastname, treatmentArea) 1 -->M
tblTreatmentArea 1(TreatmentArea1_ID
(K1),PtID(F1),DateTime,MeasurementType,MeasurementValue)
tblPatient (PtID(K1), firstname, lastname, treatmentArea) 1 -->M
tblTreatmentArea 2(TreatmentArea2_ID
(K1),PtID(F1),DateTime,MeasurementType,MeasurementValue)
tblPatient (PtID(K1), firstname, lastname, treatmentArea) 1 -->M
tblTreatmentArea 3(TreatmentArea3_ID
(K1),PtID(F1),DateTime,MeasurementType,MeasurementValue)

Leads to a completly different query.

Ed Warren

 

[Ken Snell [MVP]]

I believe what you want to use is not one query to "find" all the records,
as you state it.

Instead, use a form with various subforms on it. The main form will display
the information from the patient table (the "main" table); each subform
displays the related records from the "child" tables. Display one table in
each subform.
--

Ken Snell
<MS ACCESS MVP>

 

[CathyA]

This sounds like a good idea - a bit simpler maybe than what I was trying to
do with the reports.
I've been experimenting with forms like this using the auto form wizard.
Will this display the child form depending on what treatment area is selected
(ie a different subform if chest is selected rather than leg)? (I haven't got
very far with experimenting yet!! ). Can this be printed off easily as the
report.
One thing I noticed though is that the Wizard changes the tick boxes to
"Yes" and "No" - is there any way to keep the tick boxes etc. I assume I can
change the layout like I can in a form or report setup.
Thanks for your help.
Cathy

 

[Ken Snell [MVP]]

A subform will display on the form if it has any records. If you put more
than one subform on the form, each will display independently.

It's possible to make a subform visible or not visible if you use a macro or
VBA code to change that property of the control that holds the subform; you
can base this on a variety of possible values.

You can have subreports in a report -- they are similar to subforms on
forms.

I'm not understanding what you mean by the wizard changing the tick boxes?
Can you give me more info?

--

Ken Snell
<MS ACCESS MVP>

 

[CathyA]

OK. So how do I do that? !! This is the part that I don't fully
understand how to do in Access, so please forgive my ignorance.
What I want to be able to do when the user presses the "Print" button is this:
I want a dialog box to open and ask for the patients ID number. Then, I
want the dialog box to show extra patient details (ie name and treatment
area) based on that ID number so that the user can verify it's the right
patient. Then they say OK to print or cancel. When OK is pressed, ideally I
would like access to look at the treatment area (the three I have set up so
far is chest, pelvis and other) and then print the linked treatment setup.
What I'm basically getting at is, can I put something like an IF statement in
the coding behind the OK button that says something like IF treatment area =
chest, then print the chest report/form (whichever works best in this
situation) for this patient. Then that will open say a report based on a
query that links the patient details and chest treatment details.
Do you think this would work??? If should work, I have no idea about how
the programming side of things should be done - could you give instructions
on how to insert the IF statement - where, what it should say etc??
I think that's really the crux of what I'm after - whether an IF statement
will do this - I think I should be able to fit it into either a subreport or
subform situation.
Thanks for all your help
Cathy

 

[Ken Snell [MVP]]

From your description, it appears that you'd have a separate report form for
chest, for pelvic, and so on? You wouldn't have a single report that would
display the test results on it for all the tests?

What I'm suggesting (we'll get to code in next exchange) is that you create
a report that has a subreport for each possible test type IF they would need
different report layouts for each test. If each test can be represented by
the same report format, then just a single subreport would be needed; the
report will be able to generate separate "sections" for each test.

When you would run the report, if there are no test results for a specific
test, nothing would print for that test. This can be done using the normal
report setup and doesn't require code at all.

What you describe for a dialog box and such is straightforward and fairly
easy to implement. But before we get to that, I want to understand what you
are thinking for the report format/setup/layout.

--

Ken Snell
<MS ACCESS MVP>

 

[CathyA]

I'd assumed that I would need separate report forms because there are
different forms for each treatment. Each treatment will have a slightly
different layout because they have different numbers of fields - for example,
I think chest has about 30 fields wheras pelvis has about 20. If it can be
done on the one report with different subreports for each treatment area that
would seem to be better - so long as I can set it so that it doesn't leave
whole blank areas where the other subreports are that aren't relevant to the
patient.
Does this help you? How do you think it's best to set it all up?
Thanks,
Cathy

 

[Ken Snell [MVP]]

In a report, you can set the Can Shrink and Can Grow properties of a
subreport control to Yes (for both properties). Then, if there are no
records in the subreport to display, the control "shrinks" to zero height,
and no white space is left on the report. So stacking various subreports one
above the other (not on top of each other, meaning they don't layer over top
of each other) should do what you seek. Each subreport's Source Object
(report) has its own record source, and you can link the subreport to the
main report via the same types of "Link Child Fields" and "Link Master
Fields" method as used in subforms.

As for a "dialog" box that allows you to select the patient, that's fairly
easy to do.
Create a form that has a combo box on it and a command button on it.
Set the Row Source of the combo box to a query that gives you a list of
the patients; the query should likely contain two fields: the PatientID
field and the PatientName field. If you have multiple fields for names of
patient, you can use a calculated field to concatenate the names for a
patient and then display that.
Be sure that the combo box is set to have 2 columns and that the bound
column is column 1. The first column should have a width of 0" so that it
will not be displayed in the dropdown list nor in the combo box after
selection.
The command button should have code on its Click event that opens your
report (filtering the report based on the value selected in the combo box)
and then closes the form whose button you just clicked.

Then, in your print button's code in the original form, the code should open
the above form in dialog mode. The rest will go as you desire.
--

Ken Snell
<MS ACCESS MVP>

 

[CathyA]

So what I do is:
Set up each subreport as separate reports and then set up a "master" report
which the separate reports link into as the sub reports. - Is this right?
Set up the dialog form so user can select appropriate patient.
- I'm not quite sure what you mean about the bound column for the combo box
though - could you please explain that just a little more?
Thanks

 

[Ken Snell [MVP]]

Comments inline....
--

Ken Snell
<MS ACCESS MVP>

So what I do is:
Set up each subreport as separate reports and then set up a "master" report
which the separate reports link into as the sub reports. - Is this right?
Yes.



Set up the dialog form so user can select appropriate patient.
- I'm not quite sure what you mean about the bound column for the combo box
though - could you please explain that just a little more?

The bound column of a combo box defines which column in the row source is
the one from which the combo box draws its value. In the Properties window,
on the Data tab, you'll see this property. Bound column is one-based,
meaning that 1 is the first column, 2 the second column, etc. (Many
properties are zero-based, meaning that 0 is the first column, 1 is second
column, etc.). So, if your query returns two fields -- an ID field and a
descriptor field, for example -- you'd likely want the Bound Column to be 1
so that the ID value is the actual value of the combo box. But you can
display the descriptor field for the user's benefit.

 

[CathyA]

Thanks Ken for all your help. I don't think it would have ever occurred to
me to use sub-reports.
There's just one last thing though - with the dialog box to use to filter
the report - I have followed the instructions in Access called "Use a form to
enter report criteria" to set up the dialog box with the command buttons and
macros. The instructions say "Enter the criteria for the data. In the
expression, use the Forms object, the name of the form, and the names of the
controls in the criteria." Then it gives an example. I modified the example
so that instead of saying between certain dates, I wrote (in the criteria
section of the PatientID field of the query the report is based on):
LIKE [Forms]![PrintDialog]![UR Number]
(UR Number is the name of the field for PatientID)
When I run the report it opens with the dialog box, I select the appropriate
UR Number, press OK, then an error pops up:
"The expression you entered has a function name that CT Data Management
can't find."
And the report opens for the first patient in the patient list.
How do I fix this??
Thanks

I have linked the "Master Report" to a query that lists all patients and
their ID numbers

 

[Ken Snell [MVP]]

Is PatientID a numeric field? If yes, I'd use an expression that has =
instead of Like.

I am not directly familiar with the "instructions" that you cite, so I'm not
sure where the programming for popping up the dialog box is (in the report?)

UR Number is the name of the control on the form (named "PrintDialog"),
right? And UR Number is bound to the field PatientID on that form? What type
of form is PrintDialog?

I need a little more info about how you set up this dialog box, etc. Can you
tell me where you started in ACCESS and where you've put
code/programming/etc.? And which form or forms are open when you run the
report? And how you run the report (e.g., you click a button on a form named
.....)?
--

Ken Snell
<MS ACCESS MVP>

Thanks Ken for all your help. I don't think it would have ever occurred to
me to use sub-reports.
There's just one last thing though - with the dialog box to use to filter
the report - I have followed the instructions in Access called "Use a form to
enter report criteria" to set up the dialog box with the command buttons and
macros. The instructions say "Enter the criteria for the data. In the
expression, use the Forms object, the name of the form, and the names of the
controls in the criteria." Then it gives an example. I modified the example
so that instead of saying between certain dates, I wrote (in the criteria
section of the PatientID field of the query the report is based on):
LIKE [Forms]![PrintDialog]![UR Number]
(UR Number is the name of the field for PatientID)
When I run the report it opens with the dialog box, I select the appropriate
UR Number, press OK, then an error pops up:
"The expression you entered has a function name that CT Data Management
can't find."
And the report opens for the first patient in the patient list.
How do I fix this??
Thanks

I have linked the "Master Report" to a query that lists all patients and
their ID numbers

Comments inline....
--

Ken Snell
<MS ACCESS MVP>

right?

Yes.


combo
box

The bound column of a combo box defines which column in the row source is
the one from which the combo box draws its value. In the Properties window,
on the Data tab, you'll see this property. Bound column is one-based,
meaning that 1 is the first column, 2 the second column, etc. (Many
properties are zero-based, meaning that 0 is the first column, 1 is second
column, etc.). So, if your query returns two fields -- an ID field and a
descriptor field, for example -- you'd likely want the Bound Column to be 1
so that the ID value is the actual value of the combo box. But you can
display the descriptor field for the user's benefit.

 

[CathyA]

The field is actually called UR Number and it's a text field because even
though it's a number, the first number is usually 0 and if it's set up as a
numeric field the 0 won't show. The 0 must be there to keep things the same
across all programs.

I've set up a switchboard that has a button that says "Print". This opens
the report. The report has an "On Open" macro that will open the dialog form
(called PrintDialog). This form has a combo box on it that asks for the UR
Number (if you press the drop down arrow it displays two columns - UR Number
and Patient Name). It also has two command buttons - OK - which is supposed
to open up the report for the UR Number in the combo box. The other button
is cancel - this one doesn't work properly either.

The instructions are in the help menu of Access called "Use a form to enter
report criteria". It says to set up a form with the following properties -
DefaultView: Single Form; ViewsAllowed: Form; ScrollBars: Neither;
RecordSelectors: No; NavigationButtons: No; BorderStyle: Dialog.

It says to use a text box to enter criteria, but I used a combo as described
above with the Name property set to UR Number.

Then it says to set up four macros in a macro group:
Open Dialog - this is the OnOpen procedure for the report - opens the dialog
form
Close Dialog - this is the OnClose procedure for the report - closes the
dialog form
OK - this is attached to the OK button on the dialog form(settings are Item
- [Visible]; Expression - No)
Cancel - attached to the Cancel button on the dialog form

The report is attached to a query with only two fields - UR Number and Name.
In the criteria for UR Number I modified their example of between dates so
that it looked for a UR Number like the one in the combo box.

It all seems to work up until the moment I press OK. The report is filtered
down to one patient - unfortunately it is always the first patient on the
list, not the one selected on the form. Also, when I press the cancel
button, the form closes but then the automatic dialog box for the query is
open.

Thinking about it now, I wonder if I should just forget all that macro stuff
and just use the automatic pop-up box that the query uses - though it would
be nice for the end user to be able to double check they have entered the
right UR Number prior to the report opening (which they can do with the combo
box).

Is PatientID a numeric field? If yes, I'd use an expression that has =
instead of Like.

I am not directly familiar with the "instructions" that you cite, so I'm not
sure where the programming for popping up the dialog box is (in the report?)

UR Number is the name of the control on the form (named "PrintDialog"),
right? And UR Number is bound to the field PatientID on that form? What type
of form is PrintDialog?

I need a little more info about how you set up this dialog box, etc. Can you
tell me where you started in ACCESS and where you've put
code/programming/etc.? And which form or forms are open when you run the
report? And how you run the report (e.g., you click a button on a form named
.....)?
--

Ken Snell
<MS ACCESS MVP>

Thanks Ken for all your help. I don't think it would have ever occurred to
me to use sub-reports.
There's just one last thing though - with the dialog box to use to filter
the report - I have followed the instructions in Access called "Use a form to
enter report criteria" to set up the dialog box with the command buttons and
macros. The instructions say "Enter the criteria for the data. In the
expression, use the Forms object, the name of the form, and the names of the
controls in the criteria." Then it gives an example. I modified the example
so that instead of saying between certain dates, I wrote (in the criteria
section of the PatientID field of the query the report is based on):
LIKE [Forms]![PrintDialog]![UR Number]
(UR Number is the name of the field for PatientID)
When I run the report it opens with the dialog box, I select the appropriate
UR Number, press OK, then an error pops up:
"The expression you entered has a function name that CT Data Management
can't find."
And the report opens for the first patient in the patient list.
How do I fix this??
Thanks

I have linked the "Master Report" to a query that lists all patients and
their ID numbers

Comments inline....
--

Ken Snell
<MS ACCESS MVP>

So what I do is:
Set up each subreport as separate reports and then set up a "master"
report
which the separate reports link into as the sub reports. - Is this right?

Yes.



Set up the dialog form so user can select appropriate patient.
- I'm not quite sure what you mean about the bound column for the combo
box
though - could you please explain that just a little more?

The bound column of a combo box defines which column in the row source is
the one from which the combo box draws its value. In the Properties window,
on the Data tab, you'll see this property. Bound column is one-based,
meaning that 1 is the first column, 2 the second column, etc. (Many
properties are zero-based, meaning that 0 is the first column, 1 is second
column, etc.). So, if your query returns two fields -- an ID field and a
descriptor field, for example -- you'd likely want the Bound Column to be 1
so that the ID value is the actual value of the combo box. But you can
display the descriptor field for the user's benefit.



Thanks

 

[Ken Snell [MVP]]

Thanks for this explanation. This helps me understand much better.

Your concept is very close to what is good to use, however, I'm going to
suggest a slightly different approach that should work just the way you want
it to work. Here are the steps that should occur in a general sense:

1) The Print button on your switchboard should open the PrintDialog form. It
should not open the report itself.

2) The PrintDialog form as you describe it should be ok so long as the Bound
Column of the combo box on that form is set to the same column number as the
column that holds the UR Number value.

3) The Cancel button on the PrintDialog form should run code that simply
closes the PrintDialog form (example given below):
Private Sub CancelButtonName_Click()
DoCmd.Close acForm, Me.Name
End Sub

4) The OK button on the PrintDialog form should make the form invisible and
then open the report, using code similar to this:
Private Sub OKButtonName_Click()
Me.Visible = False
DoCmd.OpenReport "ReportName", acViewNormal, , _
"[UR Number]='" & Me.ComboBoxName.Value & "'"
End Sub

5) Delete the macro from the OnOpen property of the report.

6) Keep the macro on the OnClose property of the report (the macro that
closes the PrintDialog form).


Should work as advertised now. Let me know.
--

Ken Snell
<MS ACCESS MVP>

The field is actually called UR Number and it's a text field because even
though it's a number, the first number is usually 0 and if it's set up as a
numeric field the 0 won't show. The 0 must be there to keep things the same
across all programs.

I've set up a switchboard that has a button that says "Print". This opens
the report. The report has an "On Open" macro that will open the dialog form
(called PrintDialog). This form has a combo box on it that asks for the UR
Number (if you press the drop down arrow it displays two columns - UR Number
and Patient Name). It also has two command buttons - OK - which is supposed
to open up the report for the UR Number in the combo box. The other button
is cancel - this one doesn't work properly either.

The instructions are in the help menu of Access called "Use a form to enter
report criteria". It says to set up a form with the following properties -
DefaultView: Single Form; ViewsAllowed: Form; ScrollBars: Neither;
RecordSelectors: No; NavigationButtons: No; BorderStyle: Dialog.

It says to use a text box to enter criteria, but I used a combo as described
above with the Name property set to UR Number.

Then it says to set up four macros in a macro group:
Open Dialog - this is the OnOpen procedure for the report - opens the dialog
form
Close Dialog - this is the OnClose procedure for the report - closes the
dialog form
OK - this is attached to the OK button on the dialog form(settings are Item
- [Visible]; Expression - No)
Cancel - attached to the Cancel button on the dialog form

The report is attached to a query with only two fields - UR Number and Name.
In the criteria for UR Number I modified their example of between dates so
that it looked for a UR Number like the one in the combo box.

It all seems to work up until the moment I press OK. The report is filtered
down to one patient - unfortunately it is always the first patient on the
list, not the one selected on the form. Also, when I press the cancel
button, the form closes but then the automatic dialog box for the query is
open.

Thinking about it now, I wonder if I should just forget all that macro stuff
and just use the automatic pop-up box that the query uses - though it would
be nice for the end user to be able to double check they have entered the
right UR Number prior to the report opening (which they can do with the combo
box).

Is PatientID a numeric field? If yes, I'd use an expression that has =
instead of Like.

I am not directly familiar with the "instructions" that you cite, so I'm not
sure where the programming for popping up the dialog box is (in the report?)

UR Number is the name of the control on the form (named "PrintDialog"),
right? And UR Number is bound to the field PatientID on that form? What type
of form is PrintDialog?

I need a little more info about how you set up this dialog box, etc. Can you
tell me where you started in ACCESS and where you've put
code/programming/etc.? And which form or forms are open when you run the
report? And how you run the report (e.g., you click a button on a form named
.....)?
--

Ken Snell
<MS ACCESS MVP>

Thanks Ken for all your help. I don't think it would have ever

occurred
to

me to use sub-reports.
There's just one last thing though - with the dialog box to use to filter
the report - I have followed the instructions in Access called "Use a

form
to

enter report criteria" to set up the dialog box with the command

buttons
and

macros. The instructions say "Enter the criteria for the data. In the
expression, use the Forms object, the name of the form, and the names

of
the

controls in the criteria." Then it gives an example. I modified the example
so that instead of saying between certain dates, I wrote (in the criteria
section of the PatientID field of the query the report is based on):
LIKE [Forms]![PrintDialog]![UR Number]
(UR Number is the name of the field for PatientID)
When I run the report it opens with the dialog box, I select the appropriate
UR Number, press OK, then an error pops up:
"The expression you entered has a function name that CT Data Management
can't find."
And the report opens for the first patient in the patient list.
How do I fix this??
Thanks

I have linked the "Master Report" to a query that lists all patients and
their ID numbers

:

Comments inline....
--

Ken Snell
<MS ACCESS MVP>

So what I do is:
Set up each subreport as separate reports and then set up a "master"
report
which the separate reports link into as the sub reports. - Is this right?

Yes.



Set up the dialog form so user can select appropriate patient.
- I'm not quite sure what you mean about the bound column for the combo
box
though - could you please explain that just a little more?

The bound column of a combo box defines which column in the row

source
is

the one from which the combo box draws its value. In the Properties window,
on the Data tab, you'll see this property. Bound column is one-based,
meaning that 1 is the first column, 2 the second column, etc. (Many
properties are zero-based, meaning that 0 is the first column, 1 is second
column, etc.). So, if your query returns two fields -- an ID field and a
descriptor field, for example -- you'd likely want the Bound Column

to
be 1

so that the ID value is the actual value of the combo box. But you can
display the descriptor field for the user's benefit.



Thanks

 

[CathyA]

Thanks Ken that was a big help. I do have one glitch though - when I press
OK, an error message pops up:
"CT Data Management couldn't print your object"
I've double checked names etc in the code in case that was causing problems.
This is the code for the OK button:

Private Sub OK_Click()
On Error GoTo Error_OK_Click

Me.Visible = False
DoCmd.OpenReport "MasterReport", acViewNormal, , "[UR Number]='" &
Me.URNumber.Value & "'"

Exit_OK_Click:
Exit Sub

Error_OK_Click:
MsgBox Err.Description
Resume Exit_OK_Click

End Sub

Is any of this causing the problem??
P.S. Re: the ComboBox - the bound column is set to 1. I assume this is the
same as the UR Number column as UR is the first column out of the two.

Thanks

Thanks for this explanation. This helps me understand much better.

Your concept is very close to what is good to use, however, I'm going to
suggest a slightly different approach that should work just the way you want
it to work. Here are the steps that should occur in a general sense:

1) The Print button on your switchboard should open the PrintDialog form. It
should not open the report itself.

2) The PrintDialog form as you describe it should be ok so long as the Bound
Column of the combo box on that form is set to the same column number as the
column that holds the UR Number value.

3) The Cancel button on the PrintDialog form should run code that simply
closes the PrintDialog form (example given below):
Private Sub CancelButtonName_Click()
DoCmd.Close acForm, Me.Name
End Sub

4) The OK button on the PrintDialog form should make the form invisible and
then open the report, using code similar to this:
Private Sub OKButtonName_Click()
Me.Visible = False
DoCmd.OpenReport "ReportName", acViewNormal, , _
"[UR Number]='" & Me.ComboBoxName.Value & "'"
End Sub

5) Delete the macro from the OnOpen property of the report.

6) Keep the macro on the OnClose property of the report (the macro that
closes the PrintDialog form).


Should work as advertised now. Let me know.
--

Ken Snell
<MS ACCESS MVP>

The field is actually called UR Number and it's a text field because even
though it's a number, the first number is usually 0 and if it's set up as a
numeric field the 0 won't show. The 0 must be there to keep things the same
across all programs.

I've set up a switchboard that has a button that says "Print". This opens
the report. The report has an "On Open" macro that will open the dialog form
(called PrintDialog). This form has a combo box on it that asks for the UR
Number (if you press the drop down arrow it displays two columns - UR Number
and Patient Name). It also has two command buttons - OK - which is supposed
to open up the report for the UR Number in the combo box. The other button
is cancel - this one doesn't work properly either.

The instructions are in the help menu of Access called "Use a form to enter
report criteria". It says to set up a form with the following properties -
DefaultView: Single Form; ViewsAllowed: Form; ScrollBars: Neither;
RecordSelectors: No; NavigationButtons: No; BorderStyle: Dialog.

It says to use a text box to enter criteria, but I used a combo as described
above with the Name property set to UR Number.

Then it says to set up four macros in a macro group:
Open Dialog - this is the OnOpen procedure for the report - opens the dialog
form
Close Dialog - this is the OnClose procedure for the report - closes the
dialog form
OK - this is attached to the OK button on the dialog form(settings are Item
- [Visible]; Expression - No)
Cancel - attached to the Cancel button on the dialog form

The report is attached to a query with only two fields - UR Number and Name.
In the criteria for UR Number I modified their example of between dates so
that it looked for a UR Number like the one in the combo box.

It all seems to work up until the moment I press OK. The report is filtered
down to one patient - unfortunately it is always the first patient on the
list, not the one selected on the form. Also, when I press the cancel
button, the form closes but then the automatic dialog box for the query is
open.

Thinking about it now, I wonder if I should just forget all that macro stuff
and just use the automatic pop-up box that the query uses - though it would
be nice for the end user to be able to double check they have entered the
right UR Number prior to the report opening (which they can do with the combo
box).

Is PatientID a numeric field? If yes, I'd use an expression that has =
instead of Like.

I am not directly familiar with the "instructions" that you cite, so I'm not
sure where the programming for popping up the dialog box is (in the report?)

UR Number is the name of the control on the form (named "PrintDialog"),
right? And UR Number is bound to the field PatientID on that form? What type
of form is PrintDialog?

I need a little more info about how you set up this dialog box, etc. Can you
tell me where you started in ACCESS and where you've put
code/programming/etc.? And which form or forms are open when you run the
report? And how you run the report (e.g., you click a button on a form named
.....)?
--

Ken Snell
<MS ACCESS MVP>




Thanks Ken for all your help. I don't think it would have ever occurred
to
me to use sub-reports.
There's just one last thing though - with the dialog box to use to filter
the report - I have followed the instructions in Access called "Use a form
to
enter report criteria" to set up the dialog box with the command buttons
and
macros. The instructions say "Enter the criteria for the data. In the
expression, use the Forms object, the name of the form, and the names of
the
controls in the criteria." Then it gives an example. I modified the
example
so that instead of saying between certain dates, I wrote (in the criteria
section of the PatientID field of the query the report is based on):
LIKE [Forms]![PrintDialog]![UR Number]
(UR Number is the name of the field for PatientID)
When I run the report it opens with the dialog box, I select the
appropriate
UR Number, press OK, then an error pops up:
"The expression you entered has a function name that CT Data Management
can't find."
And the report opens for the first patient in the patient list.
How do I fix this??
Thanks

I have linked the "Master Report" to a query that lists all patients and
their ID numbers

:

Comments inline....
--

Ken Snell
<MS ACCESS MVP>

So what I do is:
Set up each subreport as separate reports and then set up a "master"
report
which the separate reports link into as the sub reports. - Is this
right?

Yes.



Set up the dialog form so user can select appropriate patient.
- I'm not quite sure what you mean about the bound column for the
combo
box
though - could you please explain that just a little more?

The bound column of a combo box defines which column in the row source
is
the one from which the combo box draws its value. In the Properties
window,
on the Data tab, you'll see this property. Bound column is one-based,
meaning that 1 is the first column, 2 the second column, etc. (Many
properties are zero-based, meaning that 0 is the first column, 1 is
second
column, etc.). So, if your query returns two fields -- an ID field and a
descriptor field, for example -- you'd likely want the Bound Column to
be 1
so that the ID value is the actual value of the combo box. But you can
display the descriptor field for the user's benefit.



Thanks

 

[CathyA]

Don't worry about it - I think I may have solved the problem myself. I
noticed that when I was in the coding window some pop-up options were
displayed. I changed acViewNormal to acViewPreview and this seems to now
work.

Thanks for all your help Ken - I would have gone batty I think trying to do
all that myself. You've saved me quite a lot of stress. Thank you very much.

If I ever have problems again I'll come straight back to this support site.
It's marvellous.

Thanks, Cathy

Thanks Ken that was a big help. I do have one glitch though - when I press
OK, an error message pops up:
"CT Data Management couldn't print your object"
I've double checked names etc in the code in case that was causing problems.
This is the code for the OK button:

Private Sub OK_Click()
On Error GoTo Error_OK_Click

Me.Visible = False
DoCmd.OpenReport "MasterReport", acViewNormal, , "[UR Number]='" &
Me.URNumber.Value & "'"

Exit_OK_Click:
Exit Sub

Error_OK_Click:
MsgBox Err.Description
Resume Exit_OK_Click

End Sub

Is any of this causing the problem??
P.S. Re: the ComboBox - the bound column is set to 1. I assume this is the
same as the UR Number column as UR is the first column out of the two.

Thanks

Thanks for this explanation. This helps me understand much better.

Your concept is very close to what is good to use, however, I'm going to
suggest a slightly different approach that should work just the way you want
it to work. Here are the steps that should occur in a general sense:

1) The Print button on your switchboard should open the PrintDialog form. It
should not open the report itself.

2) The PrintDialog form as you describe it should be ok so long as the Bound
Column of the combo box on that form is set to the same column number as the
column that holds the UR Number value.

3) The Cancel button on the PrintDialog form should run code that simply
closes the PrintDialog form (example given below):
Private Sub CancelButtonName_Click()
DoCmd.Close acForm, Me.Name
End Sub

4) The OK button on the PrintDialog form should make the form invisible and
then open the report, using code similar to this:
Private Sub OKButtonName_Click()
Me.Visible = False
DoCmd.OpenReport "ReportName", acViewNormal, , _
"[UR Number]='" & Me.ComboBoxName.Value & "'"
End Sub

5) Delete the macro from the OnOpen property of the report.

6) Keep the macro on the OnClose property of the report (the macro that
closes the PrintDialog form).


Should work as advertised now. Let me know.
--

Ken Snell
<MS ACCESS MVP>

The field is actually called UR Number and it's a text field because even
though it's a number, the first number is usually 0 and if it's set up as a
numeric field the 0 won't show. The 0 must be there to keep things the same
across all programs.

I've set up a switchboard that has a button that says "Print". This opens
the report. The report has an "On Open" macro that will open the dialog form
(called PrintDialog). This form has a combo box on it that asks for the UR
Number (if you press the drop down arrow it displays two columns - UR Number
and Patient Name). It also has two command buttons - OK - which is supposed
to open up the report for the UR Number in the combo box. The other button
is cancel - this one doesn't work properly either.

The instructions are in the help menu of Access called "Use a form to enter
report criteria". It says to set up a form with the following properties -
DefaultView: Single Form; ViewsAllowed: Form; ScrollBars: Neither;
RecordSelectors: No; NavigationButtons: No; BorderStyle: Dialog.

It says to use a text box to enter criteria, but I used a combo as described
above with the Name property set to UR Number.

Then it says to set up four macros in a macro group:
Open Dialog - this is the OnOpen procedure for the report - opens the dialog
form
Close Dialog - this is the OnClose procedure for the report - closes the
dialog form
OK - this is attached to the OK button on the dialog form(settings are Item
- [Visible]; Expression - No)
Cancel - attached to the Cancel button on the dialog form

The report is attached to a query with only two fields - UR Number and Name.
In the criteria for UR Number I modified their example of between dates so
that it looked for a UR Number like the one in the combo box.

It all seems to work up until the moment I press OK. The report is filtered
down to one patient - unfortunately it is always the first patient on the
list, not the one selected on the form. Also, when I press the cancel
button, the form closes but then the automatic dialog box for the query is
open.

Thinking about it now, I wonder if I should just forget all that macro stuff
and just use the automatic pop-up box that the query uses - though it would
be nice for the end user to be able to double check they have entered the
right UR Number prior to the report opening (which they can do with the combo
box).

:

Is PatientID a numeric field? If yes, I'd use an expression that has =
instead of Like.

I am not directly familiar with the "instructions" that you cite, so I'm not
sure where the programming for popping up the dialog box is (in the report?)

UR Number is the name of the control on the form (named "PrintDialog"),
right? And UR Number is bound to the field PatientID on that form? What type
of form is PrintDialog?

I need a little more info about how you set up this dialog box, etc. Can you
tell me where you started in ACCESS and where you've put
code/programming/etc.? And which form or forms are open when you run the
report? And how you run the report (e.g., you click a button on a form named
.....)?
--

Ken Snell
<MS ACCESS MVP>




Thanks Ken for all your help. I don't think it would have ever occurred
to
me to use sub-reports.
There's just one last thing though - with the dialog box to use to filter
the report - I have followed the instructions in Access called "Use a form
to
enter report criteria" to set up the dialog box with the command buttons
and
macros. The instructions say "Enter the criteria for the data. In the
expression, use the Forms object, the name of the form, and the names of
the
controls in the criteria." Then it gives an example. I modified the
example
so that instead of saying between certain dates, I wrote (in the criteria
section of the PatientID field of the query the report is based on):
LIKE [Forms]![PrintDialog]![UR Number]
(UR Number is the name of the field for PatientID)
When I run the report it opens with the dialog box, I select the
appropriate
UR Number, press OK, then an error pops up:
"The expression you entered has a function name that CT Data Management
can't find."
And the report opens for the first patient in the patient list.
How do I fix this??
Thanks

I have linked the "Master Report" to a query that lists all patients and
their ID numbers

:

Comments inline....
--

Ken Snell
<MS ACCESS MVP>

So what I do is:
Set up each subreport as separate reports and then set up a "master"
report
which the separate reports link into as the sub reports. - Is this
right?

Yes.



Set up the dialog form so user can select appropriate patient.
- I'm not quite sure what you mean about the bound column for the
combo
box
though - could you please explain that just a little more?

The bound column of a combo box defines which column in the row source
is
the one from which the combo box draws its value. In the Properties
window,
on the Data tab, you'll see this property. Bound column is one-based,
meaning that 1 is the first column, 2 the second column, etc. (Many
properties are zero-based, meaning that 0 is the first column, 1 is
second
column, etc.). So, if your query returns two fields -- an ID field and a
descriptor field, for example -- you'd likely want the Bound Column to
be 1
so that the ID value is the actual value of the combo box. But you can
display the descriptor field for the user's benefit.



Thanks

 

[Ken Snell [MVP]]

Interesting that your report works with acViewPreview but not with
acViewNormal? This may come back to be a problem for you down the road,
whatever is causing this.

But, anyway, congratulations on your success!
--

Ken Snell
<MS ACCESS MVP>

Don't worry about it - I think I may have solved the problem myself. I
noticed that when I was in the coding window some pop-up options were
displayed. I changed acViewNormal to acViewPreview and this seems to now
work.

Thanks for all your help Ken - I would have gone batty I think trying to do
all that myself. You've saved me quite a lot of stress. Thank you very much.

If I ever have problems again I'll come straight back to this support site.
It's marvellous.

Thanks, Cathy

Thanks Ken that was a big help. I do have one glitch though - when I press
OK, an error message pops up:
"CT Data Management couldn't print your object"
I've double checked names etc in the code in case that was causing problems.
This is the code for the OK button:

Private Sub OK_Click()
On Error GoTo Error_OK_Click

Me.Visible = False
DoCmd.OpenReport "MasterReport", acViewNormal, , "[UR Number]='" &
Me.URNumber.Value & "'"

Exit_OK_Click:
Exit Sub

Error_OK_Click:
MsgBox Err.Description
Resume Exit_OK_Click

End Sub

Is any of this causing the problem??
P.S. Re: the ComboBox - the bound column is set to 1. I assume this is the
same as the UR Number column as UR is the first column out of the two.

Thanks

Thanks for this explanation. This helps me understand much better.

Your concept is very close to what is good to use, however, I'm going to
suggest a slightly different approach that should work just the way you want
it to work. Here are the steps that should occur in a general sense:

1) The Print button on your switchboard should open the PrintDialog form. It
should not open the report itself.

2) The PrintDialog form as you describe it should be ok so long as the Bound
Column of the combo box on that form is set to the same column number as the
column that holds the UR Number value.

3) The Cancel button on the PrintDialog form should run code that simply
closes the PrintDialog form (example given below):
Private Sub CancelButtonName_Click()
DoCmd.Close acForm, Me.Name
End Sub

4) The OK button on the PrintDialog form should make the form invisible and
then open the report, using code similar to this:
Private Sub OKButtonName_Click()
Me.Visible = False
DoCmd.OpenReport "ReportName", acViewNormal, , _
"[UR Number]='" & Me.ComboBoxName.Value & "'"
End Sub

5) Delete the macro from the OnOpen property of the report.

6) Keep the macro on the OnClose property of the report (the macro that
closes the PrintDialog form).


Should work as advertised now. Let me know.
--

Ken Snell
<MS ACCESS MVP>





The field is actually called UR Number and it's a text field because even
though it's a number, the first number is usually 0 and if it's set up as
a
numeric field the 0 won't show. The 0 must be there to keep things the
same
across all programs.

I've set up a switchboard that has a button that says "Print". This opens
the report. The report has an "On Open" macro that will open the dialog
form
(called PrintDialog). This form has a combo box on it that asks for the
UR
Number (if you press the drop down arrow it displays two columns - UR
Number
and Patient Name). It also has two command buttons - OK - which is
supposed
to open up the report for the UR Number in the combo box. The other
button
is cancel - this one doesn't work properly either.

The instructions are in the help menu of Access called "Use a form to
enter
report criteria". It says to set up a form with the following
properties -
DefaultView: Single Form; ViewsAllowed: Form; ScrollBars: Neither;
RecordSelectors: No; NavigationButtons: No; BorderStyle: Dialog.

It says to use a text box to enter criteria, but I used a combo as
described
above with the Name property set to UR Number.

Then it says to set up four macros in a macro group:
Open Dialog - this is the OnOpen procedure for the report - opens the
dialog
form
Close Dialog - this is the OnClose procedure for the report - closes the
dialog form
OK - this is attached to the OK button on the dialog form(settings are
Item
- [Visible]; Expression - No)
Cancel - attached to the Cancel button on the dialog form

The report is attached to a query with only two fields - UR Number and
Name.
In the criteria for UR Number I modified their example of between dates
so
that it looked for a UR Number like the one in the combo box.

It all seems to work up until the moment I press OK. The report is
filtered
down to one patient - unfortunately it is always the first patient on the
list, not the one selected on the form. Also, when I press the cancel
button, the form closes but then the automatic dialog box for the query is
open.

Thinking about it now, I wonder if I should just forget all that macro
stuff
and just use the automatic pop-up box that the query uses - though it
would
be nice for the end user to be able to double check they have entered the
right UR Number prior to the report opening (which they can do with the
combo
box).

:

Is PatientID a numeric field? If yes, I'd use an expression that has =
instead of Like.

I am not directly familiar with the "instructions" that you cite, so I'm
not
sure where the programming for popping up the dialog box is (in the
report?)

UR Number is the name of the control on the form (named "PrintDialog"),
right? And UR Number is bound to the field PatientID on that form? What
type
of form is PrintDialog?

I need a little more info about how you set up this dialog box, etc. Can
you
tell me where you started in ACCESS and where you've put
code/programming/etc.? And which form or forms are open when you run the
report? And how you run the report (e.g., you click a button on a form
named
.....)?
--

Ken Snell
<MS ACCESS MVP>




Thanks Ken for all your help. I don't think it would have ever
occurred
to
me to use sub-reports.
There's just one last thing though - with the dialog box to use to
filter
the report - I have followed the instructions in Access called "Use a
form
to
enter report criteria" to set up the dialog box with the command
buttons
and
macros. The instructions say "Enter the criteria for the data. In
the
expression, use the Forms object, the name of the form, and the names
of
the
controls in the criteria." Then it gives an example. I modified the
example
so that instead of saying between certain dates, I wrote (in the
criteria
section of the PatientID field of the query the report is based on):
LIKE [Forms]![PrintDialog]![UR Number]
(UR Number is the name of the field for PatientID)
When I run the report it opens with the dialog box, I select the
appropriate
UR Number, press OK, then an error pops up:
"The expression you entered has a function name that CT Data
Management
can't find."
And the report opens for the first patient in the patient list.
How do I fix this??
Thanks

I have linked the "Master Report" to a query that lists all patients
and
their ID numbers

:

Comments inline....
--

Ken Snell
<MS ACCESS MVP>

So what I do is:
Set up each subreport as separate reports and then set up a
"master"
report
which the separate reports link into as the sub reports. - Is this
right?

Yes.



Set up the dialog form so user can select appropriate patient.
- I'm not quite sure what you mean about the bound column for the
combo
box
though - could you please explain that just a little more?

The bound column of a combo box defines which column in the row
source
is
the one from which the combo box draws its value. In the Properties
window,
on the Data tab, you'll see this property. Bound column is
one-based,
meaning that 1 is the first column, 2 the second column, etc. (Many
properties are zero-based, meaning that 0 is the first column, 1 is
second
column, etc.). So, if your query returns two fields -- an ID field
and a
descriptor field, for example -- you'd likely want the Bound Column
to
be 1
so that the ID value is the actual value of the combo box. But you
can
display the descriptor field for the user's benefit.



Thanks