Updating multiple tables using a query driven Form

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

Guest

I have three tables - Staff table - Questions table and a Score table
the relationships are Staff to Questions 1 to many; Questions to Score 1 to
many
when I run the query it does show the junk data I added, When I bring up the
Form, it shows the all fields correctly but it will not allow me to add
records or update the junk record I added. I want the Score table to be
updated/added and to just display the questions and Employee information.
Where am I going wrong?
 
Hi Bunky,

Many times queries are not updateable - you can't add records to them. In
your case, with one-to-many relationships, your "one" side should be the
main form, then insert a subform for your "many" table data, linking via the
relational field. TO be more precise, create form based on Staff, then
insert a subform based on Questions, then insert into the Questions subform,
yet another subform based on Score. suing the wizard will make the linking
fairly simple.
 
As long as your relationships are setup properly (and they probably are if
your query works) then no, you can simply use the tables themselves as the
recordsource.
 
Hi Susan,

Let me tell you exactly what I'm facing; I sure you could do it in a flash
but it is not working as I want it to.

Employee table - I want to be able to select a person from a list and have
it populate the skill, full name, and supervisor. In the past I was able to
do this with some VB code and think this is still a viable method. Key is
user

Questions table - This is a series of 35 questions. I want all the
questions to immediately be displayed on open with their appropriate numbers.
These questions and numbers may change based on skill level. Key is Question
number.

Score table - this table contains the number of the question and the score
that was entered, User, and monitor Date. Key is Auto number.

So basically, the Employee table and the questions table should not have any
data entry done except to find the employee being monitored. The Scores
table needs something to deliniate one set of scores from another, so I put
in an Auto number as a key.

Ideas?
 
How are they related? I would expect:
Employee - one-to-many - Questions, on field EmployeeID (exists in both
tables)
Questions - one-to-many - Score, on field QuestionID (exists in both tables)

Now I would base your main form on table Employee.
Then on the Main form, I'd insert a subform, based on table Questions, with
the linking field EmployeeID.
Then on Questions subform, I'd insert another subform, based on table Score,
with the linking field QuestionID. Also, I'd make this a continuous form for
best viewability.

I hope this makes sense!
 
Employee - one to many with Score on EmployeeID

Questions - one to many to Score on Question #

I got the pull down on the main form working fine. I then created a query
to show all the questions at once and that works once I change the pull down
and go back to the original. Then it remains fine. I created the score
subform on the main form along side the question subform. I can enter scores
and such fine for the 1st person only. When I try to change to another
person, it tells me I am trying to create duplicate keys. Does the same
thing when I try to scroll down in the question list.

Does this make sense to you? It doesn't to me.
 
You have the Score subform embedded in the Main subform, but there's no
direct relationship - what are the Score subforms MasterLinkk and ChildLink
values?
 
Hummm - everything is 'user' . . does that mean I must relate Score directly
to Employee and directly to Question?
 
If the relationship is:
Employee/EmployeeID = ONE to Questions/EmployeeID = MANY
Then the MasterLink and ChildLink properties should show as EmployeeID

Similarly, if the relationship is:
Employee/EmployeeID = ONE to Score/EmployeeID = MANY
Then the MasterLink and ChildLink properties should show as EmployeeID

If this is how your relationships are setup, you can indeed have 2 subforms
on one main form. I *thought* you had:
Employee/EmployeeID = ONE to Questions/EmployeeID = MANY
Then the MasterLink and ChildLink properties should show as EmployeeID

Questions/QuestionNo = ONE to Score/QuestionNo= MANY
Then the MasterLink and ChildLink properties should show as QuestionNo


I'm a bit confused now <smile> What is Users? What table does this live in?
<lost>

Oh, and if you actually have a field named Question# that's not a good idea,
using the hashmark...
 
Okay, I blew it. The user field name is the Employee Id. ...in the heat of
battle...

I have the Employee / User linked to Questions / User in a one to many
relationship.

I have the Employee / User linked to Scores / User in a one to many
relationship.

I have the Question tbl / Question number linked to Scores / Question number
in a one to many relationship

I have 1 main form with two subforms on it. Both of the subforms have
MasterLink and Childlink properties set to 'User'.

Now If I understand you correctly, the Score subform should have the two
link properties set to Question number? Correct? If I do that, the user
name is not showing on the Score subform.
 
Nah you didn't blow it <g> we just got ahead of ourselves!

If both Scores and Questions are related to Employee via the field User you
don't need to relate Scores and questions at all - the Employee link already
joins those 2 tables. See what happens when you remove that relationship.
 
Susan,

I appreciate your help. Unfortunately I still am getting the duplicate key
message and can't find out why. In addition, it won't allow me to make
changes to the subforms that show when I go to the form view. Ideas?
 
Back
Top