Mainform/Subform Help Please

[TotallyConfused]

I have a Mainform and 2 subforms. When I enter text on subform 1, I want
that to be populated on subform 2. Subform 2 has many rows that are
associated with subform 1's ID. Currently, when I enter subform 1's text it
will only populate the first row on subform 2. How can I get it to populate
all of the rows on subform 2? I have tried different combinations in the
Subform properties on Master/Child links on both subforms and if I change
what I have now I loose the rows on my subform 2. I do not want to the rows
on subform 2. Can this be done, if so how? Thank you in advance for any
help you can provide me. Thank you.

 

[tina]

first thing we need to understand are the relationships of the underlying
tables. i'm guessing that Table1 (main form table) has a one-to-many
relationship with Table2 (subform1 table), and Table2 has a one-to-many
relationship with Table3 (subform2 table).

next, you didn't say *how* you've linked the two subforms. the only way i
know to do it, is to add an unbound textbox control to the main form (i'll
call it txtPK). set its' ControlSource to the primary key field of Table2
(subform1), as

=[SubformOneControlName].[Form]![PrimaryKeyFieldName]

then set the LinkMasterFields property of subform2 to [txtPK]. set the
LinkChildFields property to the corresponding foreign key field in Table3
(subform2).

hth

 

[TotallyConfused]

Hi Thank you for your response. I can't get this to work when I enter info
my text box in my suform 1 it does not populate the other rows if there is
more than one it will only fill the first row. I have the main form linked
to my subform to the id that has the one to many relationship. I there
anything else I can do or I am not doing? Is there any other way? Thank you.

 

[John W. Vinson]

Hi Thank you for your response. I can't get this to work when I enter info
my text box in my suform 1 it does not populate the other rows if there is
more than one it will only fill the first row. I have the main form linked
to my subform to the id that has the one to many relationship. I there
anything else I can do or I am not doing? Is there any other way? Thank you.

I think your expectations are what is in error, not your table setup!

Having two tables related does NOT automagically create new records in TableB
when you insert a record into TableA, nor should it. Relationships *prevent*
you from inserting records which would violate the relationship; they don't
automatically populate anything.

Could you describe the three tables? What type of information do they contain;
how are they related (both logically in the real world, and in the
relationships window); and how are you trying to link the forms and subforms?
What do you EXPECT to happen when you enter a record on the first subform, and
what (if any) code have you written to cause it to happen?

John W. Vinson [MVP]

 

[TotallyConfused]

I am not trying to enter new records. This is a tracking db tbl1 patients
tbl2 doctors. This db is just to track dates, ltrs sent, who has responded
and when. Doctors can have many relationships to patients. When entering
comments for a doctor, In my main form, I would like to have that same
comment field filled in the subform listing the patients assoc to the doctor.
I do not want to copy the comment for each patient the doctor has
associated. Tables are linked by member ID and doctor ID. I have one form
listing all doc and their patients. My main form you can look up the doc .
1st subform you have all the doc's information and comment field. 2nd
subform lists all pts associated with that doc dates and comment field. When
I look up a doc and select, if I enter comments I want the same comments to
apper on all the patients listed in the subform. I hope this helps. Thank
you.

 

[John W. Vinson]

I am not trying to enter new records. This is a tracking db tbl1 patients
tbl2 doctors. This db is just to track dates, ltrs sent, who has responded
and when. Doctors can have many relationships to patients. When entering
comments for a doctor, In my main form, I would like to have that same
comment field filled in the subform listing the patients assoc to the doctor.
I do not want to copy the comment for each patient the doctor has
associated. Tables are linked by member ID and doctor ID. I have one form
listing all doc and their patients. My main form you can look up the doc .
1st subform you have all the doc's information and comment field. 2nd
subform lists all pts associated with that doc dates and comment field. When
I look up a doc and select, if I enter comments I want the same comments to
apper on all the patients listed in the subform. I hope this helps. Thank
you.

Please clarify. I don't understand your table structure. Do you have two
tables? I suspect you should have THREE, if there is a many to many
relationship between doctors and patients!

Please list your current table structures in a format like

tablename
fieldname <Datatype> <primary key>
fieldname <datatype>
fieldname <datatype>

and indicate the Recordsource for each form/subform, and the Master and Child
Link fields for each subform.


John W. Vinson [MVP]

 

[TotallyConfused]

TBL 1 - Patients
File # (number)<primary Key>
Pt ID (number)
Pt NM (text)
PT Add (text)
Pt Ph (text)
Pt Comments (memo)

TBL 2 - Doc
Doc ID (number)<Doc ID>primary key
Doc NM (text)
Doc Ph (text)
Doc Fx (text)
Doc Add (text)
Doc Comments (memo)

TBL 3 - Schedule
File # (number)<primary key>
Date Send (date)
Date Rcvd (date)
Date Fx (text)

Subform 1 links = Doc Id
Subform 2 links = file #, doc ph, doc NM

 

[John W. Vinson]

TBL 1 - Patients
File # (number)<primary Key>
Pt ID (number)
Pt NM (text)
PT Add (text)
Pt Ph (text)
Pt Comments (memo)

TBL 2 - Doc
Doc ID (number)<Doc ID>primary key
Doc NM (text)
Doc Ph (text)
Doc Fx (text)
Doc Add (text)
Doc Comments (memo)

TBL 3 - Schedule
File # (number)<primary key>
Date Send (date)
Date Rcvd (date)
Date Fx (text)

Subform 1 links = Doc Id
Subform 2 links = file #, doc ph, doc NM

I don't see ANY relationship between Patients and Docs, or between Schedules
and either of the other tables. If you have a list of Docs on one subform, and
a list of Files on the second subform, there does not appear to be any way to
associate them. You say that Subform 1 Links = Doc ID; but there IS no Doc ID
to link to in the patients table!

Could you step back and describe these three tables? What real-life Entities
(persons, events, things) do they represent? How are they related in the real
world - e.g. "Each Patient will be treated by one or more Doctors, and each
Doctor will treat one or more Patients"?

I *suspect* that you've made a very common error: jumping into form design
before you have your table structure fully worked out!

John W. Vinson [MVP]

 

[TotallyConfused]

The relationship between the tables is the File #. I will review my tables.
Thank you.

 

[John W. Vinson]

The relationship between the tables is the File #. I will review my tables.
Thank you.

There is no File# in the Doc table, and - again - no Doc ID in the Patients
table to provide a link; and if the File # is the primary key of both Tbl 1
and Tbl 3, that means that the Schedule table can have one and only one record
for each patient. That doesn't sound right for a schedule table - surely each
patient would have multiple dates!

I suspect that the Schedule table should have File # (and, by the way, it's
best not to use either blanks or special characters like # in fieldnames) as a
nonunique foreign key field, and its own primary key.

Your Subform 2 links also don't seem correct: there is no File # in the Doc
table, and there is no doc ph or doc NM in the Patients table. There's nothing
to link these two tables AT ALL, and putting random fields in the link field
won't help you! The "Master Link Field" should be one (or rarely two or three)
fields that occur uniquely in the main form (one side table); the Child Link
Field should be matching foreign key fields in the child table. They work
together - a Master Link Field without a Child Link Field is like one end of
a rope.

John W. Vinson [MVP]