Run Time Error 3061

[bstice]

I have the following code that generates the Run Time Error 3061 - too few parameters - Expected 1.
Dim db As DAO.Database
Dim rs As DAO.Recordset

Select Case UCase(Me.AccountingPeriod)
Case "Q1"
AccountingPeriod = "[Indorders].[Accounting Period]='200801' OR [Indorders].[Accounting Period]='200802' OR [Indorders].[Accounting Period]='200803'"
Case "Q2"
AccountingPeriod = "[Indorders].[Accounting Period]='200804' OR [Indorders].[Accounting Period]='200805' OR [Indorders].[Accounting Period]='200806'"
Case "Q3"
AccountingPeriod = "[Indorders].[Accounting Period]='200807' OR [Indorders].[Accounting Period]='200808' OR [Indorders].[Accounting Period]='200809'"
Case "Q4"
AccountingPeriod = "[Indorders].[Accounting Period]='200810' OR [Indorders].[Accounting Period]='200811' OR [Indorders].[Accounting Period]='200812'"
Case Else
AccountingPeriod = "[Indorders].[Accounting Period]='" & Me.AccountingPeriod & "'"
End Select
'str = "SELECT Orders.[Accounting Period], Orders.[Order Number], Orders.[Order Booked Date], Orders.[Organization Code], Orders.[Master Customer Name], Orders.[Master Customer Number], Orders.[Product Service Code], Orders.[Product Class], Orders.[Product ID], Orders.[Product Description], Orders.[Offering Accounting Type Code], Orders.[Net Order Value-US MTD], Orders.[Primary Salesperson Name]FROM Orders
'WHERE ((" & AccountingPeriod & ") and ((Orders.[Product Service Code])= 'GP29') AND ((Orders." & FIELD1 & ")='" & crit1 & "'));"
str = "SELECT [Indorders].[Accounting Period], [Indorders].[Order Number], [Indorders].[Order Booked Date], [Indorders].[Organization Code], [Indorders].[Master Customer Name], [Indorders].[Master Customer Number], [Indorders].[Product Service Code], [Indorders].[Product Class], [Indorders].[Product ID], [Indorders].[Product Description], [Indorders].[Offering Accounting Type Code], [Indorders].[Net Order Value-US MTD], [Indorders].[Primary Salesperson Name], Hierarchy.[Region 0], Hierarchy.Region, Hierarchy.Industry, Hierarchy.[Level 1], Hierarchy.[Level 2], Hierarchy.[Level 3]" _
& "FROM [Indorders] INNER JOIN Hierarchy ON [Indorders].[Organization Code] = Hierarchy.[FML ORG CODE]" _
& "WHERE (" & AccountingPeriod & ") AND (([Indorders].[Product Service Code])= 'GP29') AND (([Indorders]." & FIELD1 & ")='" & crit1 & "');"

Set db = CurrentDb
Set rs = db.OpenRecordset(str, dbOpenSnapshot)

Any thoughts why it would generate this error? Thanks all.

Brennan

 

[bstice]

I should add that I determine field1 and crit1 as from textboxes on a form.
Dim AccountingPeriod As String
Dim FIELD1 As String
Dim crit1 As String
FIELD1 = Me.field
crit1 = Me.OrgDetail

 

[redneckgeek]

I doubt this would cause the error your seeing, but it looks like you need to insert a space before the FROM and the WHERE.

Did you try viewing the value of str with the debugger? Does it contain what you'd expect it to contain?

 

[MSAccessRookie]

I have the following code that generates the Run Time Error 3061 - too few parameters - Expected 1.
Dim db As DAO.Database
Dim rs As DAO.Recordset

