Run-Time Error 3061 :Too few parametet ,Excepted One , when i wanted to assign sql statement to the one specific text box

Question

I have a "Query" that contains either one row and Field. I used two Method ways"That mentioned as follow" to enter the result of this "Query" in a specific box, but every time I want to execute it,Instead of concluding ,"Error 3061" I receive

What solution to this problem can you offer?

Thanks for your attention

First One:

Private Sub Combo42_AfterUpdate()

Me.Text27 = CurrentDb.OpenRecordset("SELECT Sum([Query_All_In_One_Fifo_Lifo_AVR_Plus(2)].Expr1)AS SumOfExpr1 FROM [Query_All_In_One_Fifo_Lifo_AVR_Plus(2)]")

End Sub

Second One:

Private Sub Combo42_AfterUpdate()
Dim db As Database
Dim rs As DAO.Recordset
Dim SQL As String

SQL = "SELECT Sum([Query_All_In_One_Fifo_Lifo_AVR_Plus(2)].Expr1) AS SumOfExpr1 FROM [Query_All_In_One_Fifo_Lifo_AVR_Plus(2)]"


Set db = CurrentDb
Set rs = db.OpenRecordset(SQL)

   Me.Text27 = rs![SumOfExpr1]

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

End Sub

Answer 1

Did you misspell a column name? I don't see where you are explicitly using a parameter in your SQL statement so it indicates to me that you have a column (Expr1?) or expression in your SQL statement that the query processor cannot identify - so it expects a parameter.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 2

Did you misspell a column name? I don't see where you are explicitly using a parameter in your SQL statement so it indicates to me that you have a column (Expr1?) or expression in your SQL statement that the query processor cannot identify - so it expects a parameter.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Thanks for your comment , This "sql satement" is actually a "Query" with one column (SumOfExpr1) that extracts its result from another "Query" called [Query_All_In_One_Fifo_Lifo_AVR_Plus (2)] which Inside This Query is a field named "Expe1" , the" Query "point Has a parameter in one of its fields (The parameter is one of the options defined that have presented in a "ComboBox"), is the existence of this parameter caused This problem? And if so, is there any way to solve this problem "without deleting the parameter in Main Query"?

Answer 3

Did you misspell a column name? I don't see where you are explicitly using a parameter in your SQL statement so it indicates to me that you have a column (Expr1?) or expression in your SQL statement that the query processor cannot identify - so it expects a parameter.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Thanks for your comment , This "sql satement" is actually a "Query" with one column (SumOfExpr1) that extracts its result from another "Query" called [Query_All_In_One_Fifo_Lifo_AVR_Plus (2)] which Inside This Query is a field named "Expe1" , the" Query "point Has a parameter in one of its fields (The parameter is one of the options defined that have presented in a "ComboBox"), is the existence of this parameter caused This problem? And if so, is there any way to solve this problem "without deleting the parameter in Main Query"?

In your SELECT statement you need to use SQL keywords, supported functions and table/column names. You can't use a query name as the source - you will need to take the SQL out of queries and use it to build your SELECT statement.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 4

... the" Query "point Has a parameter in one of its fields (The parameter is one of the options defined that have presented in a "ComboBox"), is the existence of this parameter caused This problem? And if so, is there any way to solve this problem "without deleting the parameter in Main Query"?

Yes, you could try Leigh's Generic Recordset function.

Hope it helps...

Answer 5

... the" Query "point Has a parameter in one of its fields (The parameter is one of the options defined that have presented in a "ComboBox"), is the existence of this parameter caused This problem? And if so, is there any way to solve this problem "without deleting the parameter in Main Query"?

Yes, you could try Leigh Generic Recordset function.

Hope it helps...

Thank you for brilliant answer. But very appritioate If you can guide me, where is my mistake when I came up with "SQL Stament" in Function "fDAOGenericRst" as below, I encountered instead of answering with "#Name?" in the Text box, Where is My Fault?

=fDAOGenericRst(SELECT Sum([Query_All_In_One_Fifo_Lifo_AVR_Plus(2)].Expr1) AS SumOfExpr1
   FROM [Query_All_In_One_Fifo_Lifo_AVR_Plus(2)])

Answer 6

Did you misspell a column name? I don't see where you are explicitly using a parameter in your SQL statement so it indicates to me that you have a column (Expr1?) or expression in your SQL statement that the query processor cannot identify - so it expects a parameter.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Thanks for your comment , This "sql satement" is actually a "Query" with one column (SumOfExpr1) that extracts its result from another "Query" called [Query_All_In_One_Fifo_Lifo_AVR_Plus (2)] which Inside This Query is a field named "Expe1" , the" Query "point Has a parameter in one of its fields (The parameter is one of the options defined that have presented in a "ComboBox"), is the existence of this parameter caused This problem? And if so, is there any way to solve this problem "without deleting the parameter in Main Query"?

In your SELECT statement you need to use SQL keywords, supported functions and table/column names. You can't use a query name as the source - you will need to take the SQL out of queries and use it to build your SELECT statement.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

If I correctly understood your opinion based on your content, instead of the "Query" I used substructure of this query that Contained four related tables and the necessary conditions on so on in this "Sql statement", as you will see below

instead of :

SQL="SELECT Sum([Query_All_In_One_Fifo_Lifo_AVR_Plus(2)].Expr1)AS SumOfExpr1 FROM [Query_All_In_One_Fifo_Lifo_AVR_Plus(2)]")

