I am trying to create an Access form where the parent form will have 4
sub forms attached which shall contain the details of a different
person. Each sub form shall have a unique ID which will relate to that
person however if that person is used again on other parent forms sub
form then they will be recognised by their ID number.
The problem I am encountering is that I can get people created on a sub
form but when I change the parent form to add a new record all the sub
forms get carried over instead of having blank sub forms ready to enter
the details of new people.

Theories that have been given to me include

1. I need a new table for each sub form instead of one table for all
the sub forms.
(Problem I see with this is that when I search for a person I will have
to search over a number of tables and not just the one).

2. Use Excel to assign numbers to each person and link them back into
Access. Each person shall have a unique number in excel that can then
be exported back into access.
(Not sure how to do this)

I also want access to create an auto number for each form but this must
consist of details form the department field. For example if person one
works in department Corporate services I would like an auto number
generated as CP0001 (Corporate services). I have tried numerous ways to
get this to work but nothing seems to be working yet. People have
suggested to me to use an expression builder and do it that way which I
am looking at now but it does not seem to be working.


Any ideas would be a bonus!