Transfering form data

[gsanderson99]

I have a form used to search and display information about a person.
That data is stored in table A. A command button allows me to open
another form where additional data is entered about that one person.
That data is stored in table B, which is linked to table A through an
ID number (not autonumber). The second form collects and saves the
data, via a "Save" command button just fine, however, how do I also
include the person's ID number and name in table B? I have tried an
append query at the time of save, but it does not work. Would an
unbound form work better? Thank you.

 

[Tom van Stiphout]

On Wed, 17 Mar 2010 14:41:33 -0700 (PDT), "(e-mail address removed)"

Let's first make sure you have the correct database design. It appears
you are storing Person data in two tables. That is not necessarily
wrong but it is unusual. Why are you doing it that way? Can you list
the important fields in each table?

-Tom.
Microsoft Access MVP

 

[gsanderson99]

On Wed, 17 Mar 2010 14:41:33 -0700 (PDT), "(e-mail address removed)"


Let's first make sure you have the correct database design. It appears
you are storing Person data in two tables. That is not necessarily
wrong but it is unusual. Why are you doing it that way?

The first table contains the usual info, name, address, phone, unique
ID. The second table contains answers to questions about the person
(all based on option groups). It is possible for a person to answer
the questions more than one time, such as a month or so after the
initial answers. I will be building a report that will list the
person's name and unique ID, as well as the answers to the questions
based on the date answered.

Can you list the important fields in each table?

The unique ID is the most important field and is the related field
between the two tables. It needs to append to the second table (as
well as the answers) when a person answers the questions in the second
form and selects the Save command button.

-Tom.
Microsoft Access MVP

Thank you, Tom!

Geoff

 

[Tom van Stiphout]

On Thu, 18 Mar 2010 08:20:59 -0700 (PDT), "(e-mail address removed)"

Hi Geoff,
Thanks for clarifying. In first instance you wrote "how do I also
include the person's ID number and name in table B". Now the answer is
clear: you ONLY include the ID number, NOT the name. That's the power
of a relational database for you. If you ever wanted to know the name
of the person giving an answer, simply join the two tables together in
a query, pick up the name from the Persons table, and use that query
as the RecordSource for your report.
Your approximate design should be:
tblPersons
PersonID autonumber PK
FirstName text(20) required
LastName text(30) required
etc.

tblQuestions
QuestionID autonumber PK
QuestionText text(255) required

tblAnswers
AnswerID autonumber PK
PersonID long integer required
QuestionID long integer required
DateAnswered datetime required
Answer text?
(Put a unique index on PersonID+QuestionID+DateAnswered)

Then in the Relationships window draw the lines between the
corresponding fields AND CHECK THE "ENFORCE RI" BOX.

Now you're on your way!

-Tom.
Microsoft Access MVP