Trying to change this excel code into access. I think I need 3 tables. Header, Tie-in, and Survey then have a relationship for them. But not sure on translating the the code. The stuff like application worksheet, and dialog sheet.

First you need to adjust you thinking somewhat. Access tables only conain data.

Your VBA code is too long for me to go through to try to understand what you want to do. Generally, Excel VBA code does not translate directly into Access VBA code primarily because Excel involves sheets and cells, Access involves tables. although somewhat similar, they are eentirely different concepts.

I have converted many Excel & 1-2-3 programs into Access programs. Generally, build your tables first and their relationships, if any to one another. Then build your forms and reports.