Need to Link subforms in tabs on main form

[Guest]

I have a form based on a query that has ClientID and ClientName from Client
table.
I have two tabs on the form. One has subform for Child Info based on Child
Info table and the other has subform for Child Meds History based on Child
Meds table.

Common fields of Child Info table and Chold Meds table are ClientID and
ChildID

I need to enter child info and then child meds. The subforms are linked to
main for by ClientID.

I need code for Oncurrent event so that when I go to Child Med tab/subform
it is linked to the Child that was open on Child Info tab/subform


Thanks for your help.

 

[kingston via AccessMonster.com]

Maybe you should try a nested subform. This should automatically link the
records for you:

Main form - ClientID
Subform - ChildID (linked to ClientID)
Subform of Subform - Child Meds (linked to ChildID)

 

[Guest]

I expect you are right, but I cannot get the subform to work with
Main form - ClientID
Subform ChildID

It only works with both set to ClientID

Therein probably lies my problem.
Do you have idea as to why I cannot link as you suggested?

Thanks

 

[kingston via AccessMonster.com]

Can you provide more information about your table structure? I'm suggesting
that you have three tables with the following keys:
Client (ClientID)
Child (ChildID, ClientID)
Child Medications (ChildID, MedID)

However, it sounds like you have ClientID in Child Medications for some
reason. If that is the case, your subform for Child Medications may need to
be linked by two keys: ClientID and ChildID. This seems overly complicated
to me but you may need it to be this way.

I expect you are right, but I cannot get the subform to work with
Main form - ClientID
Subform ChildID

It only works with both set to ClientID

Therein probably lies my problem.
Do you have idea as to why I cannot link as you suggested?

Thanks

Maybe you should try a nested subform. This should automatically link the
records for you:

[quoted text clipped - 19 lines]

 

[Guest]

I appreciate your help. I am not a programmer, but have some success with
ACCESS wizards etc. This project is for a nonproft that cannot aford to hire
a programmer.

Here is structure of the three tables in question. It appears that I hdo
not need ClientID in the ChildMeds table.

Page: 3 Table: tblClient_InfoV1
Columns
Name Type Size
ClientID Long Integer 4
CaseNumber Text 50

Page: 1 Table: tblChild_InfoV1
Columns
Name Type Size
ChildID Long Integer 4
ClientID Long Integer 4


Page: 2 Table: tblChildMedsV1
Columns
Name Type Size
ChildMedHistID Long Integer 4
ChildID Long Integer 4
ClientID Long Integer 4

Main form based on thjis query
Page: Query: qryClientNameV1
SQL
SELECT tblClient_InfoV1.ClientID, [Client_LN] & ", " & [Client_FN] & " " &
[Client_MI] AS ClientName
FROM tblClient_InfoV1;

ClientID and ClientName in header of main form
Subform Child_Info linkage: Main form -ClientID; subform - ClientID
Subform ChildMeds linkage: Subform - ChildID; SubSubform - ChildMedHistID

I created subform ChildMeds from table ChildMeds

Can you provide more information about your table structure? I'm suggesting
that you have three tables with the following keys:
Client (ClientID)
Child (ChildID, ClientID)
Child Medications (ChildID, MedID)
Thanks

However, it sounds like you have ClientID in Child Medications for some
reason. If that is the case, your subform for Child Medications may need to
be linked by two keys: ClientID and ChildID. This seems overly complicated
to me but you may need it to be this way.

I expect you are right, but I cannot get the subform to work with
Main form - ClientID
Subform ChildID

It only works with both set to ClientID

Therein probably lies my problem.
Do you have idea as to why I cannot link as you suggested?

Thanks

Maybe you should try a nested subform. This should automatically link the
records for you:

[quoted text clipped - 19 lines]

Thanks for your help.

 

[kingston via AccessMonster.com]

Good, now structure your forms as follows:

Main Form - Client Information
Subform - Child Information linked via ClientID
Sub-subform - Child Medical information linked via ChildID

In other words, create the subform first with the sub-subform. Make sure the
IDs are linked properly and that that it works independently of the client
information. Then embed the subform into the main form and link the two via
ClientID. You can have the other keys in your table, but they won't be used
to link these forms together. Sometimes this stuff gets confusing and you
might be better off starting fresh so you don't have to figure out what
settings you may have changed while trying to get things to work. HTH.

I appreciate your help. I am not a programmer, but have some success with
ACCESS wizards etc. This project is for a nonproft that cannot aford to hire
a programmer.

