Open Object Error

[DEI]

I am trying to open an ADODB recordset with the following code

Dim SQL As String

Dim Conn As ADODB.Connection
Dim rss As New ADODB.Recordset

Set Conn = CurrentProject.Connection

'Source table (Query1)

Select Case STAFF

Case "ALL"

SQL = "SELECT AUDIT.ADATE, AUDIT.STAFFID, AUDIT.AUDITID, AUDIT.ACTID,
AUDIT.TIME " & _
"FROM AUDIT " & _
"WHERE (((AUDIT.LOCID) = " & ASC & ")) " & _
"ORDER BY AUDIT.ADATE, AUDIT.STAFFID, AUDIT.TIME;"

Case "RN"

SQL = "SELECT AUDIT.ADATE, AUDIT.STAFFID, AUDIT.AUDITID, AUDIT.ACTID,
AUDIT.TIME " & _
"FROM STAFF INNER JOIN AUDIT ON STAFF.STAFFID = AUDIT.STAFFID "
& _
"WHERE (((AUDIT.LOCID) = " & ASC & ") And ((STAFF.POSITION) = '"
& STAFF & "')) " & _
"ORDER BY AUDIT.ADATE, AUDIT.STAFFID, AUDIT.TIME;"

End Select

'Open source table

rss.Open SQL, Conn, adOpenKeyset, adLockOptimistic

When I use the first SQL statement, the recordset opens fine. When I use
the second (which is very similar, but has a join) I get the following
run-time error: Method 'Open' of object "-Recordset' failed. I can not
figure out what the problem is. Do I need to use different arguments for the
Open method?

Thanks in advance.

DEI

 

[DEI]

Jim,

The STAFFID fileds in both tables have the same datatype; a one-to many
relationship has been defined in the database between the 2 tables on this
field.

ASC is a long variable. I actually pereviously used a number in SQL string
instead of the reference to the variable and had the same result.

When I copy and paste the query statement into the query builder it works
fine.

Thanks for your assistance.

DEI

 

[DEI]

Jim,

The STAFFID fileds in both tables have the same datatype; a one-to many
relationship has been defined in the database between the 2 tables on this
field.

ASC is a long variable. I actually pereviously used a number in SQL string
instead of the reference to the variable and had the same result.

When I copy and paste the query statement into the query builder it works
fine.

Thanks for your assistance.

DEI

 

[DEI]

Thanks for the tip re: the ASC function. I fised that.

The SQL statement still does not work, however, even when I eliminate all
string variables.

I eventually defined a query in the database and created an SQL statement to
access that query (using SELECT *, etc.). It works fine. I still can not
determine why the original statement generates the error.

Thanks,

DEI

 

[DEI]

Thanks for the tip re: the ASC function. I fised that.

The SQL statement still does not work, however, even when I eliminate all
string variables.

I eventually defined a query in the database and created an SQL statement to
access that query (using SELECT *, etc.). It works fine. I still can not
determine why the original statement generates the error.

Thanks,

DEI