Subform Wizard and number of primary key fields

[John]

I have two tables in a 1:M relationship- the parent has 5 fields in the
primary key and the child 6 (these are actually pretty far downstream in
a complicated ER model, but the problem is between these two). I set up
the relationship with no problem- referential integrity and left outer
join. I have created two forms- one for the parent and one for the
child table. I want to import the child table's form as a subform on
the parent table's form. Using the Subform Wizard, everything goes well
until it gets to the point where I need to specify the linking fields.
It shows a number of entries for these, all of which are single, key fields.

My problem is that Access allows only up to 3 fields for linking a
subform to a parent form, and that would force my having to enter
manually the other 2 fields in the primary key from the parent into the
child. Is there any way of getting around this? Thanks!

[Anthony England]

"John" <jh********@yahoo.com> wrote in message
news:dt***********@netnews.upenn.edu...

I have two tables in a 1:M relationship- the parent has 5 fields in the
primary key and the child 6 (these are actually pretty far downstream in a
complicated ER model, but the problem is between these two). I set up the
relationship with no problem- referential integrity and left outer join. I
have created two forms- one for the parent and one for the child table. I
want to import the child table's form as a subform on the parent table's
form. Using the Subform Wizard, everything goes well until it gets to the
point where I need to specify the linking fields. It shows a number of
entries for these, all of which are single, key fields.

My problem is that Access allows only up to 3 fields for linking a subform
to a parent form, and that would force my having to enter manually the
other 2 fields in the primary key from the parent into the child. Is
there any way of getting around this? Thanks!

When you say 'enter manually' you do realise that you could use vba code to
do this. In other words, you do not use the subform wizard so that the
child/master fields are not filled in at design time. You then write code
in the form's current event which tells the subform which records to
display. Code will need to be written for when you add a new record,
automatically inserting the required values.

Having said all this, I would change the table design to use single keys -
that is autonumber/long integer fields. You do realise that you can set up
a unique index over these 6 fields without it needing to be primary. I
understand there may be arguments in favour of your approach, but in my
experience the single-key approach is preferable.

[John]

Anthony England wrote:

"John" <jh********@yahoo.com> wrote in message
news:dt***********@netnews.upenn.edu...

I have two tables in a 1:M relationship- the parent has 5 fields in the
primary key and the child 6 (these are actually pretty far downstream in a
complicated ER model, but the problem is between these two). I set up the
relationship with no problem- referential integrity and left outer join. I
have created two forms- one for the parent and one for the child table. I
want to import the child table's form as a subform on the parent table's
form. Using the Subform Wizard, everything goes well until it gets to the
point where I need to specify the linking fields. It shows a number of
entries for these, all of which are single, key fields.

My problem is that Access allows only up to 3 fields for linking a subform
to a parent form, and that would force my having to enter manually the
other 2 fields in the primary key from the parent into the child. Is
there any way of getting around this? Thanks!

When you say 'enter manually' you do realise that you could use vba code to
do this. In other words, you do not use the subform wizard so that the
child/master fields are not filled in at design time. You then write code
in the form's current event which tells the subform which records to
display. Code will need to be written for when you add a new record,
automatically inserting the required values.

Having said all this, I would change the table design to use single keys -
that is autonumber/long integer fields. You do realise that you can set up
a unique index over these 6 fields without it needing to be primary. I
understand there may be arguments in favour of your approach, but in my
experience the single-key approach is preferable.

Thanks! I do know about the VBA thing, but this is for a group of
people I'm teaching who don't know VBA at all. As to the autonumber,
that won't work, I'm afraid- the applications these people will be
creating (for medical research) don't do well at all with the autonumber
approach. Thanks anyway!