Select Case UCase(Me.AccountingPeriod)
Case "Q1"
AccountingPeriod = "[Indorders].[Accounting Period]='200801' OR [Indorders].[Accounting Period]='200802' OR [Indorders].[Accounting Period]='200803'"
Case "Q2"
AccountingPeriod = "[Indorders].[Accounting Period]='200804' OR [Indorders].[Accounting Period]='200805' OR [Indorders].[Accounting Period]='200806'"
Case "Q3"
AccountingPeriod = "[Indorders].[Accounting Period]='200807' OR [Indorders].[Accounting Period]='200808' OR [Indorders].[Accounting Period]='200809'"
Case "Q4"
AccountingPeriod = "[Indorders].[Accounting Period]='200810' OR [Indorders].[Accounting Period]='200811' OR [Indorders].[Accounting Period]='200812'"
Case Else
AccountingPeriod = "[Indorders].[Accounting Period]='" & Me.AccountingPeriod & "'"
End Select
'str = "SELECT Orders.[Accounting Period], Orders.[Order Number], Orders.[Order Booked Date], Orders.[Organization Code], Orders.[Master Customer Name], Orders.[Master Customer Number], Orders.[Product Service Code], Orders.[Product Class], Orders.[Product ID], Orders.[Product Description], Orders.[Offering Accounting Type Code], Orders.[Net Order Value-US MTD], Orders.[Primary Salesperson Name]FROM Orders
'WHERE ((" & AccountingPeriod & ") and ((Orders.[Product Service Code])= 'GP29') AND ((Orders." & FIELD1 & ")='" & crit1 & "'));"
str = "SELECT [Indorders].[Accounting Period], [Indorders].[Order Number], [Indorders].[Order Booked Date], [Indorders].[Organization Code], [Indorders].[Master Customer Name], [Indorders].[Master Customer Number], [Indorders].[Product Service Code], [Indorders].[Product Class], [Indorders].[Product ID], [Indorders].[Product Description], [Indorders].[Offering Accounting Type Code], [Indorders].[Net Order Value-US MTD], [Indorders].[Primary Salesperson Name], Hierarchy.[Region 0], Hierarchy.Region, Hierarchy.Industry, Hierarchy.[Level 1], Hierarchy.[Level 2], Hierarchy.[Level 3]" _
& "FROM [Indorders] INNER JOIN Hierarchy ON [Indorders].[Organization Code] = Hierarchy.[FML ORG CODE]" _
& "WHERE (" & AccountingPeriod & ") AND (([Indorders].[Product Service Code])= 'GP29') AND (([Indorders]." & FIELD1 & ")='" & crit1 & "');"

Set db = CurrentDb
Set rs = db.OpenRecordset(str, dbOpenSnapshot)

Any thoughts why it would generate this error? Thanks all.

Brennan

When the Error occurs, I assume you get the Standard End/Debug message. What line is highlighted when you choose Debug?

I do not have an answer for you at this time, but I would like to make a suggestion about the query:

