By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,576 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

Subforms with queries and relationships

P: n/a
Hi.
First of all: I started working with databases two weeks ago, so some
questiones may seem senseless...

I want to create a form for the data collection of different studies.
Each study has an ID which is the primary key and some additional
information like date of publication which are included in table 1.

Additional other tables are the outcome tables that have the study ID
as a foreign key.

I created a form which is based on the query that included table 1 and
some of the additional other tables.
My problem is that I could not include all the fields in one query
because I have to use more than 70 fields in total. I tried to resolve
this in including subforms to my form that are based
on another query which relates table 1 with the primary key to the rest
of the additional tables which use the study ID as a foreign key.
If I enter data to the main form it works but if I try to enter data
into the subform I get the error message "the changes you requested to
the table were not successfull because they would create duplicate
values in the index, primary key or relationship".

I am not sure if I explained clear enough what the database looks like
but if someone has any idea, I would be really glad!!
Thanks,
Tina

Apr 26 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Tina:

It sounds like you are trying to add a record to table 1 that has the
same primary key as an existing record. For subforms, do NOT include
the primary key's table in the underlying query. Instead, just have a
query that returns all of the related records (e.g. only include table
2). Then, in your subform object's properties, you're going to want to
make sure the 'Link Child Fields' (your foreign key from table 2) and
the 'Link Master Fields' (your primary key from table 1) both reflect
the linking field names. This creates the relationship between the
data on your subform and the main form, and new records added to the
related table will automatically have the primary key from your table 1
inserted in them, without trying to add a new record to table 1.

Note that you will need to have the primary key field on your main form
and the foreign key field on your subform, though they need not be
visible.

HTH,
Jana

Apr 26 '06 #2

P: n/a
Jana:

Thanks a lot!
It seems to work now. It took me some time to fix it because you cannot
ad a lot of tables to the "Link Child field" and I had to summarize all
my tables to some few but I think this is better anyway.
Have a good one!
Tina

Apr 26 '06 #3

P: n/a
Tina:
You're quite welcome!

Jana

Apr 27 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.