Help!! I'm running around in circles!

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" wrote in message
...
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]" wrote:

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" wrote in message
...
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]" wrote:

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" wrote in message
...
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]" wrote:

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" wrote in message
...
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

"Ken Snell [MVP]" wrote:

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.
--

Ken Snell
MS ACCESS MVP



"CathyA" wrote in message
...
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

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]" wrote:

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" wrote in message
...
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]" wrote:

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" wrote in message
...
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]" wrote:

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" wrote in message
...
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]" wrote:

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" wrote in message
...
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

"Ken Snell [MVP]" wrote:

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.
--

Ken Snell
MS ACCESS MVP



"CathyA" wrote in message
...
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

Comments inline....
--

Ken Snell
MS ACCESS MVP

"CathyA" wrote in message
news
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]" wrote:

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" wrote in message
...
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]" wrote:

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" wrote in message
...
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]" wrote:

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" wrote in message
...
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]" wrote:

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" wrote in message
...
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

"Ken Snell [MVP]" wrote:

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.
--

Ken Snell
MS ACCESS MVP



"CathyA" wrote in
message

...
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

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]" wrote:

Comments inline....
--

Ken Snell
MS ACCESS MVP

"CathyA" wrote in message
news
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]" wrote:

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" wrote in message
...
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]" wrote:

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" wrote in message
...
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]" wrote:

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" wrote in message
...
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]" wrote:

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" wrote in message
...
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

"Ken Snell [MVP]" wrote:

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.
--

Ken Snell
MS ACCESS MVP



"CathyA" wrote in
message

...
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

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




"CathyA" wrote in message
...
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]" wrote:

Comments inline....
--

Ken Snell
MS ACCESS MVP

"CathyA" wrote in message
news
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

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).

"Ken Snell [MVP]" wrote:

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




"CathyA" wrote in message
...
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]" wrote:

Comments inline....
--

Ken Snell
MS ACCESS MVP

"CathyA" wrote in message
news 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

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





"CathyA" wrote in message
...
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).

"Ken Snell [MVP]" wrote:

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




"CathyA" wrote in message
...
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]" wrote:

Comments inline....
--

Ken Snell
MS ACCESS MVP

"CathyA" wrote in message
news 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

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. 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

"Ken Snell [MVP]" wrote:

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





"CathyA" wrote in message
...
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).

"Ken Snell [MVP]" wrote:

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




"CathyA" wrote in message
...
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]" wrote:

Comments inline....
--

Ken Snell
MS ACCESS MVP

"CathyA" wrote in message
news 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

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

"CathyA" wrote:

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. 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

"Ken Snell [MVP]" wrote:

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





"CathyA" wrote in message
...
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).

"Ken Snell [MVP]" wrote:

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




"CathyA" wrote in message
...
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]" wrote:

Comments inline....
--

Ken Snell
MS ACCESS MVP

"CathyA" wrote in message
news 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

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



"CathyA" wrote in message
...
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

"CathyA" wrote:

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. 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

"Ken Snell [MVP]" wrote:

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





"CathyA" wrote in message
...
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).

"Ken Snell [MVP]" wrote:

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




"CathyA" wrote in message
...
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]" wrote:

Comments inline....
--

Ken Snell
MS ACCESS MVP

"CathyA" wrote in message
news 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