Here is structure of the three tables in question. It appears that I hdo
not need ClientID in the ChildMeds table.

Page: 3 Table: tblClient_InfoV1
Columns
Name Type Size
ClientID Long Integer 4
CaseNumber Text 50

Page: 1 Table: tblChild_InfoV1
Columns
Name Type Size
ChildID Long Integer 4
ClientID Long Integer 4

Page: 2 Table: tblChildMedsV1
Columns
Name Type Size
ChildMedHistID Long Integer 4
ChildID Long Integer 4
ClientID Long Integer 4

Main form based on thjis query
Page: Query: qryClientNameV1
SQL
SELECT tblClient_InfoV1.ClientID, [Client_LN] & ", " & [Client_FN] & " " &
[Client_MI] AS ClientName
FROM tblClient_InfoV1;

ClientID and ClientName in header of main form
Subform Child_Info linkage: Main form -ClientID; subform - ClientID
Subform ChildMeds linkage: Subform - ChildID; SubSubform - ChildMedHistID

I created subform ChildMeds from table ChildMeds

Can you provide more information about your table structure? I'm suggesting
that you have three tables with the following keys:
Client (ClientID)
Child (ChildID, ClientID)
Child Medications (ChildID, MedID)
Thanks

However, it sounds like you have ClientID in Child Medications for some
reason. If that is the case, your subform for Child Medications may need to

[quoted text clipped - 17 lines]

 

[Guest]

I apologize for not being able to reply sooner. But the good news is that I
believe you have solved my problem! I set the form and subforms per your
instructions, ran some tests and all appears to function properly.

I appreciate your help very much, but I appreciate your professionalism just
as much! You were patient and adept at getting me to provide the information
you needed to help me. You were never condescending and that means a lot to
this 70-year old guy.

Thank you again for sharing your expertise.

Good, now structure your forms as follows:

Main Form - Client Information
Subform - Child Information linked via ClientID
Sub-subform - Child Medical information linked via ChildID

In other words, create the subform first with the sub-subform. Make sure the
IDs are linked properly and that that it works independently of the client
information. Then embed the subform into the main form and link the two via
ClientID. You can have the other keys in your table, but they won't be used
to link these forms together. Sometimes this stuff gets confusing and you
might be better off starting fresh so you don't have to figure out what
settings you may have changed while trying to get things to work. HTH.

I appreciate your help. I am not a programmer, but have some success with
ACCESS wizards etc. This project is for a nonproft that cannot aford to hire
a programmer.

Here is structure of the three tables in question. It appears that I hdo
not need ClientID in the ChildMeds table.

Page: 3 Table: tblClient_InfoV1
Columns
Name Type Size
ClientID Long Integer 4
CaseNumber Text 50

Page: 1 Table: tblChild_InfoV1
Columns
Name Type Size
ChildID Long Integer 4
ClientID Long Integer 4

Page: 2 Table: tblChildMedsV1
Columns
Name Type Size
ChildMedHistID Long Integer 4
ChildID Long Integer 4
ClientID Long Integer 4

Main form based on thjis query
Page: Query: qryClientNameV1
SQL
SELECT tblClient_InfoV1.ClientID, [Client_LN] & ", " & [Client_FN] & " " &
[Client_MI] AS ClientName
FROM tblClient_InfoV1;

ClientID and ClientName in header of main form
Subform Child_Info linkage: Main form -ClientID; subform - ClientID
Subform ChildMeds linkage: Subform - ChildID; SubSubform - ChildMedHistID

I created subform ChildMeds from table ChildMeds

Can you provide more information about your table structure? I'm suggesting
that you have three tables with the following keys:
Client (ClientID)
Child (ChildID, ClientID)
Child Medications (ChildID, MedID)
Thanks

However, it sounds like you have ClientID in Child Medications for some
reason. If that is the case, your subform for Child Medications may need to

[quoted text clipped - 17 lines]

Thanks for your help.

 

[kingston via AccessMonster.com]

I'm glad I could be of help. Good luck.

I apologize for not being able to reply sooner. But the good news is that I
believe you have solved my problem! I set the form and subforms per your
instructions, ran some tests and all appears to function properly.

I appreciate your help very much, but I appreciate your professionalism just
as much! You were patient and adept at getting me to provide the information
you needed to help me. You were never condescending and that means a lot to
this 70-year old guy.

Thank you again for sharing your expertise.

Good, now structure your forms as follows:

[quoted text clipped - 62 lines]