Error 3061 Too few Parameters. Expected 1

Anonymous
2011-09-10T21:05:32+00:00

I'm using a Union query as a Source for an Append query.  I will leave the code here.  tblGLTransactions and tblGLTranaactionNo. I don't know if that is causing a problem.  The tblGLTransactions has 2 fields, the fiest field GLTransactionNo is an autonumber field, so I didn't append to that field.  I need both tables because of the relationship for a form and subform for GLTransactions.  The entry I'm trying to append closes the year end.  Well, it should.

I should post the union query as well.   I have drained out John Vinson who has been helping my like crazy in the past two days.  This is the final step in completing this database and I cannoyt believe it crashes.

Here is the Union query ......I changed it.  No AccountTypeID and no AcctgDate.  IT's been modified for the extra fields. EDITED 09/12/11 12:51

SELECT qryYearEndReBalanceSheetTotals.AccountID, IIf([TotalTrans]<0,[TotalTrans]*-1,0) AS Debit, IIf([TotalTrans]>0,[TotalTrans],0) AS Credit, 0 AS GLTransactionNo, "Closing Entry" AS Source,qryYearEndReBalanceSheetTotals.TransDate FROM qryYearEndReBalanceSheetTotals

UNION ALL SELECT  ([Forms]![frmYrEndProcessing]![cboRetainedEarnings]) AS AccountID, IIf([SumOfProfit]<0,[SumOfProfit],0) AS Debit, IIf([SumOfProfit]>0,[SumOfProfit],0) AS Credit, 0 AS GLTransactionNo, "Closing Entry" AS Source, DLookUp("FiscalYearEndDate","tblMyCompanyInfo") AS TransDate FROM qryYearEndBalanceSheetProfit;

 Here is the code:  I created another query with GLTansactionNo and AcctgDate.   The same 2 fields as the table.  It still fails with the same Error 3061. Like the Title

   Thanks for any help.  this coded edited Sunday 09/12/11 12:54 pm

Private Sub cmdOkay_Click()

On Error GoTo ErrorHandler

    Dim dbs As DAO.Database

    Dim rst As DAO.Recordset

    Dim strSQL As String

    Dim lngID As Long

    Dim strMessage As String

    Dim IntMessageDialog As Integer

    Dim strTitle As String

    Dim intResult As Integer

     If Me!chkPrintFinancials = False Then

     MsgBox ("Print Financial Statements before Closing the Year End." & vbCrLf & _

     "Ensure Start and Fiscal Year End Dates are not Blank in My Company Information Form.")

     Else

                        If Nz(Me![cboRetainedEarnings].Value) = "" Then

                        MsgBox ("Choose an Account for Retained Earnings.")

                    End If

                End If

                    If Not Me.chkPrintFinancials = False And Me.cboRetainedEarnings > -1 Then

                   Me.cmdCancelAndExit.Enabled = False

                Else: Exit Sub

            End If

         DoCmd.Beep

                strMessage = "Are you sure you want to Close the Year End?"

                IntMessageDialog = vbQuestion + vbYesNo + vbDefaultButton1

                strTitle = "Confirm Year End Close"

                intResult = MsgBox(strMessage, IntMessageDialog, strTitle)

                        If intResult = vbNo Then

                                Exit Sub

                        End If

                        If intResult = vbYes Then

                            If IsNull(DLookup("FiscalYearEndDate", "tblMyCompanyInfo")) Then

                       MsgBox "Please fill in the fiscal year end date in the My Company info form", vbOKOnly

                        Exit Sub

                            Else

        strSQL = "INSERT INTO [tblGLTransactions] ( AcctgDate) SELECT AcctgDate FROM " _

        & "[qryYearEndGLTransactions] ;"

        CurrentDb.Execute strSQL, dbFailOnError

        lngID = DMax("GLTransactionNo", "tblGLTransactions")

        strSQL = "INSERT INTO [tblTransactions] (TransDate, AccountID, Debit, Credit," _

        & " Source, GLTransactionNo)" _

        & "SELECT TransDate, AccountID, Debit, Credit," _

        & "Source, " & lngID & " As GLTransactionNo FROM [qryuniYearEndFinalClosingEntry] ;"

        CurrentDb.Execute strSQL, dbFailOnError

        DBEngine(0)(0).Execute "qupdYearEndDateChanges", dbFailOnError

         MsgBox ("The Year End is Processed.")

    End If

