link IDs of various tables

[leaf]

Hello there,

I have foreign keys in various tables and have linked the
foreign keys to the primary key in the main table. I
would like to automatically populate the ID which is the
primary key in the main table reflected in the main form
when I open a subform in the main form.

This subform has an optional relationship with the main
form. My subform is called Age55 and my mainform is
called CallerInfo. There could be 10 records in
Table_Callerinfo but only 2 records in Table_Age55.

I would like to achieve the following:
when I open the subform Age55 in the mainform Callerinfo,
I would like the ID from the mainform Callerinfo to
populate in the subform Age55.

I am still working on it and would appreciate some
detailed help.

Thanks!

leaf

 

[Pavel Romashkin]

If you have a frmMain that is based on Table_Callerinfo with a field
called CallerInfoID, and a subform sfAge55 that is based on Table_Age55
having the FK CallerInfoID, then when you open the main form, the
subform will show all records from Table_Age55 filtered by the frmMain
CallerInfoID. If you go to a record in frmMain for whos CallerInfoID
there are no records in sfAge55, there will be nothing in the subform.
However, if you now create a record in the subform, the FK in
Table_Age55 will be set tthe current CallerInfoID.
Basically you don't have to do anything except properly linking frmMain
and sfAge55 on the CallerInfoID field.
Hope this helps.
Pavel

 

[Pavel Romashkin]

I am baffled.
I thought you said that CallerInfo is already linked to Table_Age55?
Then linking parent/child fields of subform should do the trick.
If this still doesn't work, we should perhaps start over with:
What fields are in Table_Age55
What wields are in CallerInfo
What is the PK and FK in each
Which one is the source for the main form
Which one is the source for subform

Cheers,
Pavel

 

[leaf]

Did you mean to create a query with all the fields from
table_Age55 and be the new record source for the
form_Age55?

Thanks,

Yeying

 

[leaf]

Sorry I just saw your message below.
In Table_Age55, I have ID and specific questions for
people with age beyond or equal 55.
In Table_CallerInfo, I have ID and contact info such as
patient address.
The PK in Table_CallerInfo is ID.
The FK in Table_Age55 is ID.
The main form is CallerInfo.
The sub form is Age55.

Thanks,
Leaf

 

[Pavel Romashkin]

Ok, this is what I thought.
If you have ID of subrorm linked with IF of the main form, for every
record in CallerInfo you should have only those records in Table_Age55
displayed in the subform that have the same ID. Is this happening?
If you happen to select a CallerInfo record that has no related records
in Age55, you should have an empty subform. Is this happening?
If you have an empty subform and type in information in the subform, it
should create a new record in Table_Age55 with the ID automatically
entered from the currently selected record from CallerInfo. Is this happening?
If you answer Yes to all of the above, the subform is functioning
properly. If so, which additional functionality do you desire?
Pavel

 

[leaf]

Thank you very much for your help.
I have been trying to find where I can set parent child
relationship because the ID in my main form (parent form)
is not automatically poping up in a sub from (child form)
when I open the subform embedded in the main form.

Leaf

 

[Pavel Romashkin]

In Main form design view, select the subform control of the Main form
and open its Properties (that is, *select* it, not dbl-click it to get
into the properties of the subform Form itself). There will be
LinkChildFields and Parent fields in the Properties, under Data tab.

Pavel

 

[leaf]

Hello,

I did not find LinkChildFields.
Anyway, I have linked the IDs, but now the ID in subform
will be overwritten each time. So, if I entered 5
records, there will only be the last one recorded in my
subforms, even though there are 5 records in the main form.

Leaf