Copying values in one form to another field in another form

[Tom D]

Hello, Thanks for checking in,

If in the same database I have a Form called "Main" with a Field called
"Breed" and the Value in "Breed" is "Golden Retriever". How could I copy
that value into a Form called "Details" and a Field called "Your-Breed"
without physically copying the value and pasting it (since I want to be able
to do this quickly for hundreds of other values using the respective fields)?
The form "Main" was created from the table "Main" using autoform. The form
"Details" was created from the table "Details" using autoform. Combining the
source tables "Main" and "Details" in a query is not a solution in this case,
because I want the Value "Golden Retriever" to exist in the field
"Your-Breed". I am using Access 2003. Ideas and if possible code for
placement in properties, macros, etc is appreciated.

Thanks for your time,
Tom

 

[Klatuu]

Put your hands behind your head and back away from the computer!

Now, first you need to rethink your database design. It violates database
normalization rules to carry the same data in multiple places.

What does the Main table represent?
Why does it have a field called Breed?
What does the table Details represent?
What is the difference between the field Your-Breed and the Breed field in
Main?

There is also another error apparent. Good naming conventions will save you
a lot of grief. Jet is more forgiving than some database engines, but to
avoid possible ambiguity and to reduce problems, the basic rules for field
names are Use only Letters, Numbers, and the Underscore. Do not use any
other special characters or spaces. Do not use any Access reserved words
like Date, Year, Month, Day, Name, etc.

We will be happy to make some suggestions on database design if you will
post back with what it is you want to accomplish.

 

[Tom D]

Thanks for your advice.

The database described is a fictional database representing a real database
that I need to keep confidential on many details. I was using the “dog
owner†database as an example that reflects the manner in which I have been
working with the objects and values and to learn if their is a simple way to
copy values across forms.

I have one main table and form that has the personal details of different
people. I have 5 other tables and forms that focus on specific health issues
of each person (one table/form per health issue) and includes fields that
relate to that health issue. Only a few people have a full five health
issues. Each form was developed from its respective table using autoform. I
will be sharing this database with other users associated with the health
issues.

Breed represented the name field “Name1â€. Your-Breed represented “Name2â€.
All six of the forms will have a name field Name1, Name2, Name3, Name4, Name5
and Name6 respectively. I want the name to appear identical in all the forms
and for the ID value to be identical in all forms. Having the identical
values in all of the objects has helped to assure that the health issues are
not mixed and that the other users have confidence in the data they are
viewing.

All the tables are joined in a relationship via the ID field. All the
queries, macros, and reports work as the database currently stands.

I tried other methods such as creating queries that combined the main table
with the other tables to finally create forms with the name control source
always pointing to Name1. Since this method does not place the name value in
the 5 health issue tables, I am unable to keep the ID value (auto number)
consistent for each record across the tables. I use all the tables for
reports and queries.

I am happy to know of a way to assure identical ID and name values across
the objects that is done under proper normalization rules.

Many thanks,
Tom

 

[John Vinson]

I am happy to know of a way to assure identical ID and name values across
the objects that is done under proper normalization rules.

First off... you cannot, and should not even TRY, join Autonumber to
Autonumber. For that matter, it's very rare indeed to join Primary Key
to Primary Key.

It sounds like you have a classic Many to Many relationship. You have
many Patients; you have multiple (five, currently, maybe someday there
will be more) HealthIssues; each person may have zero, one, or more
HealthIssues; each health issue may afflict zero, one or more
Patients.

Is that a fair assessment of the real-life situation?

If so, you should NOT be storing health issues in fields. Instead,
consider three tables:

Patients
PatientID <primary key>
(This might be an Autonumber, a manually assigned unique ID, or a
programmatically assigned unique ID)
<demographic information>

HealthIssues
HealthIssueID <primary key>
(This might be an Autonumber, or - probably better - an
insurance agency standard diagnosis code; just so it's unique and
unambiguous)
<fields about this health issue as an entity>

PatientIssues
PatientID <Long Integer, if Patients.PatientID is an Autonumber;
otherwise the same datatype as Patients.PatientID>
HealthIssueID <ditto for HealthIssues.HealthIssueID>
<other fields concerning THIS patient's affliction with THIS
condition>

Note that the PatientIssues table does NOT contain the patient's name;
nor does it contain the name of the condition! It is *NOT* necessary,
in fact it's a very bad idea, to store such descriptive information
redundantly in a second table. Just store a link, and use the tools
Access provides - Queries, Forms, Combo and Listboxes, Reports - to
pull the information together.

Note also that the very subject line of your question reveals a
misconception. You cannot pull information "from a Form" because
information isn't STORED in Forms. It's stored in Tables, and only in
Tables; the form is just a tool, a window onto that data.

John W. Vinson[MVP]

 

[Klatuu]

Tom,

I could not have said it any better than John. You would be well advised to
heed his recommendations. One point he made regarding storing a name is more
important than it may appear. In addition to unnecessarily increasing
database size, it becomes a maintenance problem. If Mary Smith marries Larry
Jones and you have her name in multiple tables, you have to change all the
tables and records where her old name is. The only atomic piece of data that
ever needs to be repeated is the keys that relate the data in the various
tables.

 

[Tom D]

Thank you much John and Klatuu. This is very helpful!
Sincerely, Tom