Putting a field on a form from another record

[Jeff]

Hi

I'm currently working on a form (that is tied to the parent)with a
subform(one child of the parent), and the user of the database would
like to see a field from another child of the parent displayed on the
screen. The date of birth from the subform would be used to find a
matching DOB on the other child.

I'm not sure how to do this, and would appreciate any help that could
be provided!

Thanks

Jeff

 

[tina]

hmm, it's interesting that you would have the same date of birth stored in
two different child tables. can you give us some specific information about
the "real" data that your parent and child tables hold, and how they're
related?

hth

 

[Jeff]

Sure, this is a database that has evolved over time, which explains a
bit of the repetition. I'm the curent support person (on contract.)

The company is interested in information about impoverished families.
The parent record is actually information about a parent. The one
child record is a history of immunizations. That record had the DOB
originally. Then they added a program regarding follow-up visits with
the children. This is the 2nd child record to the same parent.
Apparently originally there was no way to determine the child being
referenced in the child record, except through a rather awkward set of
calculations (e.g. would the child have been roughly 1 year old on
this date, etc.) So they added the DOB to the follow-up visits.

Does that help?

Jeff

 

[tina]

yes, it does help, thanks. if you were building the database, my first
recommendation would be that you restructure the tables to include a table
listing each child (person, not table <g>), and use one or more child tables
of *that* table to record the various activities associated with a child
(person). but, given your situation, we'll forego addressing the root cause
of the problem, and see if we can get around it at the form level.

you can link the second child subform to the first, within the main form.
i've never tried exactly the following setup, but it might work for you:
make sure both subforms are linked to the parent form with the standard
primary key/foreign key field references in each subform control's
LinkChildFields and LinkMasterFields properties. then add an unbound textbox
control to the *main* form, set its' Visible property to False, set its'
ControlSource property to the name of the date of birth field (i'll call it
DOBa) in the first subform control (i'll call it ChildA), as
=[ChildA].[Form]![DOBa]
you'll need to substitute the correct names in your database, of course.
name the textbox control txtDOB.
in the second subform control, *add* the name of the date of birth field to
the LinkChildFields property. so that property will show two fieldnames:
the foreign key field that links the table to the parent table, and the date
of birth field.
still in the second subform control, *add* the name txtDOB to the
LinkMasterFields property. so that property will show two names: the
primary key field of the parent table, and the name of the unbound textbox
control on the parent form.

so (hopefully) when you choose a record on the parent form, the first
subform should display all the child records related to that parent record.
and the second subform should display the records related to that parent
record *where the date of birth matches the current record in the first
subform*.

hth

 

[Jeff]

I'm trying this, but when I try to add the 2nd field (txtDOB) to the
LinkMaster/LinkChild, I only get a list of fields from the parent
record and not from the form itself. Am I doing something wrong?

 

[John W. Vinson]

I'm trying this, but when I try to add the 2nd field (txtDOB) to the
LinkMaster/LinkChild, I only get a list of fields from the parent
record and not from the form itself. Am I doing something wrong?

Nope, the Access developers did.

Just type it in. It's not on the list to choose from but if you put it there
yourself it will work.

John W. Vinson [MVP]

 

[Jeff]

Hmm, I get a message that says The text you entered is not an item in
the List. The value txtDOB is exactly the same spelling and case as
that which shows in the Tab order screen and other places. I'm not
sure what to do.

Also to check out the value, I made the txtDOB field visible, and it
showed the dreaded #Name? in it.

Any thoughts?

Jeff

 

[John W. Vinson]

Hmm, I get a message that says The text you entered is not an item in
the List. The value txtDOB is exactly the same spelling and case as
that which shows in the Tab order screen and other places. I'm not
sure what to do.

Also to check out the value, I made the txtDOB field visible, and it
showed the dreaded #Name? in it.

What is its Control Source? #Name? means that Access doesn't recognize the
Control source as a valid fieldname in the form's Recordsource. If the Control
Source is DOB, doublecheck that there is in fact a field named DOB in the
form's Recordsource query (and add it if necessary).

John W. Vinson [MVP]

 

[Jeff]

then add an unbound textbox
control to the *main* form, set its' Visible property to False, set
its'
ControlSource property to the name of the date of birth field (i'll
call it
DOBa) in the first subform control (i'll call it ChildA), as
=[ChildA].[Form]![DOBa]

This was the suggestion from earlier in the message thread. I added
the unbound textbox (named txtDOB) and defined its ControlSource as
the =Form![Van Visit FYOL Birth subform]![Child DOB]

Jeff

 

[tina]

you got it backwards, hon. do it the way i posted:

=[ChildA].[Form]![DOBa]

note that in the above example, "ChildA" is the name of the *subform
control* that "contains" the subform - not the name of the subform itself.
sometimes the two names are the same, but sometimes not. i personally never
use the same name for both; by using different names, i always know
immediately which object i'm working with in code and/or expressions. if
you're not familiar with the difference, go to
http://home.att.net/~california.db/instructions.html and click the
SubformControlName link for illustrated instructions.

hth