subforms in table

[CK]

Hi
I'd be very grateful for some expert advice on my forms please!
I'm having troubles with a subform within another subform in the main
form.....

I'll try to summarise my database first then the problem:
I'm setting up a research database of patients seen in clinic. Each
patient's details are recorded in a "Patient Demographics" form (and saved to
the underlying table). Each patient has a unique ID (PatientNumber). When a
patient presents to clinic with a new problem, this is a new record on the
"History" form/table (this table has TWO primary(?) keys: HistoryID and
PatientNumber). For each problem (as recorded in "History") the patient may
come back for one or more follow-up consultations, each episode a new record
(with a unique ID, ConsultationID) in the "Consultation" form/table. Of
course a patient may return with a NEW problem, generating a new record in
"History" with corresponding new record(s) in "Consultation."

The first two tables (PatientDemographics, History) are linked via
PatientNumber, the 3rd table (Consultation)is linked to the second (History)
via Consultation.ConsultationID to History.HistoryID.

For each record in the PatientDemographics Form, there is a history subform
visible within which is a Consultation subform (in table format). Thus at a
glance one can see the consultations that that particular patient has had for
that particular problem.

The problem:
This works very well EXCEPT when I add a new History record (i.e. a new
problem) for an existing patient who has been seen previously for a different
problem, the consultations shown are still related to the previous problem. i
can't seem to find a way to show only the consultations relating to the new
problem.

I hope this explains the situation adequately and that the solution is
straightforward!

Thanks for your help
Regards

 

[Wayne-I-M]

Hi

There are a few way to do what you want - this is just one of them.

Make a copy of your database and try this out on the copy.
*******

You have 3 tables
tblPatients - PK = PatientsID -
tblHistory - PK = HistoryID
tblConsultation - PK = ConsultationID

add a new field to tblPatients called Extra (format = number) - more details
below


Create 3 forms

frmPatients - Single form
frmHistory - Continous
frmConsultation - Continous or single (up to you)

Open frmPatients in deisgn view and add 2 tabs
On the 1st tab put frmHistory - linked to the main form by PatientID
On the 2nd tab put frmConsultation -

Now comes a little cheat that many developers use to link frmHistory to
frmConsultation.

Make sure you can see txtExtra (bound the new Extra field in your table) on
the main form (frmPatients ) - when you have finished it will be set to
Visible = No, but for now keep it visible just so you can see whats going on.

The OnClick event of the form - in your case that would be selecting a
record on the continous form by clicking the record selector (the small
section at the right of the record)

Forms!frmPatients!Extra = Forms!frmPatients!frmHistory.Form!HistoryID

So you can see what you have done - I hope

By clicking the record on the continous form you have set the value of the
Extra field in the main table to that of the ID of the history record - I
would not use an unbound box for this (although some people do) as you will
not very ofter change this - and it is only a very small amount of data to
save. But this is up to you.

OK - the next thing to do is to be able to click a record on frmHistory
(Continous form) and go to the coresponding record on frmConsultation

Do this you need to add an extra line of code to the OnClick event of the
record in frmHistory. Like this

The 1st thing you need to do is to link the forms so
Right click frmConsultation and open the properties box
In the data column
Link Master Field = PatientsID
Link Child Field = Extra

So you see what you have done. This form is "set to" (sorry don't know the
correct English word for this) to a specific Patient and a specific HistoryID
(medicial problem)

Next to back to frmHistory and change the ode you made to this (OnClick)

Forms!frmPatients!Extra = Forms!frmPatients!frmHistory.Form!HistoryID
DoCmd.GoToControl "frmConsultation"

This will (on clicking the history form) take you to the consultation form
(and as you have just linked the forms) to the right consultation that is
linked to that specific history for that specific patient.

Hope all this make sense.

Good luck

 

[Wayne-I-M]

Sorry - a friend of mine just telephoned me to say I should explain the
"Extra" field a little more

The basics are that you are applying a filter to your 3rd form.

OK - I said this in the last post

By clicking the record on the continous form you have set the value of the
Extra field in the main table to that of the ID of the history record - I
would not use an unbound box for this (although some people do) as you will
not very ofter change this - and it is only a very small amount of data to
save. But this is up to you.


