Acess Forms

[scott.denholm]

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!

 

[Arvin Meyer [MVP]]

You can get some code which is easily modified for your alphanumeric ID
here:

http://www.datastrat.com/Download/AlphaNumeric2K.zip

You are correct that all the people need to be in 1 table, and I am unsure
why you would think that you need to display them on 4 separate subforms.
You can use a mainform and display all the people on 1 subform. As you
scroll through those records, the details can be displayed individually on a
sub-subform.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Bush]

Thanks for the info, I shall give it a try.

Answer to the 4 sub form query is the database will be monitoring
people attending meetings (such as a disaplinary) and each person who
attends will have their own section (sub form) which shall contain data
that they have said in that particular meeting. However the same people
may attend a different meeting (disaplinary) and have differet
notes/views so these will have to be recorded on a seperate subform
which shall be attached to differetn form recording a different
meetings outsomes.

I know this sounds strange but its what I need.