Cant Load query as recordset

[Codebug]

Trying to load a query in Access 2003 as a recordset, returns error 3061, too few parameters, Expected 1.

Dim StrSQL As String
Dim rs As DAO.Recordset
Dim db As DAO.Database

StrSQL = "<some_SQL_Stat ement>"
Set db = CurrentDb()


Set rs = db.OpenRecordse t(StrSQL, dbOpenSnapshot)


also tried loading a query table as follows with the same results:-

Set rs = db.OpenRecordse t("My Query Table", dbOpenSnapshot)


I can load "My Query Table" using the same SQL statements via the standard Access environment OK.

[Stewart Ross]

StrSQL = "SELECT [Product Table].[Product/Service], [Product Table].[Cost Sheet Path], [Product Table].[Check Sheet Path]FROM [Product Table]WHERE [Product Table].[Product/Service] =Forms!NCR!Prod uctCombo;"
Set db = CurrentDb()

I suspect your difficulty is in referring to the combo within the SQL string, instead of the value of the combo. The Where clause will fail as it stands. Try:

StrSQL = "SELECT [Product Table].[Product/Service], [Product Table].[Cost Sheet Path], [Product Table].[Check Sheet Path]FROM [Product Table]WHERE [Product Table].[Product/Service] = " & Forms!NCR!ProductCombo & ";"

Stewart

[Codebug]

Thanks, works ok now

I suspect your difficulty is in referring to the combo within the SQL string, instead of the value of the combo. The Where clause will fail as it stands. Try:

StrSQL = "SELECT [Product Table].[Product/Service], [Product Table].[Cost Sheet Path], [Product Table].[Check Sheet Path]FROM [Product Table]WHERE [Product Table].[Product/Service] = " & Forms!NCR!ProductCombo & ";"

Stewart

[Stewart Ross]

Another thought: if you need to access the productcombo value when you run the query I find that Access is not consistent with references to form fields used as criteria. They can work OK as queries, yet fail when you try to open them using the OpenRecordSet method. Such references to form fields also fail if you convert a select query to a crosstab query.

I wrote a function to retrieve the field values which will work in SQL statements and in crosstab queries.

Public Function FormFieldValue(FormName As String, FieldName As String)
    FormFieldValue = Forms(FormName).Controls(FieldName)
End Function

Using it in the example you have given the SQL would be

StrSQL = "SELECT [Product Table].[Product/Service], [Product Table].[Cost Sheet Path], [Product Table].[Check Sheet Path]FROM [Product Table]WHERE [Product Table].[Product/Service] =FormFieldValue("NCR", "ProductCombo");"

It's another thing to try!

Stewart