As you can see - each time you create a new record in tblHistory - this is
each time the patient comes with a new problem - and you click the form
slector
You will alter the content of the field "extra" in tblpatient
This mean you will always go to the right "bit" of frmConsultation

Basically it's a cheat you are filtering frmConsultation to only show
the records that are linked to the record in frmHistory that you clicked.

 

[CK]

Hi Wayne-I-M

Thanks for your help, I've made the changes you've suggested but it hasn't
(yet) worked as expected. I suspect I've misinterpreted a line or two along
the way.

My understanding is that your aim is to force the value of the HistoryID to
show up in the "Extra" box , then on pressing the consultation tab it should
open on a consultation linked to the value of "Extra" (i.e. the HistoryID).
Will this allow each patient to have more than one History AND have more
than one Consultation per History?

I've put the contol "Extra" on the main Patients form.
I've typed the code in the event procedure of the History form's Tabctl (I
think this is where you referring to?) and changed the linked fields in the
Consultation form to Master:Extra and ChildatientID (i.e. opposite to the
way round you had written, but your way wasn't available to me)

However, the Extra field remains blank despite all the changes. I can't
force a number into it. If I physically type a number in it, the PatientID
on the Consultation form changes to that number instead. So I changed the
linked fields to Extra and ConsultationID (instead of PatientID) but the
consultationID and patientID on the consultation form are now both blank!

Does this make sense to you and can you spot any glaring errors?

Thanks again for your help
All the best
CK

 

[Wayne-I-M]

This is the bit you need to work on

Forms!frmPatients!Extra = Forms!frmPatients!frmHistory.Form!HistoryID


Can I make a suggestion - sorry if it offends I don't mean to. But I am not
sure har far you have got in the learning of code writting.

Try this
Open the Main form in design view
So you can see both the main form and the sub (on the tab)
Right click the sub form on the tab (the blank bit in the in the form) below
the detail section
In the properties box - event column select OnClick
Create a macro
Select Set Value
Use the wizard to set the value of the txtExtra on the main form to that of
the ID on the Tabed Subform

If you have problems post back




C

 

[CK]

Hi Wayne

You're quite right, I have very little experience with code or macros so no
offence taken, I'm happy to learn!

I've tried to do it with the macro builder as you suggested but I still have
no joy on automatically filling in the Extra control.

These are the arguments from the macro using SetValue:

[Forms]![Patients]![Extra], [Forms]![Patients]![History2].[Form]![HistoryID]

But when I run the macro I get an error (2950)

Any further ideas?
Thanks again
Regards
CK

 

[Wayne-I-M]

It should be this

Forms!Patients!Extra =Forms!Patients!History2.Form!HistoryID

From this code I assume
You have a form called Patients
On the main form you have a sub form called History2
On your form (History2) you have a control called HistoryID

You should "check" that these are the right names - they are not the names
of the bound tables or any tables field but they are the name of controls on
your form(s)

Open the form in design view
Right click the control - or subform, just at the edge
Open the properties box and check the name of the control (a subform is also
a control)
Look in the Other column and in the Name row

On you main form make sure the control that is bound to your Extra field (in
the table) is called Extra - juust and idea change the name of the control to
txtExtra - to make it simpler to finde it use on the form

On your subform - NOT on any controls but the form it self

Forms!Patients!txtExtra =Forms!Patients!History2.Form!HistoryID

Note I have changed the Extra to txtExtra

To check that you are using the (sub form called History2) form's OnClick
and not a control do this

Open the properties box
Right Click the form
Select the Event column
Select the OnClick row
Select the build option (...)
Select code

It should look like this

Private Sub Form_Click()

End Sub

If it does not then you are not click the form but some control. Close the
builder and go back to the form and click the grey area and try and again

when you get this

Private Sub Form_Click()

End Sub


Add you code to it so it looks like this

Private Sub Form_Click()
Forms!Patients!txtExtra =Forms!Patients!History2.Form!HistoryID
End Sub


If you have problems post back


--
Wayne
Manchester, England.

Hi Wayne

You're quite right, I have very little experience with code or macros so no
offence taken, I'm happy to learn!

I've tried to do it with the macro builder as you suggested but I still have
no joy on automatically filling in the Extra control.

These are the arguments from the macro using SetValue:

[Forms]![Patients]![Extra], [Forms]![Patients]![History2].[Form]![HistoryID]

But when I run the macro I get an error (2950)

Any further ideas?
Thanks again
Regards
CK

This is the bit you need to work on

Forms!frmPatients!Extra = Forms!frmPatients!frmHistory.Form!HistoryID


Can I make a suggestion - sorry if it offends I don't mean to. But I am not
sure har far you have got in the learning of code writting.

Try this
Open the Main form in design view
So you can see both the main form and the sub (on the tab)
Right click the sub form on the tab (the blank bit in the in the form) below
the detail section
In the properties box - event column select OnClick
Create a macro
Select Set Value
Use the wizard to set the value of the txtExtra on the main form to that of
the ID on the Tabed Subform

If you have problems post back




C

 

[CK]

Hi Wayne

Thanks for your patience with my basic coding! I've been playing with this
code and for some reason couldn't make it work in the Form "onclick"
property but interestingly it does do something when the same code is put
into the "onEnter"property of the SUBform/subreport. It does cause the value
of historyID to be entered into txtExtra but only after clicking both
historyID and txtExtra a few times. At least its doing something!
Unfortunately when I added in the next bit of code: DoCmd.GoToControl
"Consultation2", once the Consultation2 subform is open I can't navigate away
from it. Neither clicking the History tab nor changing the patient works, the
consultation tab stays open.

Is this curable or should I try something else?

Cheers
CK

It should be this

Forms!Patients!Extra =Forms!Patients!History2.Form!HistoryID

From this code I assume
You have a form called Patients
On the main form you have a sub form called History2
On your form (History2) you have a control called HistoryID

You should "check" that these are the right names - they are not the names
of the bound tables or any tables field but they are the name of controls on
your form(s)

Open the form in design view
Right click the control - or subform, just at the edge
Open the properties box and check the name of the control (a subform is also
a control)
Look in the Other column and in the Name row

On you main form make sure the control that is bound to your Extra field (in
the table) is called Extra - juust and idea change the name of the control to
txtExtra - to make it simpler to finde it use on the form

On your subform - NOT on any controls but the form it self

Forms!Patients!txtExtra =Forms!Patients!History2.Form!HistoryID

Note I have changed the Extra to txtExtra

To check that you are using the (sub form called History2) form's OnClick
and not a control do this

Open the properties box
Right Click the form
Select the Event column
Select the OnClick row
Select the build option (...)
Select code

It should look like this

Private Sub Form_Click()

End Sub

If it does not then you are not click the form but some control. Close the
builder and go back to the form and click the grey area and try and again

when you get this

Private Sub Form_Click()

End Sub


Add you code to it so it looks like this

Private Sub Form_Click()
Forms!Patients!txtExtra =Forms!Patients!History2.Form!HistoryID
End Sub


If you have problems post back


--
Wayne
Manchester, England.

Hi Wayne

You're quite right, I have very little experience with code or macros so no
offence taken, I'm happy to learn!

I've tried to do it with the macro builder as you suggested but I still have
no joy on automatically filling in the Extra control.

These are the arguments from the macro using SetValue:

[Forms]![Patients]![Extra], [Forms]![Patients]![History2].[Form]![HistoryID]

But when I run the macro I get an error (2950)

Any further ideas?
Thanks again
Regards
CK

This is the bit you need to work on

Forms!frmPatients!Extra = Forms!frmPatients!frmHistory.Form!HistoryID


Can I make a suggestion - sorry if it offends I don't mean to. But I am not
sure har far you have got in the learning of code writting.

Try this
Open the Main form in design view
So you can see both the main form and the sub (on the tab)
Right click the sub form on the tab (the blank bit in the in the form) below
the detail section
In the properties box - event column select OnClick
Create a macro
Select Set Value
Use the wizard to set the value of the txtExtra on the main form to that of
the ID on the Tabed Subform

If you have problems post back




C


--
Wayne
Manchester, England.



:


Hi Wayne-I-M

Thanks for your help, I've made the changes you've suggested but it hasn't
(yet) worked as expected. I suspect I've misinterpreted a line or two along
the way.

My understanding is that your aim is to force the value of the HistoryID to
show up in the "Extra" box , then on pressing the consultation tab it should
open on a consultation linked to the value of "Extra" (i.e. the HistoryID).
Will this allow each patient to have more than one History AND have more
than one Consultation per History?

I've put the contol "Extra" on the main Patients form.
I've typed the code in the event procedure of the History form's Tabctl (I
think this is where you referring to?) and changed the linked fields in the
Consultation form to Master:Extra and ChildatientID (i.e. opposite to the
way round you had written, but your way wasn't available to me)

However, the Extra field remains blank despite all the changes. I can't
force a number into it. If I physically type a number in it, the PatientID
on the Consultation form changes to that number instead. So I changed the
linked fields to Extra and ConsultationID (instead of PatientID) but the
consultationID and patientID on the consultation form are now both blank!

Does this make sense to you and can you spot any glaring errors?

Thanks again for your help
All the best
CK





:

Hi

There are a few way to do what you want - this is just one of them.

Make a copy of your database and try this out on the copy.
*******

You have 3 tables
tblPatients - PK = PatientsID -
tblHistory - PK = HistoryID
tblConsultation - PK = ConsultationID

add a new field to tblPatients called Extra (format = number) - more details
below


Create 3 forms

frmPatients - Single form
frmHistory - Continous
frmConsultation - Continous or single (up to you)

Open frmPatients in deisgn view and add 2 tabs
On the 1st tab put frmHistory - linked to the main form by PatientID
On the 2nd tab put frmConsultation -

Now comes a little cheat that many developers use to link frmHistory to
frmConsultation.

Make sure you can see txtExtra (bound the new Extra field in your table) on
the main form (frmPatients ) - when you have finished it will be set to
Visible = No, but for now keep it visible just so you can see whats going on.

The OnClick event of the form - in your case that would be selecting a
record on the continous form by clicking the record selector (the small
section at the right of the record)

Forms!frmPatients!Extra = Forms!frmPatients!frmHistory.Form!HistoryID

So you can see what you have done - I hope

By clicking the record on the continous form you have set the value of the
Extra field in the main table to that of the ID of the history record - I
would not use an unbound box for this (although some people do) as you will
not very ofter change this - and it is only a very small amount of data to
save. But this is up to you.

OK - the next thing to do is to be able to click a record on frmHistory
(Continous form) and go to the coresponding record on frmConsultation

Do this you need to add an extra line of code to the OnClick event of the
record in frmHistory. Like this

The 1st thing you need to do is to link the forms so
Right click frmConsultation and open the properties box
In the data column
Link Master Field = PatientsID
Link Child Field = Extra

So you see what you have done. This form is "set to" (sorry don't know the
correct English word for this) to a specific Patient and a specific HistoryID
(medicial problem)

Next to back to frmHistory and change the ode you made to this (OnClick)

Forms!frmPatients!Extra = Forms!frmPatients!frmHistory.Form!HistoryID
DoCmd.GoToControl "frmConsultation"

This will (on clicking the history form) take you to the consultation form
(and as you have just linked the forms) to the right consultation that is
linked to that specific history for that specific patient.

Hope all this make sense.

Good luck





--
Wayne
Manchester, England.



:

Hi
I'd be very grateful for some expert advice on my forms please!
I'm having troubles with a subform within another subform in the main
form.....

I'll try to summarise my database first then the problem:
I'm setting up a research database of patients seen in clinic. Each
patient's details are recorded in a "Patient Demographics" form (and saved to
the underlying table). Each patient has a unique ID (PatientNumber). When a
patient presents to clinic with a new problem, this is a new record on the
"History" form/table (this table has TWO primary(?) keys: HistoryID and
PatientNumber). For each problem (as recorded in "History") the patient may
come back for one or more follow-up consultations, each episode a new record
(with a unique ID, ConsultationID) in the "Consultation" form/table. Of
course a patient may return with a NEW problem, generating a new record in
"History" with corresponding new record(s) in "Consultation."

The first two tables (PatientDemographics, History) are linked via
PatientNumber, the 3rd table (Consultation)is linked to the second (History)
via Consultation.ConsultationID to History.HistoryID.

For each record in the PatientDemographics Form, there is a history subform
visible within which is a Consultation subform (in table format). Thus at a
glance one can see the consultations that that particular patient has had for
that particular problem.

The problem:
This works very well EXCEPT when I add a new History record (i.e. a new
problem) for an existing patient who has been seen previously for a different
problem, the consultations shown are still related to the previous problem. i
can't seem to find a way to show only the consultations relating to the new
problem.

I hope this explains the situation adequately and that the solution is
straightforward!

Thanks for your help
Regards

 

[CK]

Hi Wayne

Thanks again for your help, I've continued tweaking this but I don't feel
I've got anywhere recently so I'm going to throw it open to the forum again
to see if there is another way around the problem. I'll keep working on your
suggestions in the meantime though.
Thanks for what you have done so far.
Regards
CK

Hi Wayne

Thanks for your patience with my basic coding! I've been playing with this
code and for some reason couldn't make it work in the Form "onclick"
property but interestingly it does do something when the same code is put
into the "onEnter"property of the SUBform/subreport. It does cause the value
of historyID to be entered into txtExtra but only after clicking both
historyID and txtExtra a few times. At least its doing something!
Unfortunately when I added in the next bit of code: DoCmd.GoToControl
"Consultation2", once the Consultation2 subform is open I can't navigate away
from it. Neither clicking the History tab nor changing the patient works, the
consultation tab stays open.

Is this curable or should I try something else?

Cheers
CK

It should be this

Forms!Patients!Extra =Forms!Patients!History2.Form!HistoryID

From this code I assume
You have a form called Patients
On the main form you have a sub form called History2
On your form (History2) you have a control called HistoryID

You should "check" that these are the right names - they are not the names
of the bound tables or any tables field but they are the name of controls on
your form(s)

Open the form in design view
Right click the control - or subform, just at the edge
Open the properties box and check the name of the control (a subform is also
a control)
Look in the Other column and in the Name row

On you main form make sure the control that is bound to your Extra field (in
the table) is called Extra - juust and idea change the name of the control to
txtExtra - to make it simpler to finde it use on the form

On your subform - NOT on any controls but the form it self

Forms!Patients!txtExtra =Forms!Patients!History2.Form!HistoryID

Note I have changed the Extra to txtExtra

To check that you are using the (sub form called History2) form's OnClick
and not a control do this

Open the properties box
Right Click the form
Select the Event column
Select the OnClick row
Select the build option (...)
Select code

It should look like this

Private Sub Form_Click()

End Sub

If it does not then you are not click the form but some control. Close the
builder and go back to the form and click the grey area and try and again

when you get this

Private Sub Form_Click()

End Sub


Add you code to it so it looks like this

Private Sub Form_Click()
Forms!Patients!txtExtra =Forms!Patients!History2.Form!HistoryID
End Sub


If you have problems post back


--
Wayne
Manchester, England.

Hi Wayne

You're quite right, I have very little experience with code or macros so no
offence taken, I'm happy to learn!

I've tried to do it with the macro builder as you suggested but I still have
no joy on automatically filling in the Extra control.

These are the arguments from the macro using SetValue:

[Forms]![Patients]![Extra], [Forms]![Patients]![History2].[Form]![HistoryID]

But when I run the macro I get an error (2950)

Any further ideas?
Thanks again
Regards
CK

:

This is the bit you need to work on

Forms!frmPatients!Extra = Forms!frmPatients!frmHistory.Form!HistoryID


Can I make a suggestion - sorry if it offends I don't mean to. But I am not
sure har far you have got in the learning of code writting.

Try this
Open the Main form in design view
So you can see both the main form and the sub (on the tab)
Right click the sub form on the tab (the blank bit in the in the form) below
the detail section
In the properties box - event column select OnClick
Create a macro
Select Set Value
Use the wizard to set the value of the txtExtra on the main form to that of
the ID on the Tabed Subform

If you have problems post back




C


--
Wayne
Manchester, England.



:


Hi Wayne-I-M

Thanks for your help, I've made the changes you've suggested but it hasn't
(yet) worked as expected. I suspect I've misinterpreted a line or two along
the way.

My understanding is that your aim is to force the value of the HistoryID to
show up in the "Extra" box , then on pressing the consultation tab it should
open on a consultation linked to the value of "Extra" (i.e. the HistoryID).
Will this allow each patient to have more than one History AND have more
than one Consultation per History?

I've put the contol "Extra" on the main Patients form.
I've typed the code in the event procedure of the History form's Tabctl (I
think this is where you referring to?) and changed the linked fields in the
Consultation form to Master:Extra and ChildatientID (i.e. opposite to the
way round you had written, but your way wasn't available to me)

However, the Extra field remains blank despite all the changes. I can't
force a number into it. If I physically type a number in it, the PatientID
on the Consultation form changes to that number instead. So I changed the
linked fields to Extra and ConsultationID (instead of PatientID) but the
consultationID and patientID on the consultation form are now both blank!

Does this make sense to you and can you spot any glaring errors?

Thanks again for your help
All the best
CK





:

Hi

There are a few way to do what you want - this is just one of them.

Make a copy of your database and try this out on the copy.
*******

You have 3 tables
tblPatients - PK = PatientsID -
tblHistory - PK = HistoryID
tblConsultation - PK = ConsultationID

add a new field to tblPatients called Extra (format = number) - more details
below


Create 3 forms

frmPatients - Single form
frmHistory - Continous
frmConsultation - Continous or single (up to you)

Open frmPatients in deisgn view and add 2 tabs
On the 1st tab put frmHistory - linked to the main form by PatientID
On the 2nd tab put frmConsultation -

Now comes a little cheat that many developers use to link frmHistory to
frmConsultation.

Make sure you can see txtExtra (bound the new Extra field in your table) on
the main form (frmPatients ) - when you have finished it will be set to
Visible = No, but for now keep it visible just so you can see whats going on.

The OnClick event of the form - in your case that would be selecting a
record on the continous form by clicking the record selector (the small
section at the right of the record)

Forms!frmPatients!Extra = Forms!frmPatients!frmHistory.Form!HistoryID

So you can see what you have done - I hope

By clicking the record on the continous form you have set the value of the
Extra field in the main table to that of the ID of the history record - I
would not use an unbound box for this (although some people do) as you will
not very ofter change this - and it is only a very small amount of data to
save. But this is up to you.

OK - the next thing to do is to be able to click a record on frmHistory
(Continous form) and go to the coresponding record on frmConsultation

Do this you need to add an extra line of code to the OnClick event of the
record in frmHistory. Like this

The 1st thing you need to do is to link the forms so
Right click frmConsultation and open the properties box
In the data column
Link Master Field = PatientsID
Link Child Field = Extra

So you see what you have done. This form is "set to" (sorry don't know the
correct English word for this) to a specific Patient and a specific HistoryID
(medicial problem)

Next to back to frmHistory and change the ode you made to this (OnClick)

Forms!frmPatients!Extra = Forms!frmPatients!frmHistory.Form!HistoryID
DoCmd.GoToControl "frmConsultation"

This will (on clicking the history form) take you to the consultation form
(and as you have just linked the forms) to the right consultation that is
linked to that specific history for that specific patient.

Hope all this make sense.

Good luck





--
Wayne
Manchester, England.



:

Hi
I'd be very grateful for some expert advice on my forms please!
I'm having troubles with a subform within another subform in the main
form.....

I'll try to summarise my database first then the problem:
I'm setting up a research database of patients seen in clinic. Each
patient's details are recorded in a "Patient Demographics" form (and saved to
the underlying table). Each patient has a unique ID (PatientNumber). When a
patient presents to clinic with a new problem, this is a new record on the
"History" form/table (this table has TWO primary(?) keys: HistoryID and
PatientNumber). For each problem (as recorded in "History") the patient may
come back for one or more follow-up consultations, each episode a new record
(with a unique ID, ConsultationID) in the "Consultation" form/table. Of
course a patient may return with a NEW problem, generating a new record in
"History" with corresponding new record(s) in "Consultation."

The first two tables (PatientDemographics, History) are linked via
PatientNumber, the 3rd table (Consultation)is linked to the second (History)
via Consultation.ConsultationID to History.HistoryID.

For each record in the PatientDemographics Form, there is a history subform
visible within which is a Consultation subform (in table format). Thus at a
glance one can see the consultations that that particular patient has had for
that particular problem.