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: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2011-09-29T05:18:42+00:00

    I forgot to give you some examples of what is in the combo.

    3000 Guy Tremblant, Capital

    3005 Guy Tremblant, Drawings

    3010 Contributed Capital

    3015 Net Income Loss

    3020 Retained Earnings Beginning

    3030 Retained Earnings

    3040 Common Shares

    3050 Prefferred Share

    This is an example, they are not the exact account munbers. but all equity accounts are on the list.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-09-29T05:27:16+00:00

    ok... still in the fog here a bit but here's a possible problem:

    SELECT qryYearEndNetOfaccttype3.AccountID, IIf([Prof]<0,[Prof]*-1,[qryYearEndBalanceSheetNetIncome].[Debit]) AS Debit, IIf([Prof]>0,[Prof],[qryYearEndBalanceSheetNetIncome].[Credit]) AS Credit,

    YOu have circular definitions here. You're defining the field named Debit in terms of the field name Debit, and the same with Credit. You can't alias a field as itself! What happens if you remove the

    AS Debit

    and

    AS Credit

    from the query? Or are there fields with those names elsewhere in the query? If so you'll need to choose a different alias name.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-09-29T15:00:21+00:00

    First of all the fields Debit and Credit are the field name that I have to append the data to in tblTransactions.  I wish Access would catch the error and leave a better description.

    A quick rundown, might clarify. 

    Query 1=(tblTransactions is the only table in the query Grid.  My first query returns all Transactions with all the fields in that table for the entire year.  so, that would be form StartDate to FiscalYearEndDate.

    Query 2 = Returns the AccountID  and a calculated field called TotalTrans, which is the net Sum of the AccountID's Debits and Credits for the entire year. (AccountID from AccountTypeID 3(EquityAccounts) and AccountTypeID 4,5,6(Income, Costof Goods Sold and Expense accounts)

    The Result of Query 2 is the Total for each account  affected as TotalTrans.  These are Accounts that need to be closed, except I included all the Equity Accounts(AccountTypeID=3)

    So the query Grid  AccountTypeID, Criteria >=3 and <=6. It is hidden, No checkbox on Show.

    Query 3= I have to take the TotalTrans for Equity Accounts from the Previous query and bring them to zero.  The reason,  you don't close Equity accounts, althought they have a TotalTrans amount.  I created an alias call Etrans.  IIf([AccountTypeID]=3, [TotalTrans]},0).  This new column shows the Values only for Equity Accounts in the Etrans Column.

    Then I subtract as follows[TotalTrans]-[ETrans]=[Prof] , a new column.

    I'm trying my best to explain this and I think you understand.

    I forgot to mention that other fields from tblTransactions are in the query 5 results as expressions. i,e  AccountID:Forms!frmYrEndprocessing!cboRetainedEarnings  DateTimeStamp:Now(),    Source:"Closing Entry",   TransDate:DLookUp("FiscalYearEndDate",""tblMyCompanyInfo").   GLTransactionNo:0    In query 5

    Query 4  Sum of [Prof] = Profit  (Calaculation of Profit)

    Query 5 Evaluates Profit   (IIf[Profit]>0,[Profit},0) As Debit

                                                   (IIf[Profit]<0-1*[Profit],0) As Credit

    This is qryYearEndBalanceSheetNetIncome.

    The values from query 5 are as you posted.  When I used the [qryYearEndBalanceSheetNetIncome].{Debit]  and the same with Credit, this shows the Profit value that needs to be assigned tot he combobox AccountID value with the JOIN..  I hope I undeerstand this SQL.

    Do you get this? I originally had only Income and Expense accounts only (AccountTypeID 4-6) to filter the accounts, but the intention of using a union query to pull in the Net Income/Loss for AccountTypeID 3, didn't work.  I felt it didn't work necause I filtered the data only for 4-6 AccountTypeID's. Thus the need for a Union query, I thought.  The parameter I believed that was missing was to include AccountTypeID 3, equity accounts, which is the cboRetainedEarningsvalue, =AccountTypeID 3.  I'm still not really sure if that's the problem.

    When I ran the first statement of the Union sql as a test, it did work in code and posted the transaction, but this does not include the Profit/Loss amount.  That amount was in the second UNION ALL SELECT statement.  You can see my first post yesterday.  I amended that to 1 sql statement.  I think you are correct about the alias.

    I basically took values i.e 0 As GLTransactionNo.  This is not a returned value froma query.

    Not sure if I could do that.

    Thanks,

    Rob.   I'm thinking about putting an end to this and just create another sql to insert the profit.

    Do you know how to get the value of GLTransactionNo  field in the last record of the table, so it will be part of the same entry?  Dlookup(GLTranactionNo", tblGLTransactions, value of the last GlTransactionNo)  or will WHERE DMax("GLTransactionNo',"tblGLTransactions")=GLTransactionNo.  If I were to use 2 INSERT sql's.  1 for the Income and Expense accounts and 1 for the Profit/Loss account

    Thanks John,

    Rob

    But when I ran the Union query it worked from the query, not code.

    Was this answer helpful?

    0 comments