OpenRecordSet Error Message 1

[Rzrbkpk]

When I run the following code, I get "'Run Time Error 3061' -Too few Parameters. Expected 3". The line that I get the error on is noted below. Any help is appreciated. (Code courtesy of PHV)

Private Sub Command272_Click()
Dim DB As Database
Dim objWord As Object
Dim rs As DAO.Recordset

'Open Word, import the text to the bookmarks and display the letter
Set objWord = New Word.Application
objWord.Visible = True
'Open Your Recordset and move to the first record
Set DB = CurrentDb
Set rs = DB.OpenRecordset("AdminReport_Final", dbOpenDynaset) ****ERROR 3061***
rs.MoveFirst
'Loop through each record of the rs, inserting each field of the record into a Bookmark in the template
Do Until rs.EOF
Set objWordDoc = objWord.Documents.Add(Template:="c:\Monthly_Report_Template.dot", NewTemplate:=False)
With objWordDoc.Bookmarks
.Item("Recap").Range.Text = rs.Fields(1)
End With
Loop
objWord.Quit
Set objWord = Nothing

End Sub

 

[abaldwin]

Can you post the SQL for the AdminReport_final query. I believe it is probably requesting parameters that you are not sending or setting in the OpenRecordset.

Used to run into this all the time with queries that referenced controls on an open form.

The error message means that JET is unable to resolve some of the names in
the query.

If the query contains links to Forms!SomeForm!SomeControl, it will work in
the query window, but not in code. The solution is to concatenate the value
of the controls into the sql string. That is, instead of:
sql = "SELECT ... WHERE SomeField = Forms!SomeForm!SomeControl;"
use:
sql = "SELECT ... WHERE SomeField = " & Forms!SomeForm!SomeControl & ";"

So you may need to create a variable to create and hold a sql statement and open it instead of a saved query.




Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[Rzrbkpk]

That's exactly what this query is doing, referencing a control in a form for criteria. I've posted the SQL from the query below. Could I put the SQL in the VBA instead of using a query? If so, can you provide the proper syntax?

SELECT DISTINCT [AdminReport_Final].Record_Number,
[AdminReport_Final].Type,
[AdminReport_Final].Classification,
[AdminReport_Final].[Name],
[AdminReport_Final].[Division(s)],
[AdminReport_Final].[Region(s)],
[AdminReport_Final].[Origin],
[AdminReport_Final].[Open Date],
[AdminReport_Final].[Close Date],
[AdminReport_Final].[Status],
[AdminReport_Final].[Value],
tblReportTable.Memo,
UserID.Name
FROM ([AdminReport_Final] INNER JOIN UserID ON [AdminReport_Final].[Name] = UserID.UserID) INNER JOIN tblCaseTable ON [AdminReport_Final].Record_Number = tblReportTable.Record_Number
WHERE ((([AdminReport_Final].[Division(s)])=[Forms]![Admin_Reports]![Division_Crit]))
WITH OWNERACCESS OPTION;

 

[abaldwin]

Exactly. Instead of starting a query create a string variable to hold all of that sql and make sure you concantenate on the Forms part. Then run the sql instead of a called query.

Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[Rzrbkpk]

Great. One quick question. What is the proper syntax for referencing tables, queries, forms, reports? What should be put in () and []? I struggle with this, so I'll usually just create a query instead of putting SQL in my code.

 

[abaldwin]

Quicky answer is

me.visible
me!txtBox0
me!textBox0.visible
Forms!MyFormName.form!txtbox().visible
Forms!myForm!mySubform.form!txtBox0.visible

dots for properties and methods
! for control


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[Rzrbkpk]

Below is the SQL that I put into my code. I'm still getting the error message.

strSQL = "SELECT DISTINCT [AdminReport_Final].Record_Number," & _
"[AdminReport_Final].Type," & _
"[AdminReport_Final].Classification," & _
"[AdminReport_Final].[Name]," & _
"[AdminReport_Final].[Division(s)]," & _
"[AdminReport_Final].[Region(s)]," & _
"[AdminReport_Final].[Origin]," & _
"[AdminReport_Final].[Open Date]," & _
"[AdminReport_Final].[Close Date]," & _
"[AdminReport_Final].[Status]," & _
"[AdminReport_Final].[Value]," & _
"[AdminReport_Final].[Name]," _ "tblCaseTable.Synopsis,userid.Name" & _
" FROM ([AdminReport_Final] INNER JOIN UserID ON " & _
"[AdminReport_Final].[Name]=UserID.UserID) INNER JOIN tblReportTable ON" & _
"[AdminReport_Final].Record_Number = tblReportTable.Record_Number" & _
" WHERE [AdminReport_Final].[Division(s)] =" & "Forms!Admin_Reports!Division_Crit

 

[abaldwin]

Change the last line:
" WHERE [AdminReport_Final].[Division(s)] =" & "Forms!Admin_Reports!Division_Crit"

to this

" WHERE [AdminReport_Final].[Division(s)] =" & Forms!Admin_Reports!Division_Crit & ";"


Also is the AdminReport_Final a query or a table?


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[PHV]

And what about this ?
...
Dim qd As DAO.QueryDef
Dim p As DAO.Parameter
Set DB = CurrentDb
Set qd = DB.QueryDefs("AdminReport_Final")
For Each p in qd.Parameters
p.Value = Eval(p.Name)
Next p
Set rs = qd.OpenRecordset()
rs.MoveFirst
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rzrbkpk]

That fixed that problem, but now I'm getting the following error:

Run-time error '3075':
Syntax error(missing operator) in query expression
'[AdminReport_Final].[Division(s)] =Division A - West'


Division A - West is the selection I chose from the combo box on the form. Sorry for being such a pest!

 

[TomCologne]

Changing the last line to:

" WHERE [AdminReport_Final].[Division(s)] =[red]'[/red]" & Forms!Admin_Reports!Division_Crit & "[red]'[/red];"

should do it,

TomCologne

 

[Rzrbkpk]

New error message:

Run-Time error '3061':
Too few parameters. Expected 2.

I'm sorry to be such a trouble. This is becoming humorous to me. Either that or I becoming delirious.

 

[PHV]

That fixed that problem
Which suggestion ?

 

[Rzrbkpk]

PHV,
Sorry.I completely missed your entry. I'll give it a shot. Are you suggesting that use my original query to circumvent using SQL in my code?

 

[TomCologne]

Is your code copied and pasted?

"[AdminReport_Final].[Name]," [red]&[/red] _ "tblCaseTable.Synopsis,userid.Name" & _
" FROM ([AdminReport_Final] INNER JOIN UserID ON " & _
"[AdminReport_Final].[Name]=UserID.UserID) INNER JOIN tblReportTable ON [red]"[/red] & _

Syntax matters!

TomCologne

 

[PHV]

Are you suggesting that use my original query to circumvent using SQL in my code?
Yes