Hi Jason,
I looked at your db and here are some general comments and suggestions.
1) You need a single table to hold personnel data. Name, rank, DOB (not age), etc. In the related tables, you will store the person's PersonID. SSN is probably unique but should NEVER be used as a primary key. Use an autonumber.
2) I would add another table so that all charges could be defined in a single table. This table would have the following structure:
a) ActionID (autonumber, primary key)
b) PersonID (long integer, foreign key to personnel table)
c) ActionType (long integer, foreign key to action type lookup table)
3) Then the separate tables that you have created would use ActionID (as a long integer) as their primary key. They would be related 1-to-1 with the action table. This gives you a consolidated place to assign key values and count actions by type against individuals.
4) The combos need to be consistent. They should store the code value but show the descriptive text value. This has been covered to death on the forum. Bind the first column but hide it. You’ll also need to make the combos wider so that more of the text will show.
5) All forms/reports should be based on queries rather than on the tables themselves. In your case, you will be removing many fields (the duplicated fields relating to person data) so a query is how you will get those fields back. Join the new “action” table to the person table and to the specific action table that contains the data you need. You can then select data from all three tables. As long as you don’t change any field names, you will be able to simply drop the new query name into the recordsource in place of the table name and everything will work the way it did previously. The query will need something in the criteria field for ActionType to restrict its recordset to just the rows that relate to the action a particular form is based on. Lock the person fields on these forms because you don’t want them to be updated from the action forms.
6) Your column and table names should NOT contain embedded spaces or special characters such as the pound sign (#).
7) Add an up front form that lets the user choose the person whose records he wants to look at or otherwise work with.
8) Make the main form bound to the person table (or preferably a query based on that table). Then set the master/child links for each of the subforms on the various tab pages so that they are all linked on the PersonID. This will allow Access to synchronize the forms.
9) Make sure that everyone who is to use this app has monitors capable of 1024 x 768 resolution and that they are set to that. Mine was set to 800 x 600 and I couldn’t see your forms properly until I reset my screen resolution.
