I want to create two linked subforms on a form. For this I need to set query parameter into the record source of the second subform like this:
select col1, col2
from my_table
where col3=[Forms]![MyForm]![subform1]![MyField]
This works with .accdb file, but not with Access Project (.adp file), connected to SQL Server. There I get "Invalid syntax near '!'". Tried many other ways, but could not make it to work.
In the ADP version, your query is run by SQL Server's db engine, which knows nothing about [Forms]![MyForm]![subform1]![MyField]. In the ACCDB version, the query is run by Access' db engine, which does know about [Forms]![MyForm]![subform1]![MyField]
See whether you can put the second subform into a subform control on subform1. If that arrangement can work for you, use subform1's [MyField] as the link master and col3 as the link child. (Those are set with the link master/child property of the subform control.) The effect will be to filter the second subform's records --- display only those records where col3 matches the MyField value of the current record in subform1.