I didnt understand it at first, but I just cant thank you all enough for enlightening me to the point that I can now begin to appreciate the delema I have inherited. Its a real beauty:
Ive got two same name fields belonging to unrelated tables. One of the same name fields has older data that appears on my details layout when older records are selected. The thing is... I need newer data to fill the second same name field when a newer record is selected in the layout. Users need to be able to look up any record, old or new, and view (only view, no interaction required) the contents of the same name field that relates to the current record. Right now, only the old records fill the field and new records appear to leave it blank, and thats because the second same name field is not really even on the layout ( took me a while to figure this out). I was thinking about laying one field on top of the other... 2 different fields with the same name, each from a different unrelated table, one laying on top of the other, then I would play some kind of a trick to hide one everytime the other needs to be seen... But even if I get this to work, the dirty data is still comming after me. Here is where you get the whole story:
I have another layout with a date field that cant be populated because the lookup points to a date field in the old table, and this layout is only for populating and printing a FORM that requires data mostly from the old table... Now here is the kicker: looking at my graph, the FORM has its own separate group with a few boxes related to it, and of course, one of them represents the old info table... so I cant just relate my new info box to it, because Im assuming that the only reason my database still works is because the same name fields belong to unrelated tables. There are over 1700 fields in the old box. I have no idea what most of those fields are for, but some of them are needed on the FORM... so I cant just disconnect that box from the group. Any ideas?