OpenRecordset help - Run time error 3061 (Too few parameters. Expected 1)

[Jonathan LaRosa]

I am trying to open a recordset and I am getting an error and I can't
figure out why.

See code below. sqlString2 does not work. sqlString does. Clearly
the problem is with the nested SELECT statement. But when I copy
sqlString2 into a new query it runs just fine. Futhermore, I have
used a nested SELECT statement with the OpenRecordset() method before
and it works just fine. Any ideas?

thanks much,
jon
Here's my code:

sqlString = "SELECT [Confirmation Sent?], [Date Confirmation] " & _
"FROM [Transaction / Billing Information Info];"

'sqlString2 = "SELECT [Confirmation Sent?], [Date Confirmation] " & _
' "FROM [Transaction / Billing Information Info] " & _
' "WHERE (([Transaction ID]) In " & _
' "(SELECT [Transaction ID] FROM [qry confirmation letter] " & _
' "WHERE (([Person ID] = 26000052))));"

Debug.Print (sqlString)

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset(sqlString, dbOpenDynaset)

[Salad]

Jonathan LaRosa wrote:

I am trying to open a recordset and I am getting an error and I can't
figure out why.

See code below. sqlString2 does not work. sqlString does. Clearly
the problem is with the nested SELECT statement. But when I copy
sqlString2 into a new query it runs just fine. Futhermore, I have
used a nested SELECT statement with the OpenRecordset() method before
and it works just fine. Any ideas?

thanks much,
jon
Here's my code:

sqlString = "SELECT [Confirmation Sent?], [Date Confirmation] " & _
"FROM [Transaction / Billing Information Info];"

'sqlString2 = "SELECT [Confirmation Sent?], [Date Confirmation] " & _
' "FROM [Transaction / Billing Information Info] " & _
' "WHERE (([Transaction ID]) In " & _
' "(SELECT [Transaction ID] FROM [qry confirmation letter] " & _
' "WHERE (([Person ID] = 26000052))));"

Debug.Print (sqlString)

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset(sqlString, dbOpenDynaset)

Why not create a new temp query, add the table [Transaction / Billing
Information Info] and the query [qry confirmation letter], create a join
line between TXID, and a whereclause for the PersonID. Run it to
verify. Then do a View/SQL, copy to clipboard, and paste the SQL string
in the code, and separate with quotes as above. It will also be faster
than doing a Select ID from ... where .... in ...

[Jon LaRosa]

The reason I can't take your suggestion and create a new query rather
than use the nested SELECT statement is because that nested SELECT
statment is dynamically generated at runtime. I have removed the
references to the variable that is used there because I thought that
could be causing my problems. Unfortunately that was not the cause of
my problems.

thanks for your help though.

any other ideas?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Eric Schittlipz]

"Jonathan LaRosa" <jl*****@alumni.brown.edu> wrote in message
news:e6**************************@posting.google.c om...

I am trying to open a recordset and I am getting an error and I can't
figure out why.

See code below. sqlString2 does not work. sqlString does. Clearly
the problem is with the nested SELECT statement. But when I copy
sqlString2 into a new query it runs just fine. Futhermore, I have
used a nested SELECT statement with the OpenRecordset() method before
and it works just fine. Any ideas?

thanks much,
jon
Here's my code:

sqlString = "SELECT [Confirmation Sent?], [Date Confirmation] " & _
"FROM [Transaction / Billing Information Info];"

'sqlString2 = "SELECT [Confirmation Sent?], [Date Confirmation] " & _
' "FROM [Transaction / Billing Information Info] " & _
' "WHERE (([Transaction ID]) In " & _
' "(SELECT [Transaction ID] FROM [qry confirmation letter] " & _
' "WHERE (([Person ID] = 26000052))));"

Debug.Print (sqlString)

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset(sqlString, dbOpenDynaset)

Can you not use a single SQL string which is not dependent on another query?
In other words, replace "FROM [qry confirmation letter]"

[Salad]

Jon LaRosa wrote:

