Displaying and modifying 1:N data in forms

[stephan.vanbeerschoten]

Hi All,

I am relatively new to MS Access but am wel suited in general
relational databases. I seem to be unable to accomplish the displaying
of query results that return more then one line item.
I want to create a person record that can have multiple addresses, and
I want all these addresses displayed on a form for manipulation, but I
want to prevent saving the fields to the database as street1, street2,
zipcode1, zipcode2, state1, state2 etc etc for any numbers of addresses
(between 0 and 6 or so) that can be added to this person record.

I have the database tables setup with all the neccesary relations to
the other tables, that was the easy part for me. I have also already
come up with the neccesary queries to pull up the data. Now all I need
is to know how I can do this in the form world of ms access.

Help would be appreciated,
Stephan

 

[Dirk Goldgar]

Hi All,

I am relatively new to MS Access but am wel suited in general
relational databases. I seem to be unable to accomplish the displaying
of query results that return more then one line item.
I want to create a person record that can have multiple addresses, and
I want all these addresses displayed on a form for manipulation, but I
want to prevent saving the fields to the database as street1, street2,
zipcode1, zipcode2, state1, state2 etc etc for any numbers of
addresses (between 0 and 6 or so) that can be added to this person
record.

I have the database tables setup with all the neccesary relations to
the other tables, that was the easy part for me. I have also already
come up with the neccesary queries to pull up the data. Now all I need
is to know how I can do this in the form world of ms access.

Help would be appreciated,
Stephan

You may not be aware of the subform feature of Access. Normally, you'd
have a main form based on the "one-side" table, and a subform on that
form based on the "many-side" table. The Link Master Fields and Link
Child Fields properties of the subform control -- the "window" control
on the main form that displays the related form as a subform --
automatically take care of the relational linkage between the main
form's record and the subform records.