OpenRecordset error Invalid Argument 3001

[tsm1993]

When I run the following code I get an error 3001: Invalid Argument

Here is my code:

Dim rsinstr As DAO.Recordset
Dim sql As String

sql = "SELECT qryEMS_TestResults.* FROM qryEMS_TestResults WHERE (((qryEMS_TestResults.CourseID)=[Forms]![frmEntry-Field]![cboLocateRoster]));"

Set rsinstr = DBEngine(0)(0).OpenRecordset(sql, dbSeeChanges)

I run something very similar without any problems. I do reference Microsoft DAO 3.6 Object library. If I take the sql code and run it as a query, I do get records. Any help would be greatly appreciated.

 

[vbajock]

Try

sql = "SELECT qryEMS_TestResults.* FROM qryEMS_TestResults WHERE (((qryEMS_TestResults.CourseID)=' " & [Forms]![frmEntry-Field]![cboLocateRoster] & " ' ));"

 

[vbajock]

Also, if CourseID is a numeric field, leave out the single quote marks, if it is text, leave them in. You also might have to enclose [Forms]![frmEntry-Field]![cboLocateRoster] with Str$ [Forms]![frmEntry-Field]![cboLocateRoster]) if it throws a type mismatch error.

 

[tsm1993]

Thank you for the suggestion. I tried both and I even tried just throwing in a value and finally added the condition to the query. I get the error: Run-Time Error 3001: Invalid Argument.

The error comes on the line: Set rsinstr = DBEngine(0)(0).OpenRecordset(sql, dbSeeChanges)

Any other ideas?

 

[Andrzejek]

Did you try:

sql = "SELECT qryEMS_TestResults.* FROM ... "
[red]    
Debug.Print sql[/red]

Set rsinstr = DBEngine(0)(0).OpenRecordset(sql, dbSeeChanges)

And look at the Immediate Window what you get as the value in your sql variable? And if your sql makes any sense, ie. work in Access?

Have fun.

---- Andy

 

[tsm1993]

If I copy what is in the Immediate Window in to a query, I get data. I switched to:

Dim rs As New ADODB.Recordset

sql = "SELECT qryEMS_TestResults.* FROM qryEMS_TestResults WHERE (((qryEMS_TestResults.CourseID)= " & [Forms]![frmEntry-Field]![cboLocateRoster] & "));"


rs.Open sql, CurrentProject.Connection

It runs fine with that. I had stitched from this in another part of the DB becuase I was getting and error on users machines that are on Access 2010.

 

[Andrzejek]

If it still does not work - could you share with use "what is in the Immediate Window"???

Have fun.

---- Andy

 

[tsm1993]

The following comes up in the immediate window:

SELECT qryEMS_TestResults.* FROM qryEMS_TestResults WHERE (((qryEMS_TestResults.CourseID)= 1567));

This is what it needs to be. When I pulled the field name out of quotes, as suggested, it started working if I switched to ADO instead of DAO. DAO still gets the same error.

 

[Andrzejek]

First, I would loose all the [tt]((()))[/tt] and play only with:

SELECT qryEMS_TestResults.* 
FROM   qryEMS_TestResults 
WHERE  qryEMS_TestResults.CourseID = 1567;

not that it will fix your problem, but you don't need them

Have fun.

---- Andy

 

[PHV]

If you want to use DAO, I'd try something like this:
Set rsinstr = DBEngine(0)(0).OpenRecordset(sql[!], dbOpenDynaset[/!], dbSeeChanges)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886