One to Zero or More and One to One or More in One to many relationship

[topazny]

Hello does anyone know if Access supports the following types of One to Many relationships?

- One to zero or more (I.e. Optional one to many)
and
- one to one or more (i.e. required one to many)

In short I am trying to create a one to 'one or more' relationship but have not been able to. I have the following tables:

Person : Pid (PK), Name

Address: Pid (FK to Person), Aid, Address (PK here is Pid and Aid)

I have tried to create a form that allows a user to populate the 'person' table and in doing so 'must' pupulate the address table as well. I was under the impression I could leave this to RI with a One to One or More relationship. (I.e. One person can have one or more addresses)

(Please note the above is an example only)

Cheers,
Topaz

 

[JerryDennison]

There is no settings you can use in the Relationship view or within Access itself to "force" an entry in one table when a record is created in another table. You can ensure that at least one record is entered in a subform(the many side of your 1-many relationship) by adding a little snippet of code to the main form to check the subform recordset for at least one record based on the mainform pk before moving to another mainform record. "Advice is a dangerous gift, even from the wise to the wise, for all course may run ill." J.R.R. Tolkien

 

[GoDawgs]

If you create a relationship (in the relationship window I mean) between two tables that have a One:Many relationship and click "Enforce Referential Integrity" then Access won't let you create a record in the "many" side if the foreign key (the primary key from the "one" side) does not correspond to a record in the "one" side...if that makes any sense. This won't actually force the user to enter a record in the "one" side, but it will make sure that the user doesn't enter the record on the "many" side. Hope this helps somewhat.

Kevin

 

[LittleSmudge]

If you arrange the Many side table to appear in a subform on the main form, with the One side table on the main form.

Then, when you created a new record have some code that moves the focus to the subform and causes some changes in the sub form's new record. The user will then HAVE to complete whatever fields have been defined as "required" in the underlying Many side table.

The user can then create more subform records etc if needed.


'ope-that-'elps

G LS