Re: Subform Wizard and number of primary key fields

Anthony England wrote:
"John" <jholmes429@xxxxxxxxx> wrote in message news:dt8rvn$84i0$1@xxxxxxxxxxxxxxxxxxxx
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!

.

Relevant Pages

  • Re: Connecting to the Same Table Twice
    ... RelationshipID (primary key) ... RelationshipName (parent, guardian, authorized pick-up, emergency contact, ... you might put spouse names into a separate table linked to this table). ... for each link between a child record and a caregiver record, ...
    (microsoft.public.access.gettingstarted)
  • Re: Connecting to the Same Table Twice
    ... > RelationshipID (primary key) ... > RelationshipName (parent, guardian, authorized pick-up, emergency contact, ... > you might put spouse names into a separate table linked to this table). ... > for each link between a child record and a caregiver record, ...
    (microsoft.public.access.gettingstarted)
  • Re: LinkMaster>LinkChild problem
    ... created in the child table, but its ID had not been inserted in the ... key value of the related parent record - but parent records do not ... In this case, the Parent/Child structure is reversed, because Subform 2 ... a foreign key field does NOT have to have the same name as its' ...
    (microsoft.public.access.forms)
  • Re: using combo boxes to filter records in a subform
    ... one parent record. ... the first subform is bound to the parent table. ... the second subform is bound to the child table. ... just put the controls on the main form. ...
    (microsoft.public.access.forms)
  • Re: LinkMaster>LinkChild problem
    ... created in the child table, but its ID had not been inserted in the ... key value of the related parent record - but parent records do not ... In this case, the Parent/Child structure is reversed, because Subform 2 ... a foreign key field does NOT have to have the same name as its' ...
    (microsoft.public.access.forms)