Table Value Update with Value in Another Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I pass the data value populated via a form from one table to that
table and to another table? I have two tables which the records need to be
kept together using a the same primary key value, but I can't figure out how
to pass the value entered in the form to the second table. I have tried
using an update query and set value as a macro. Neither worked.
 
You'll need to give a bit more explanation of what you're trying to do.
Storing the same value in more than one table is an extremely unusual
requirement, and generally indicates that the tables haven't been properly
normalized.
 
Normally what you're doing is accomplished automatically in the background
by using a form with a subform. So you'd use the main form to select/enter
the parent record, then the PK would automatically be inserted into all
records in the subform. For this to work, you also need to define the
relationship in the relationships window.

HTH;

Amy
 
I am building a questionaire database that will collect answers to questions
then generate a work-form to allow users to document their corrective action
plans for any "NO" or "Not Applicable" answers.

There are 83 unique areas of work which I am using as my primary key.
There are about 70 questions that need to be answered by each area of work.

I tried building a table to store the value for each question, answer, and
their corrective action plan, but creating a data entry form proved
difficult. I tried using a parent form for selecting the area of work with
separate sub-forms for each question, but it does not populate the area of
work field in each of the question tables. Since I need to re-use this value
to generate the corrective action plan, it must be populated.

I tried building all the data in one table; however, MS Access said there
are too many fields, so I split the data collection into two tables. I
created a query to join both tables and collect all the data; however, the
area of work field in the second table never gets populated. I have tried
using SetValue in a macro and tried to use a update Query when the form loads
to populate the area of work in the second table.
 
dsc2bjn said:
I am building a questionaire database that will collect answers to
questions
then generate a work-form to allow users to document their corrective
action
plans for any "NO" or "Not Applicable" answers.

There are 83 unique areas of work which I am using as my primary key.
There are about 70 questions that need to be answered by each area of
work.

I tried building a table to store the value for each question, answer, and
their corrective action plan, but creating a data entry form proved
difficult. I tried using a parent form for selecting the area of work
with
separate sub-forms for each question, but it does not populate the area of
work field in each of the question tables.
Click to expand...

What you need it something like this:

tblAreas
=======
AreaID (pk)
AreaDesc
AreaEtc.

tblQuestions
=========
QuestionID (pk)
AreaID (foreign key to tblarea)
QuestionStem

tblOptions
=======
OptionID (pk)
QuestionID (fk to tblquestions)
OptionText
IsRight

tblUsers
======
UserId
FirstName
LastName
Etc

tblUserResponses
============
UserId (pk)
OptionID (fk to tbloptions)

So now, open the relationship window and show all your tables. Every place
it says foreign key to the ____ table, click the field in the table where it
is a primary key and drag it to the same field in the table where it is a
foreign key.

You should then be able to create a tblAreas form that has a tblQuestions
subform. Once you have that set up, you should be able to add a tblOptions
subform to the tblQuestions subform that allows you to input the potential
answers that the student could click.

Once all that is good, you'll probably want to create a form that allows
some way to determine the user (whether the user is selected from a drop
down or what) that then jumps you a form that is based on a join between one
or more queries that joins all the questions with the user in question.
That's going to be the tough part, so you should probably post back when
you've gotten to that point. I usually don't use Access at all for that
piece, because Access isn't terribly good at that particular type of task.
But this should get you started up to the point where all your questions are
in the database.

HTH;

Amy
 
Back
Top