Okay, this makes my head hurt, so bear with me that I can explain it well enough that maybe someone can help me.
I have a huge database that is used to track Organizations and Individuals that are affliated with the org. The main inteface is a tabed form that is used to view/enter data.
Tab 1 shows Org/Ind information. This is pulled from a Names table that contains a record for each Org and each individual.
Tab 2 show the relationship between each Org and Ind, this pulls from a Table called Relationships where where are multiple records. One individual can have relationships with multiple orgs.
During the course of an Organization it can change relationships (exp...new ownership) When this happens the process to makethe changes is extensive and takes over 40 minutes to do it. They are looking for a better way to do this. here is the current process they follow:
Go to the Org Record - Change Relationship of the indv (current owner to former owner) in subform
Go to Indv Record for each person (could be multiple) and change to inactive status and Former Owner - this needs to be done on both Tab 1 & Tab 2
Create a New memberID for the new owner if not already a member - this is manual and done outside of database.
Go directly into Names table and add record for new member, this generates a new Access ID
Go back to Org Record, add the new person to Tab 2
Go to the Indv Record, add the new person to Tab 2
So as you can see they are duplicating data in multiple places and when change or update data it doesn't do so in other tables.
I know this is probably not enough info to help, but ask questions and I'll answer to my best ability, my goal is to find a better way to add/change/update data and have it duplicat entry eliminated.
You don't mention the structure of your tables, but from your presentation of the problem, I understand two things: (1) - you're following a physical process instead of trying to do it with VBA. This makes me think that (2) - your data is not normalized.
There's no reason on earth that, once it's programmed correctly, you can't do the whole operation in VBA in about 3 seconds.
I will admit that I am not an expert here and that from what I have read on Data Normalization, it seems that this DB is, however I can't say for sure, as you say it doesn't seem like it is. As for the structure of the tables, they are all linked by an Access ID field, each table has this field. I have three days left after today to finish up this project, if this linking can't be done they will accpet that, but I want to do what I can to make it easier for them on a daily basis.