Join is TableA(TableA_ID) - TableB(TableA_ID). Type 2 with "Enforce Referential Integrity".
I want TableA field "OnSite(Yes/No)" to update to "yes/checked" if a row exists in TableB with the TableA_ID present and the "To(Date/Time)" field is not in the past.
I have no idea where to start. I don't want to use VBA code as the database will be upscaled to SQL Server when completed and accessed through a web front end, although I don't know if this will matter. Any advice or guidance would be really appreciated.
Use a simple Update Query. It will transfer nicely into SQL Server.
In the query designer, bring up tblA first, then tblB. Make sure you have the "Update Query" selected. The two tables will join automatically since you have them related. Double click on tblA's OnSite(Yes/No) field. In the "To" line, enter True. Then double-click on tblB's To(Date/Time) field. In the "Criteria" line, enter ">= Date()" (without the quotes). From the way you presented the question, it should be just what the doctor ordered.
Personally I would not have that field. It's basically a calculated field (one whose value depends on another). Whatever process you would come up with would have to be run all the time to keep it updated (once a day if the field only stores date, repeatedly throughout the day if it includes time). This is the type of thing you should just calculate in a query or on a form/report.