Junction table problem

J

John Whyte

I have just created a new database in Access2002 with no
data on it. It has
4 Tables. These are shown with there use in forms in
parenthesis:-

1. tblPages, "Parent"
2. tblLayoutTasks, "Child"
3. tblResources, "Grandchild"
4. tblLayoutResources is junction between 2. and 3. and
has its primary key as combined primary key of 2 and 3.

There is a problem when using the form based on 1 (parent)
with 2 as subform (child) and 3 as a subform of 2
(grandchild).

When the first data was entered, 1 and 2 accepted entries
but the entry to 3 gave the error "field cannot be
updated". [The help system says the record is locked by
another user (there is no other user) or the locked
property of the control is set to Yes(it's set to 'No')].

On checking the junction table, 4, I find that the
primary key combined values (data type = number) are
still zero. If I change these to equal the newly created
ID's in tables 2 and 3, the form displays the correct
information.

How do I get the Junction table to update automatically
with the same values of ID's from 2 and 3?

John
 
J

John Whyte

Thanks
-----Original Message-----


Okay: so each LayoutTask has many LayoutResources, and each Resource has
many LayoutResources too. I am not sure that this parent/ child stuff is
helping very much


The normal way to do this is to base the form on a query based on
LayoutTasks, and one subform based on a query that right- joins
LayoutResources and Resources: in that way you get a list of resources with
the appropriate LayoutTaskNumber.

You'll have to be careful about which copy of the ResourceIDNumber is
visible in the query, in order for it to be updateable -- I can never
remember which is which so I have to do it by trial and error!

Hope that helps


Tim F




.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top