If I'm understanding correctly, the issue is that the controls disappear when no records are returned and the source table (tblAccomplishment) to the query in post #19 is a linked sql server table rather than a linked access table.
not sure if it is relevant and just a theory but your query contains a number of vba functions that will not be recognised in sql server - so what happens is the data is returned to the access environment for those functions to be implemented. If there are no records to be returned, perhaps the state of the recordset is different to the state where you are using an access linked table.
It's just a theory - to test, remove the calculated fields then select a date range where no records are returned. Do you then see the controls?
Another possible issue is the field names have spaces and non alpha numeric characters - ([Narrative (Text)],[Date (Numeric)],[SF/MC],etc) and a reserved word (Date). The latter two in particular can cause unexplainable errors or failures. At the very least put Date in square brackets and again as a test, exclude the fields with non alphanumeric characters. It is also worth noting that Text and Numeric are also reserved words and I do recall a situation a few years ago where a reserved word surrounded by brackets caused issues. It's on this forum somewhere.
Might also be worthwhile removing the filter and order by properties of the form - the recordsource is already sorted by date anyway, just include the rest in your query criteria if appropriate
And double check your other form properties - setting Data Entry to yes means it will not show current records, it only allows for new records to be entered.