1. Instead of compount OR Clause, you might want to consider an IN Clause instead.
   • AccountingPeriod IN ('200807', '200808', '200809'")

 

[MSAccessRookie]

I doubt this would cause the error your seeing, but it looks like you need to insert a space before the FROM and the WHERE.

Did you try viewing the value of str with the debugger? Does it contain what you'd expect it to contain?

The missing spaces may or may not be related to the cause of the problem, but they will make the code fail when it does try to run, so they should be added in.

Good catch by the redneckgeek.

 

[bstice]

In the debugger the following line generates the error:

Set rs = db.OpenRecordset(str, dbOpenSnapshot)

I added the spaces as suggested and I like MSAccessRookie's idea of using in vs. or. Thanks for your help guys. I'll make sure and add positive reputation for you both.

B

 

[bstice]

Also, the variables are showing up as they should.

B

 

[bstice]

Figured it out guys - thanks again for your help!

 

[MSAccessRookie]

Also, the variables are showing up as they should.

B

This error can occur when using the OpenRecordset method (DAO code) when a table, column, or Form Control Field name is wrong in the SQL. It also occurs with some other SQL syntax errors. Therefore you might want to take a closer look at the SQL statement to look for an error that would cause it not to run.

 

[MSAccessRookie]

Figured it out guys - thanks again for your help!

Great! What did you find the issue to be?

 

[Lrn2Code]

I'm having a similar issue with Error 3061 and can't figure out (even from reading the posts here) exactly what's going on with my code. I need to run the select statement and take a count of the recordset to determine if a change has been made. If a change has been made the original button on the form that was pressed to get to this secondary form needs to be updated with an ellipse "..." on it.

At this point I can't figure out either issue and am looking for a little guidance. My code is below -

strSQL = "SELECT Qry_TeamTeachers.TeamTeachID, Qry_TeamTeachers.EducatorID, Qry_TeamTeachers.CourseID, Qry_TeamTeachers.FName, Qry_TeamTeachers.LName FROM Qry_TeamTeachers WHERE Qry_TeamTeachers.EducatorID=forms!frmteacherdatanew!txtID And Qry_TeamTeachers.CourseID=forms!frmteacherdatanew!frmclassdetailsub!cmbclassname;"
Set rst = CurrentDb.OpenRecordset(strSQL)
C = rst.RecordCount(strSQL)


If C <> Null Then

Set Form_frmTeacherDataNew.tabcourse.Controls.Item(Cmd.Team).Caption = " & ... & "

End If

What is causing the too few parameters 2 error? I've been working on the SQL statement - removed multiple parentheses - (also will that ellipse code even work, or is there a better way to change the look of a form button if the record has been updated?)

Thank you!

 

[pbaldy]

It can't evaluate the form references; you have to concatenate in the values, like:

"...WHERE Qry_TeamTeachers.EducatorID= " & forms!frmteacherdatanew!txtID & " And ..."

If the data types are text or date, you would have to surround the values with ' or # respectively.

 

[Lrn2Code]

Okay...I'll try to see what happens with those changes. Thank you!

 

[Lrn2Code]

Now I'm getting an invalid operation error -

strSQL = "SELECT Qry_TeamTeachers.TeamTeachID, Qry_TeamTeachers.EducatorID, Qry_TeamTeachers.CourseID, Qry_TeamTeachers.FName, Qry_TeamTeachers.LName FROM Qry_TeamTeachers WHERE Qry_TeamTeachers.EducatorID= " & Forms!frmteacherdatanew!txtID & " And Qry_TeamTeachers.CourseID= " & Forms!frmteacherdatanew!frmClassDetailSub!cmbClassName & ";"
Set rst = CurrentDb.OpenRecordset(strSQL)
C = rst.RecordCount(strSQL)

There are no date fields here, both educator and course IDs are number fields.

 

[pbaldy]

What line throws the error? And I suspect instead of this:

C = rst.RecordCount(strSQL)

you want

C = rst.RecordCount

though with a DAO recordset, the record count is not fully populated until you move to the end of the recordset. If all you're after is the count, I'd go a different way anyway.

 

[Lrn2Code]

After I removed the (strSQL) from C = rst.RecordCount(strSQL) the error was gone. So that's great! Thank you!!!

It still is not putting the ellipse on the button from the original form to let the user know there is a team teacher associated with that course. Apparently the "set" code I have below doesn't do anything.


If C <> Null Then

Set Form_frmTeacherDataNew.tabcourse.Controls.Item(Cmd .Team).Caption = " & ... & "

End If

The C <> null is running correctly but it's not changing the look of the button. Do you know how can that be accomplished?

Thank you.

 

[pbaldy]

The syntax would be:

Forms!FormName.ControlName.Caption = " & ... & "

 

[Lrn2Code]

Thank you very, very much! You're a Godsend. Have a great weekend!

 

[pbaldy]

No problem, and you too!

 

[MSAccessRookie]

Now I'm getting an invalid operation error -

strSQL = "SELECT Qry_TeamTeachers.TeamTeachID, Qry_TeamTeachers.EducatorID,
Qry_TeamTeachers.CourseID, Qry_TeamTeachers.FName, Qry_TeamTeachers.LName
FROM Qry_TeamTeachers WHERE Qry_TeamTeachers.EducatorID= " &
Forms!frmteacherdatanew!txtID & " And Qry_TeamTeachers.CourseID= " &
Forms!frmteacherdatanew!frmClassDetailSub!cmbClassName & ";"
Set rst = CurrentDb.OpenRecordset(strSQL)
C = rst.RecordCount(strSQL)

There are no date fields here, both educator and course IDs are number fields.

Is cmbClassName an ID Number for the Class (like CourseID), or does the Field contain the name of the calss? If the latter is true, then you need to surround the String Field with quotes "'". That would make it look like this:

[COLOR=green]strSQL = "SELECT Qry_TeamTeachers.TeamTeachID, Qry_TeamTeachers.EducatorID, [/COLOR]
[COLOR=green]Qry_TeamTeachers.CourseID, [/COLOR][COLOR=green]Qry_TeamTeachers.FName, Qry_TeamTeachers.LName [/COLOR]
[COLOR=green]FROM Qry_TeamTeachers [/COLOR]
[COLOR=green]WHERE Qry_TeamTeachers.EducatorID= " & Forms![/COLOR][COLOR=green]frmteacherdatanew!txtID & [/COLOR]
[COLOR=green]" And Qry_TeamTeachers.CourseID= [SIZE=4][COLOR=red][B]'[/B][/COLOR][/SIZE]" & [/COLOR]
[COLOR=green]Forms!frmteacherdatanew!frmClassDetailSub![COLOR=seagreen]cmbClassName[/COLOR] & "[SIZE=4][COLOR=red][B]'[/B][/COLOR][/SIZE];"[/COLOR]
[COLOR=green]Set rst = CurrentDb.OpenRecordset(strSQL)[/COLOR]
[COLOR=green]C = rst.RecordCount(strSQL)[/COLOR]