MS Access 2002 problem: linked form fields in ODBC queries

[monnomiznogoud]

Ok, my problem is the following:

I have very complicated Access 97 databases that link through ODBC to
Sybase databases.

Now in some of the forms controls I had queries that used as "where
clause" parameters form field values; For example:

select foo from bar where a_colmun = [forms]![MyForm]![MyField]

This worked in Access 97 but fails in Access 2002 (ODBC failure) when
the table *bar* is an ODBC Sybase (in my case) linked table.

If *bar* were a local Access table, the query would do fine.

if i did

select foo from bar where a_colmun = (select
[forms]![MyForm]![MyField] from dual)

where *dual* is a table with only one line of data. That works, but it
slows my queries.

Someone have a solution ? (i cannot upgrade to newer versions of
Access)

Thanx in advance

[(PeteCresswell)]

Per mo************@yahoo.com.ar:

Now in some of the forms controls I had queries that used as "where
clause" parameters form field values; For example:

select foo from bar where a_colmun = [forms]![MyForm]![MyField]

This worked in Access 97 but fails in Access 2002 (ODBC failure) when
the table *bar* is an ODBC Sybase (in my case) linked table.

If *bar* were a local Access table, the query would do fine.

Is the SQL in the form's .RecordSource or are you applying it from VBA code?

If it's in .RecordSource, you can probably stop here and move on the next
post...

I've had slightly similar problems when VBS uses a query that refers to a form's
control value. Query works find when invokes by double clicking the query
object, but can't find the referenced control when invoked from VBA.

Workaround for me was to define a parameter in the query and explicitly load it
in VBA before opening an RS.
--
PeteCresswell

[monnomiznogoud]

PeteCresswell

Is the SQL in the form's .RecordSource or are you applying it from VBA code?

It is in the a list's record source in fact.

The IT services suggested to put back the Access 97 Sybase's patch :)

The only solution i found is the one i posted above, an innner select
using the form's field value with a local table (dual, a one-line
oracle-like table i defined)