End If

       DoCmd.Close acForm, Me.Name

        DoCmd.OpenForm "frmEnterGLTransactions"

        DoCmd.GoToRecord , , acLast

ErrorHandlerExit:

   Exit Sub

ErrorHandler:

   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description

  Debug.Print strSQL

   Resume ErrorHandlerExit

End Sub

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2011-09-18T17:45:44+00:00

I will reply, Rob, if only to wish you good luck.  I wish I'd been able to resolve the issue for you, but I think we'd just be herding those kittens for evermore I'm afraid.

One final point of detail.  I'd recommend:

"#" & Format(Now(),"yyyy-mm-dd hh:nn:ss") & "#" & _

By using the ISO standard for date and time notation it caters for whatever Windows local date format is in use.  As you'd written it, it would fail on my system for instance as the UK short date format is dd/mm/yyyy, so 4th July would be changed to 7 April, which your neighbours to the south would not appreciate.

Was this answer helpful?

0 comments
Answer accepted by question author
Anonymous
2011-09-12T23:19:56+00:00

Mea culpa. I left out spaces in two places.  BEFORE SELECT and BEFORE FROM

strSQL = "INSERT INTO [tblTransactions] (TransDate, AccountID, Debit, Credit," _

        & "Source, GLTransactionNo)" _

        & " SELECT TransDate, AccountID, Debit, Credit, Source," & lngID _

        & " FROM [qryuniYearEndFinalClosingEntry] ;"

        CurrentDb.Execute strSQL, dbFailOnError

If lngId is equal to 33, then Debug.Print should return

INSERT INTO [tblTransactions] (TransDate, AccountID, Debit, Credit,Source, GLTransactionNo) SELECT TransDate, AccountID, Debit, Credit, Source,33 FROM [qryuniYearEndFinalClosingEntry] ;

Was this answer helpful?

0 comments

