Subform Question

  • Thread starter Thread starter Jonathan Smith
  • Start date Start date
J

Jonathan Smith

I have a Main Form (frm401), which has a Sub-Form Control (frm402s)on it.

On the Main Form (frm401) there is a combo-box [cmbTherapist]. After
selection of a Therapist from thsi combo-box, I would like the Sub-Form
field [txtTherapist] to automatically update with the same Therapist
selected in the [cmbTherapist] on the Main Form.

I cannot seem to get this to work. Any ideas or suggestions?
 
Jonathan Smith said:
I have a Main Form (frm401), which has a Sub-Form Control (frm402s)on it.

On the Main Form (frm401) there is a combo-box [cmbTherapist]. After
selection of a Therapist from thsi combo-box, I would like the Sub-Form
field [txtTherapist] to automatically update with the same Therapist
selected in the [cmbTherapist] on the Main Form.

I cannot seem to get this to work. Any ideas or suggestions?
Click to expand...

Use the MasterLink and ChildLink properties of the subform control. Set
the ChildLink property to the appropriate field in the subform and set the
MasterLink property to the name of the ComboBox.

The linking wizard will not allow this if the main form is unbound, but you
can make the entries manually and they will work.
 
Jonathan Smith said:
I have a Main Form (frm401), which has a Sub-Form Control (frm402s)on
it.

On the Main Form (frm401) there is a combo-box [cmbTherapist]. After
selection of a Therapist from thsi combo-box, I would like the
Sub-Form field [txtTherapist] to automatically update with the same
Therapist selected in the [cmbTherapist] on the Main Form.

I cannot seem to get this to work. Any ideas or suggestions?
Click to expand...

Use the MasterLink and ChildLink properties of the subform control.
Set the ChildLink property to the appropriate field in the subform and
set the MasterLink property to the name of the ComboBox.

The linking wizard will not allow this if the main form is unbound,
but you can make the entries manually and they will work.
Click to expand...

I have tried this, but it does not appear to be working.

I select a Therapist on the Main Form, and nothing appears in the
subform.
 
I have a Main Form (frm401), which has a Sub-Form Control (frm402s)on it.

On the Main Form (frm401) there is a combo-box [cmbTherapist]. After
selection of a Therapist from thsi combo-box, I would like the Sub-Form
field [txtTherapist] to automatically update with the same Therapist
selected in the [cmbTherapist] on the Main Form.

I cannot seem to get this to work. Any ideas or suggestions?
Click to expand...

Question: are you trying to store the TherapistID redundantly in both
the main form's table and the subform's table? If so, why? What if
there are a dozen records on the subform - should they all have the
same Therapist, or should the user be able to choose different ones?
 
Jonathan Smith said:
I have tried this, but it does not appear to be working.

I select a Therapist on the Main Form, and nothing appears in the
subform.
Click to expand...

Are you sure the value of the ComboBox (the bound column) is giving you the
matching value? IOW if the ComboBox has multiple columns (even hidden ones) you
need to make sure that the bound column is the one that will match up to the
entries in the subform.
 
I have a Main Form (frm401), which has a Sub-Form Control (frm402s)on
it.

On the Main Form (frm401) there is a combo-box [cmbTherapist]. After
selection of a Therapist from thsi combo-box, I would like the
Sub-Form field [txtTherapist] to automatically update with the same
Therapist selected in the [cmbTherapist] on the Main Form.

I cannot seem to get this to work. Any ideas or suggestions?
Click to expand...

Question: are you trying to store the TherapistID redundantly in both
the main form's table and the subform's table? If so, why? What if
there are a dozen records on the subform - should they all have the
same Therapist, or should the user be able to choose different ones?
Click to expand...

Yes, I am attempting to store the TherapistID redundantly in both the
Main Form Table [tbl401] and the Sub Form Table [tbl402], this is being
done due to the need to export [tbl402] to an Excel Worksheet.

For any given day, a single TherapistID [frm401] could have as many as
ten (10) entries on the Sub Form [frm402]. The End User should never be
able to have a different TherapistID on [frm401] and [frm402] on the same
screen.

I hope this helps!!! I am way lost.
 
Yes, I am attempting to store the TherapistID redundantly in both the
Main Form Table [tbl401] and the Sub Form Table [tbl402], this is being
done due to the need to export [tbl402] to an Excel Worksheet.
Click to expand...

You're apparently assuming that you must have the TherapistID in
tbl402 in order to export it to an Excel Worksheet.

This assumption is very common, but it is WRONG.

You can create a Query joining tbl401 to tbl402, joining by the
patientID (or whatever), and export to Excel *from that Query* - and
that's just what I'd suggest that you do!

If you really want to store the data redundantly, then you could use
the AfterUpdate event of the mainform therapist combo box to set the
Default property in the subform:

Me!subfTbl402.Form!txtTherapist = Me!txtTherapist

(using of course your own control names) - but it is *not* necessary
to denormalize your tables in order to accomplish your goal!
 
Rick Brandt said:
Are you sure the value of the ComboBox (the bound column) is giving you the
matching value? IOW if the ComboBox has multiple columns (even hidden ones) you
need to make sure that the bound column is the one that will match up to the
entries in the subform.
Click to expand...

I believe I misunderstood your original post. Do you want the subform to
be filtered to show records matching the therapist in the main form or do
you want the entry in the main form to change values in the records already
displayed in the subform? If the latter than disregard my previous
responses.
 
Yes, I am attempting to store the TherapistID redundantly in both the
Main Form Table [tbl401] and the Sub Form Table [tbl402], this is being
done due to the need to export [tbl402] to an Excel Worksheet.
Click to expand...

You're apparently assuming that you must have the TherapistID in
tbl402 in order to export it to an Excel Worksheet.

This assumption is very common, but it is WRONG.

You can create a Query joining tbl401 to tbl402, joining by the
patientID (or whatever), and export to Excel *from that Query* - and
that's just what I'd suggest that you do!

If you really want to store the data redundantly, then you could use
the AfterUpdate event of the mainform therapist combo box to set the
Default property in the subform:

Me!subfTbl402.Form!txtTherapist = Me!txtTherapist

(using of course your own control names) - but it is *not* necessary
to denormalize your tables in order to accomplish your goal!
Click to expand...

Thank you.
 
Back
Top