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

  • 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

    Wednesday, August 9, 2017 9:58 AM

Answers

  • 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!

    • Marked as answer by Mazda Z Thursday, August 10, 2017 8:14 PM
    Thursday, August 10, 2017 3:26 PM

All replies

  • 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)

    Wednesday, August 9, 2017 12:24 PM
  • 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"?
    Wednesday, August 9, 2017 2:23 PM
  • 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)

    Wednesday, August 9, 2017 4:38 PM
  • ... 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...

    Wednesday, August 9, 2017 7:02 PM
  • ... 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)])


    • Edited by Mazda Z Thursday, August 10, 2017 6:23 AM
    Wednesday, August 9, 2017 10:22 PM
  • 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

    Thursday, August 10, 2017 7:28 AM
  • 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)

    Thursday, August 10, 2017 12:52 PM
  • 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!

    • Marked as answer by Mazda Z Thursday, August 10, 2017 8:14 PM
    Thursday, August 10, 2017 3:26 PM
  • 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
    Thursday, August 10, 2017 8:22 PM
  • 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.

    Thursday, August 10, 2017 9:09 PM
  • 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)
    Sunday, August 13, 2017 12:13 PM
  • You are welcome. Glad you worked it out. :-)

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Sunday, August 13, 2017 12:23 PM