forms, foreign keys and relational model

[Adrien G]

I am moving this from another forum since it seems more people are looking here

Hi all
I am not new in database design, but I am totally new in using access.
I've been struggling now for 2 days, and i resigned in asking for help.

I have multiple problems, but i think if i solve this one, it will solve 90%
of my problems.

It is a common problem and it is related to adding staff for a store.
On all the example i've seen, the employee, staff, customer information is
grouped into one table. But i've relationalized the model to extract it into
more table.
This means that when i had an employee, i would not update one table but
multiple, and this is where my problem reside.

this is a short view of my model (i had a picture, but i cannot add
attachements)
Table Entity_types (domain table with static data)
entity_type_id (PK)
entity_type (string)

Table Entities
entity_id (PK)
entity_type_id (FK) (many to one with Entity_types)

Table Persons
person_id (PK)
first_name
last_name
dob
other_detail

Table Contacts
contact_id (PK)
phone
email
web
(... Other stuff)

Addresses
address_id (PK)
line_1
line2
city
(....)

Table Staff
staff_id (PK)
entity_id (FK one to one with Entities)
address_id (FK one to one with Addresses)
contract_id (FK with contacts)
person_id (FK with person)
date_joined
date left

So the model is pretty straigh forward.
I could group Address and Contacts but here is not the problem

When i create my form, i import fields from addresses, contacts and persons.
I haven't firgured out a way to update the FKs in staff and entities.
I know i am doing something wrong but i do not know why.

Now the other weird thing, is that when i create the form and try to display
it in view mode, the form shows up as empty. I do not see the labels and
other stuff.
After a lot of time investigating, i have realized that it has something to
do with
the property RecordSet Type for the form properties.
If it is set to Dynaset only, then it does not display,
If it is set to Dynaset (inconsistent....) then it shows up.
Any ideas why?

I think it could be related to my relationships but not sure.

Thanks for your help.
thanks a lot

Adrien



Couple notes:
I am new at access but not database
All my tables are empty.

I tried the subforms ideas but it does not work and i would rather have
everything into one form.

thanks again

adrien

 

[Adrien G]

I forgot to mention that all the relationship links are at least cascade
update and some of them are delete as well

thanks

Adrien