none
Reuse subform

    Question

  • Hello, I am trying to reuse a subform on several forms. The outter forms are like baseball, basketball , tennis.  The problem is I only have one table for the subform and an autoid, I added an ie (for the outter form baseball) in the sub table called baseballid however because I already have an autoid I can not make baseballid auto number, nor can I add basketballid or tennisid. I can add them but only as numbers. 

    Futher when I go to the relationship table there are all kinds of relationships and I just want to see the baseball/subform relationship, there's so many relationships I can not find what I'm looking for.

    Overall I would like the baseballid number to match the outter baseball id. When I enter a number it seems to work but I need the number to be automatic so when the user adds a new subform it's related to the outter.

    Very confused need some guidance please, Emily

    Thursday, July 18, 2013 5:52 PM

Answers

  • The best use of a subform is in datasheet view.  Of course, the subform can be used as a regular form within a form to separate controls, but that is not very efficient usage.  Ideally, it's not the best practice to use the same subform with a bunch of different parent forms.  Each subform should belong to one parent form.  If you start coding in the subform this will prevent a lot of unforeseen error messages from code overlap.


    Rich P

    Thursday, July 18, 2013 8:14 PM
  • I think that's the wrong way to deal with your problem.  You never did explain what the two tables are modeling, so I can't be sure what you are trying to accomplish.

    Let's try this as an example that might or might not be close.  If you have a table of participants called Players and a table of activities called Sports, then, because a player can participate in several sports and each sport has many players, you have a many to many relationship.  This kind of relationship is modeled by using a third table often called a junction table that contains at least two fields, one is a foreign key to the players table and the other is a foreign key to the sports table.  The two foreign keys are used as the table (compound) primary key index. 

    This way you have two one to many relationships, one from sports to the junction table and the other is a one to many relationship from the players table to the junction table, all neat and clean.

    Thursday, July 18, 2013 10:45 PM

All replies

  • I discovered it's easier to just copy the fields from the subtotal and paste them in my form. Then to add a query where the Baseballid in the subtable equals the id in the outter form. So in the sub table I added columns for Baseballid, BasketBallID and TennisID. This is working perfectly great! Much easier then playing with links and children
    Thursday, July 18, 2013 7:12 PM
  • The best use of a subform is in datasheet view.  Of course, the subform can be used as a regular form within a form to separate controls, but that is not very efficient usage.  Ideally, it's not the best practice to use the same subform with a bunch of different parent forms.  Each subform should belong to one parent form.  If you start coding in the subform this will prevent a lot of unforeseen error messages from code overlap.


    Rich P

    Thursday, July 18, 2013 8:14 PM
  • I think that's the wrong way to deal with your problem.  You never did explain what the two tables are modeling, so I can't be sure what you are trying to accomplish.

    Let's try this as an example that might or might not be close.  If you have a table of participants called Players and a table of activities called Sports, then, because a player can participate in several sports and each sport has many players, you have a many to many relationship.  This kind of relationship is modeled by using a third table often called a junction table that contains at least two fields, one is a foreign key to the players table and the other is a foreign key to the sports table.  The two foreign keys are used as the table (compound) primary key index. 

    This way you have two one to many relationships, one from sports to the junction table and the other is a one to many relationship from the players table to the junction table, all neat and clean.

    Thursday, July 18, 2013 10:45 PM