I used structure that to be have same result :

SQL="SELECT Sum(SheetFifo_Lifo_AVR_Plus.Expr1) AS SumOfExpr1 FROM (GoodsEntry INNER JOIN ([Dim_KindOfFactor's] INNER JOIN SheetFifo_Lifo_AVR_Plus ON [Dim_KindOfFactor's].Id = SheetFifo_Lifo_AVR_Plus.KindOfFactor) ON GoodsEntry.LevelOneDetailedAccID = SheetFifo_Lifo_AVR_Plus.[LevelOneDetailedAccID]) INNER JOIN WareHouse ON SheetFifo_Lifo_AVR_Plus.LevelTwoDetailedAccID = WareHouse.LevelTwoDetailedAccID WHERE (((GoodsEntry.LevelOneDetailedAccID)=[Forms]![PerformanceReport]![Combo42]) AND (([Forms]![PerformanceReport]![Combo42]) Is Not Null Or ([Forms]![PerformanceReport]![Combo42])=0))"

But Unfortunately, the problem was not resolved

Answer 7

Is your issue still the same? The Form control values need to be evaluated before being added to the SQL string:

SQL="SELECT Sum(SheetFifo_Lifo_AVR_Plus.Expr1) AS SumOfExpr1 FROM (GoodsEntry INNER JOIN ([Dim_KindOfFactor's] INNER JOIN SheetFifo_Lifo_AVR_Plus ON [Dim_KindOfFactor's].Id = SheetFifo_Lifo_AVR_Plus.KindOfFactor) ON GoodsEntry.LevelOneDetailedAccID = SheetFifo_Lifo_AVR_Plus.[LevelOneDetailedAccID]) INNER JOIN WareHouse ON SheetFifo_Lifo_AVR_Plus.LevelTwoDetailedAccID = WareHouse.LevelTwoDetailedAccID WHERE (((GoodsEntry.LevelOneDetailedAccID)=" & [Forms]![PerformanceReport]![Combo42] & " AND " & [Forms]![PerformanceReport]![Combo42] & " Is Not Null Or " & [Forms]![PerformanceReport]![Combo42] & "=0))"

Sorry I can't test your SQL since I don't have your database tables and Form. You may have to verify the syntax.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 8

Thank you for brilliant answer. But very appritioate If you can guide me, where is my mistake when I came up with "SQL Stament" in Function "fDAOGenericRst" as below, I encountered instead of answering with "#Name?" in the Text box, Where is My Fault?

=fDAOGenericRst(SELECT Sum([Query_All_In_One_Fifo_Lifo_AVR_Plus(2)].Expr1) AS SumOfExpr1
   FROM [Query_All_In_One_Fifo_Lifo_AVR_Plus(2)])

Hi,

In your second sample of the AfterUpdate code, simply replace:

Set rs = db.OpenRecordset(SQL)

with this:

Set rs = fDAOGenericRst(SQL)

Let us know what happens...

Cheers!

Answer 9

Thank you for brilliant answer. But very appritioate If you can guide me, where is my mistake when I came up with "SQL Stament" in Function "fDAOGenericRst" as below, I encountered instead of answering with "#Name?" in the Text box, Where is My Fault?

=fDAOGenericRst(SELECT Sum([Query_All_In_One_Fifo_Lifo_AVR_Plus(2)].Expr1) AS SumOfExpr1
   FROM [Query_All_In_One_Fifo_Lifo_AVR_Plus(2)])

Hi,

In your second sample of the AfterUpdate code, simply replace:

Set rs = db.OpenRecordset(SQL)

with this:

Set rs = fDAOGenericRst(SQL)

Let us know what happens...

Cheers!

That's an impressively creative solution!Thanks

Answer 10

That's an impressively creative solution!Thanks

Hi,

Glad to hear it worked for you. Thanks to Leigh for coming up with this function. Good luck with your project.

Answer 11

Is your issue still the same? The Form control values need to be evaluated before being added to the SQL string:

SQL="SELECT Sum(SheetFifo_Lifo_AVR_Plus.Expr1) AS SumOfExpr1 FROM (GoodsEntry INNER JOIN ([Dim_KindOfFactor's] INNER JOIN SheetFifo_Lifo_AVR_Plus ON [Dim_KindOfFactor's].Id = SheetFifo_Lifo_AVR_Plus.KindOfFactor) ON GoodsEntry.LevelOneDetailedAccID = SheetFifo_Lifo_AVR_Plus.[LevelOneDetailedAccID]) INNER JOIN WareHouse ON SheetFifo_Lifo_AVR_Plus.LevelTwoDetailedAccID = WareHouse.LevelTwoDetailedAccID WHERE (((GoodsEntry.LevelOneDetailedAccID)=" & [Forms]![PerformanceReport]![Combo42] & " AND " & [Forms]![PerformanceReport]![Combo42] & " Is Not Null Or " & [Forms]![PerformanceReport]![Combo42] & "=0))"

Sorry I can't test your SQL since I don't have your database tables and Form. You may have to verify the syntax.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Really Thankful for your attention, I think "TheDBGuy" answered my question (To the extent that it was needed)

Answer 12

You are welcome. Glad you worked it out. :-)

---

Paul ~~~~ Microsoft MVP (Visual Basic)