ADO Problem

[Gary S]

I am having a problem with ADO. See the following simplified code:

Dim rsIndex1 As New ADODB.Recordset
rsIndex1.Open "[ReportingCreateIndexQry]", CurrentProject.Connection,
adOpenForwardOnly, adLockReadOnly
MsgBox "reportname"


This code works fine unless in the above query I am using selection criteria
that is referencing an open form. I get the following error message:
"Invalid SQL Statement; expected 'delete','insert',etc".

The selection criteria looks like "[Forms]![Reporting frm]![person combo box]"
FYI-the query runs fine if when I run it.

Can someone help me unserstand why this is not working? Thanks!
--------------------------------------------------------------------------------

 

[Ralph]

Just guessing but is your query and action query that makes a table or
updates a table rather than a select query.

 

[Gary S]

No, it is just a simple select query.

Just guessing but is your query and action query that makes a table or
updates a table rather than a select query.

I am having a problem with ADO. See the following simplified code:

Dim rsIndex1 As New ADODB.Recordset
rsIndex1.Open "[ReportingCreateIndexQry]", CurrentProject.Connection,
adOpenForwardOnly, adLockReadOnly
MsgBox "reportname"


This code works fine unless in the above query I am using selection criteria
that is referencing an open form. I get the following error message:
"Invalid SQL Statement; expected 'delete','insert',etc".

The selection criteria looks like "[Forms]![Reporting frm]![person combo box]"
FYI-the query runs fine if when I run it.

Can someone help me unserstand why this is not working? Thanks!

 

[Ralph]

I got the same error using the form as a parameter in the query. However if I
created a function for the form value, then used the function as the criteria
in the query it worked.

Something like
Public Function myParam() as string
myParam=[Forms]![Reporting frm]![person combo box]
end sub

No, it is just a simple select query.

Just guessing but is your query and action query that makes a table or
updates a table rather than a select query.

I am having a problem with ADO. See the following simplified code:

Dim rsIndex1 As New ADODB.Recordset
rsIndex1.Open "[ReportingCreateIndexQry]", CurrentProject.Connection,
adOpenForwardOnly, adLockReadOnly
MsgBox "reportname"


This code works fine unless in the above query I am using selection criteria
that is referencing an open form. I get the following error message:
"Invalid SQL Statement; expected 'delete','insert',etc".

The selection criteria looks like "[Forms]![Reporting frm]![person combo box]"
FYI-the query runs fine if when I run it.

Can someone help me unserstand why this is not working? Thanks!

 

[Gary S]

That sounds like a winner! Thanks for your help!

Any ideas why it didn't work the other way?

I got the same error using the form as a parameter in the query. However if I
created a function for the form value, then used the function as the criteria
in the query it worked.

Something like
Public Function myParam() as string
myParam=[Forms]![Reporting frm]![person combo box]
end sub

No, it is just a simple select query.

Just guessing but is your query and action query that makes a table or
updates a table rather than a select query.

:

I am having a problem with ADO. See the following simplified code:

Dim rsIndex1 As New ADODB.Recordset
rsIndex1.Open "[ReportingCreateIndexQry]", CurrentProject.Connection,
adOpenForwardOnly, adLockReadOnly
MsgBox "reportname"


This code works fine unless in the above query I am using selection criteria
that is referencing an open form. I get the following error message:
"Invalid SQL Statement; expected 'delete','insert',etc".

The selection criteria looks like "[Forms]![Reporting frm]![person combo box]"
FYI-the query runs fine if when I run it.

Can someone help me unserstand why this is not working? Thanks!