69 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-09-17T22:37:21+00:00

    The tblTransactions has a DateTimeStamp field, General Date Type, with a default value of Now().  I didn;t include this field as field that need to be appended.

    If the DateTimeStamp values are distinct, as I'd expect, then it would be better not to use a single 'append' query to insert the rows, but to loop through a recordset and insert the rows one by one.  You can then increment the DateTimeStamp values, by one second say, at each iteration of the loop.  You could still have a problem with parameters, however, if they are not in the last of a set of nested queries, as I don't think they will be part of the parameters collection of the query on which you are establishing the recordset, so can't be evaluated in the usual way, as with the code snippet I posted.  I'm not absolutely sure of this as I don't recall ever encountering that situation myself, but I suspect you'd have to evaluate the parameters of whichever query or queries contain them, not merely the topmost one.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-09-17T16:55:35+00:00

    Hi Ken,

    I have to figure this out on my own and I agree with your last paragraph.

    Thank you for taking time and patience to explain relevance in my questions  on every post.

    Sometimes "I jump the gun" and feel like I maybe missing something and I end up creating some self-doubt abot the creation of the queries.  This only happened after the query didn't work in code.

    I have to admit that I feet  nested queries are necessary to capture those numbers that represent totals for accounts to close at year end.

    The parameter in frmYrEndProcessing!cboRetainedEarnings is returning the GLTransactionNo as Long Integer Datatype, whcih should be column (0) , the bound Column in the combo box.  I believe that is fine.

    My post yesterday was the over evaluation, if there is such a term, to describe that My aith in my queries diminshed.

    I reality, there is a bit of a twist when you wish to append records based on a query,  The results from the query that is run from the design grid look fine.  For understanding the result, I may have added and extra field on the design grid, such as AccountNum, to make the data more easier to understand, since AccountID can be any number from 0 to 150, lets say.  I can add an account athe AccountID will be larger tha other accountID's in the same AccountType.

    Well, I'm going to look at the table, tblTransactions.  I know I mentioned earlier in error that AccountID was not indexed, but in fact it is indexed with Duplicates Okay.

    I really feel abit of a jinx in this.  You mentioned there may be something wrong with the stucture.  The tblTransactions has a DateTimeStamp field, General Date Type, with a default value of Now().  I didn;t include this field as field that need to be appended.

    I know I'm thinking of alot of thingds and honestly, I understand that you cannot see the database, and because the data is accounting data, it may not be easy to undestand the twistiing/manipulation of TotalTrans, the calculated field, as the sum of account Debits Less the Sum of Account Credits for accouts affected in this yeard end closing entry that only afffects 12 records in journal entry.

    Listen, Ken.  Thank you so much for all your help from when I first came here right up until today.  I learned alot from you and I'm grateful.  Stick your Anglo Name, Ken Sheridan with out the apostrophe.  I'm only kidding, interesting to see what your Irish, non-angliiczed name is. 

    All the best to you my friend,

    Your friend, Rob

    You have my email, I'm on Toronto, Ontario Canada.....feel free for any help you may need, you have a friend here.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-09-16T21:24:58+00:00

    Firstly, let's deal with this question of parameters.  The parameter expected is the reference to the control [Forms]![frmYrEndProcessing]![cboRetainedEarnings].  When you open or execute a query via the user interface the parameter is evaluated and the query opens or executes without any problem.  If on the other hand you Execute a query in code which includes a parameter the same is not true.  One way of handling this is to evaluate all members of the query's Parameters collection.  The following is a code snippet from one of my applications which does this and then returns a reference to the query's recordset, which in this case is simply to see if it is empty or not, so that the code which follows (a mail merge) is not executed if there are no records to merge:

        Set qdf = dbs.QueryDefs(strQuery)

        For Each prm In qdf.Parameters

            prm = Eval(prm.Name)

        Next prm

        Set rst = qdf.OpenRecordset

    If building an SQL statement in code, however, its far simpler just to concatenate the value of the parameter into the string expression.  The SQL statement then does not contain any parameters, so there is no error.  So in your case the relevant part of the string expression would be as follows (I assume that your really mean UNION ALL SELECT, not UNION SELECT ALL)

    "UNION ALL SELECT " & [Forms]![frmYrEndProcessing]![cboRetainedEarnings] & " AS AccountID,"

    This assumes that the data type of cboRetainedEarnings is numeric, but if it's text data type it would be:

    "UNION ALL SELECT """ & [Forms]![frmYrEndProcessing]![cboRetainedEarnings] & """ AS AccountID,"

    A contiguous pair of quotes is interpreted as a single quotes character.  This is how you include literal quotes characters in a string already delimited by quotes characters.  Or you can use a single quotes character, but beware of values with apostrophes, like Irish names.  My name in its original non-anglicised form is Cináed O'Siridean, which can't be wrapped in single quotes because of the apostrophe in my surname.

    As regards the computed TotalTrans column I don't think this has any bearing on the issue.  

    To be honest I have no idea why you are experiencing the syntax error.  I may be missing something obvious; I've often stared for a long time at SQL of my own without spotting an error, then, when the penny finally drops, wondering how I ever missed it.  But no matter how long I look at yours I can't see where the problem might be.  I really don't think I'm able to offer any constructive suggestions on this.  One problem is that we are only seeing one small element of your database, and I for one only have a very sketchy picture in my mind of the underlying logical model.  The source of the problem may be more deep seated than the surface manifestation which we see in your posts, but without an intimate knowledge of the underlying structure I cannot suggest where to start looking.   It is now a matter of you using your own familiarity with the totality of the application, and of the real world situation being modelled, to pin down and eliminate the source of the problem.

    Was this answer helpful?

    0 comments