Form Parameters and Pass Through Queries

[Paul H]

I've only just been introduced to Pass Through Queries. I understand that they use the native SQL of the ODBC database they query. I am editing existing queries to make them more flexible. This particular application was set up to have the user open up the query in a SQL window, edit dates and Vendor ID, then save, close and run the query which I thought rather odd. I want to drop some form parameters in there instead such as

Original: VendorID = '0007060391')



New: VendorID = Forms!frmNewMonth!cboVendor

but I keep getting ODBC call failed errors. My solution has been to created a formatted string in VBA and update the query def with it. This is way more complex, but seems like the only option.

Any suggestions?

[pbaldy]

You're doing it the way I handle it (use a QueryDef to change the SQL). The ODBC database has no knowledge of the Access form object, so you have to get the value from the form and put it in the SQL you pass to it.

[Paul H]

As I figured.

TYVM,

Paul

[pbaldy]

No problem. If you find a better way, let me know. I made a function I drop into databases that accepts the SQL and query name, which makes it easier. I just pass those to it and it changes the QueryDef.