0

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.

HansUp
  • 95,961
  • 11
  • 77
  • 135
Oliv
  • 10,221
  • 3
  • 55
  • 76
  • 1
    Oliv, it is nice to acknowledge responses to your questions, if the answer is not suitable a comment will do. You might like to read http://stackoverflow.com/faq#etiquette – Fionnuala Feb 10 '12 at 15:34
  • We switched back to ACCDB because of this, so no problem anymore :) – Oliv Feb 13 '12 at 10:52

1 Answers1

0

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.

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • That would work, but the subform1 is in Datasheet view, so it does not display subforms. – Oliv Feb 11 '12 at 07:08