Question-- Did you design this db from scratch or modify a MS Template?
I ask because unfortunately you've run into The Evils of Lookup Fields
and your tables need Normalization
These db design missteps are often the case with MS Templates.
It's my opinion that MS designs Templates in a non-normalized way for two reasons.
1. The average individual who downloads a template has no db design background, is at least familiar with a spreadsheet,
and is not interested in making changes to the design,
2.Others, like you, see the potential of using Access and want to take the design and revamp it to really make a working application.
Those people will take the opportunity to learn more about developing appilications in Access.
So before we try to tackle your original issue, let's work on getting your db normalized.
I have taken the liberty of adding some tables to split some of your existing tables and also to use as Lookup Tables.
Looking at your data, I'm pretty sure that you may need to add a few more tables as well.
In your existing tables --Design View--I have typed the fields that should be eventually deleted and where that data should be stored.
I can tell you've already put a lot of work into your queries & vba,
so I would suggest that BEFORE
you delete any existing fields, you should:
1. Append/Update the data to the appropriate tables using queries
2. Look at the design of EACH qry and add/edit the appropriate tables & fields
3. Do the same for Forms & Reports RecordSources & Controls
4. Remember 1:M
Here at UA, we are all about helping individuals to learn the Best Practices for db design.
We will be more than happy to help you with this process.
Once we/you get the db tables Normalized, I think we will have also solved your original issue.