Parent with many similar children

[Allison]

Access 2003 SP3
Win XP SP2

I need help with the best way to associate lots of similar children to one
parent.

Current set up is as follows:

Parent is "Contact" - associated to table of same name
Children are all "Address" - associated to table of same name, with
ContactID a foreign key to the table

One Contact can have several Addresses

Right now I have "Contact" as a main form and "Address" as a subform. Works
great with only one address, but I don't know how to add a second, third,
etc. address for the same contact.

Ideally, I'd like to display each separate address on its own tab in the form.

______
1. Is this the most-efficient way to accomplish this?
2. If not, how should I restructure the tables/forms?
3. If yes, how do I get multiple addresses within the main form?

Thanks for your help.

 

[Arvin Meyer [MVP]]

Yes, it is the best way to accomplish this. Create a subform, which is a
form displayed either as a continuous form or a datasheet for the address
table, Then in Design View with the Wizard turned on (the magic wand) put a
subform control on the main form and follow directions to connect your
subform.

 

[John W. Vinson]

Right now I have "Contact" as a main form and "Address" as a subform. Works
great with only one address, but I don't know how to add a second, third,
etc. address for the same contact.

What's causing the problem? Is this really a Subform (rather than a separate,
popup form)? If so what are its Master and Child Link Field properties?

Note that the subform can be changed from Single Form to Continuous Form to
Datasheet using the "Default View" property. You may prefer continous, in
order to see multiple records onscreen concurrently yet still have full
formatting and appearance control.

 

[Allison]

Already have it in a subform, thanks.

Yes, it is the best way to accomplish this. Create a subform, which is a
form displayed either as a continuous form or a datasheet for the address
table, Then in Design View with the Wizard turned on (the magic wand) put a
subform control on the main form and follow directions to connect your
subform.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Allison]

John,

Yes it's in a subform. But maybe I need to understand better how that works.

Right now if someone types in an address, that's the only one that displays.
How do they get to a blank form in order to enter another address? Would
they use the navigation buttons for that to get to a new record?

I'd rather have each separate address on a separate tab. Is there a way to
do that?

 

[John W. Vinson]

John,

Yes it's in a subform. But maybe I need to understand better how that works.

Right now if someone types in an address, that's the only one that displays.
How do they get to a blank form in order to enter another address? Would
they use the navigation buttons for that to get to a new record?

It sounds like (and I cannot see your screen from here, so I'm not sure) that
you have the Subform's Default View property set to "Single Form". Try setting
it to "Continuous Forms", and arrange the controls at the top of the window;
snug the bottom of the window up to the bottom of the controls. This should
let you see each address on a separate line (or two or three lines if that's
how you lay out the controls).

I'd rather have each separate address on a separate tab. Is there a way to
do that?

Not at all easily. You would need a Tab Control with a subform on each tab
page, each subform based on a different query selecting the first address,
second address, etc. by some criterion.

 

[Allison]

I cannot do continuous view because I have a sub-sub form that associates
data to the address and populates another table. (Or maybe I can do
continuous and still bind each sub-sub to only one sub? I don't know how to
do that.)

Can you give me some idea of how the query option would work?

 

[John W. Vinson]

I cannot do continuous view because I have a sub-sub form that associates
data to the address and populates another table.

Thank you. You did not indicate that this was part of the problem before.

(Or maybe I can do
continuous and still bind each sub-sub to only one sub? I don't know how to
do that.)

You can have two correlated subforms on the mainform. This would let the
second subform's content reflect the currently selected record on the first
subform. Would that be satisfactory?

Can you give me some idea of how the query option would work?

Not without knowing a lot more about your table structure. That's probably THE
MOST DIFFICULT of the options you have, both for you and the user.

 

[Allison]

Thank you for your help.

Thank you. You did not indicate that this was part of the problem before.


You can have two correlated subforms on the mainform. This would let the
second subform's content reflect the currently selected record on the first
subform. Would that be satisfactory?



Not without knowing a lot more about your table structure. That's probably THE
MOST DIFFICULT of the options you have, both for you and the user.