The reason I can't take your suggestion and create a new query rather
than use the nested SELECT statement is because that nested SELECT
statment is dynamically generated at runtime. I have removed the
references to the variable that is used there because I thought that
could be causing my problems. Unfortunately that was not the cause of
my problems.
Let's say I created a query. I do a View/SQL and the line is
Select Field1 From Table Where ThisIs That Order By HereAndThere

I could select the code from that SQL, go to my code window, and enter
strSQL = "Select Field1 " & +
"From Table " & _
"Where ThisIs That " & _
"Order By HereAndThere"

Now I have a dynamic created query at runtime.

sqlString = "SELECT [Confirmation Sent?], [Date Confirmation] " & _
"FROM [Transaction / Billing Information Info] " & _
"Inner Join [qry confirmation letter] On " & _
"[Transaction / Billing Information Info].[Transaction ID] = " & _
"[qry confirmation letter]..[Transaction ID] " & _
"WHERE [qry confirmation letter].[Person ID] = 26000052;"

Now I have created a join.
thanks for your help though.

any other ideas?

Adapt.

[Jonathan LaRosa]

Sorry Salad, but I fail to see how your example is a dynamically
generated SQL statement.

Here's my full code (including the dynamic part):

------------------
sqlForReport = "SELECT [Transaction ID] FROM " & _
"[" & Me.RecordSource & "]"

If Not (IsNull(Me.Filter) Or Me.Filter = "") Then
sqlForReport = sqlForReport & " WHERE " & Me.Filter
End If

sqlString = "SELECT [Confirmation Sent?], [Date Confirmation] " & _
"FROM [Transaction / Billing Information Info] " & _
"WHERE (([Transaction ID]) In " & _
"( & sqlForReport & "));"

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset(sqlString, dbOpenDynaset)
-----------------

This code appears in a report. The variable sqlForReport basically
gives me a list of transaction ID's that the report is displaying. I
don't know this ahead of time - it depends on what critera, if any,
the user chooses to view the report with (which becomes the filter).

Forgive me if I'm missing something obvious here, but I don't see how
what you posted is analagous to this situation.

thanks,
jon

[Salad]

Jonathan LaRosa wrote:

Sorry Salad, but I fail to see how your example is a dynamically
generated SQL statement.

Here's my full code (including the dynamic part):

------------------
sqlForReport = "SELECT [Transaction ID] FROM " & _
"[" & Me.RecordSource & "]"

If Not (IsNull(Me.Filter) Or Me.Filter = "") Then
sqlForReport = sqlForReport & " WHERE " & Me.Filter
End If

sqlString = "SELECT [Confirmation Sent?], [Date Confirmation] " & _
"FROM [Transaction / Billing Information Info] " & _
"WHERE (([Transaction ID]) In " & _
"( & sqlForReport & "));"

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset(sqlString, dbOpenDynaset)
-----------------

This code appears in a report. The variable sqlForReport basically
gives me a list of transaction ID's that the report is displaying. I
don't know this ahead of time - it depends on what critera, if any,
the user chooses to view the report with (which becomes the filter).

Forgive me if I'm missing something obvious here, but I don't see how
what you posted is analagous to this situation.

thanks,
jon

"What we have here...is a failure...to communicate"

Me. I'd debug.print your SQL. Then I'd copy the printed line to the
clipboard and paste into a SQL view window and run it. I'd probably
find your error there.

I provided you a Join method in the last response that will be much
faster than your subselect. Maybe you like subselects....if so, carry
on with that method.

Generally I would create a general query in the query builder, copy to
the clipboard the SQL command, and paste into a code window and modify
it and make is dynamic as I want.

Ahhhh...I see you aren't using a subselect anymore as in the original.
And it won't work. If you are going to use the IN, the format usually
looks something like
ID In (1,2,3)
and where you use
"WHERE (([Transaction ID]) In " & _
"( & sqlForReport & "));"
will never work as you are expecting the TXID to be found in
sqlforreport which is a record set, not a list.

Hopefully someone can tell you how to do this. I would re-read what I
provided you in this thread, since the answer to your problem is there.