Error 3061 Too few Parameters. Expected 1

Anonymous

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

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

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

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

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.

0 comments

Answer accepted by question author

Anonymous

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] ;

0 comments

69 additional answers

Anonymous

2011-09-14T16:38:57+00:00

I changed a query and added 3 columns to the qryYearEndReBalanceSheetTotals.

The columns are the TransDate, Source, and GLTransactionNo. Nothing changed as far as results go. But I just basically included the coluns in the query.

I have the new Union Query here with the changes to qryYearEndReBalanceSheetTotals.

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

FROM qryYearEndReBalanceSheetTotals

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

FROM qryYearEndBalanceSheetProfit;

This is what I think was mentioned int the previous post about query columns.

It should not affect the code, because the query has the same assignment. I added Source, GLTransactionNo and TransDate from qryYearEndBalanceSheetTrans on to the design grid and it looks like this

Source:"Closing Entry"

GLTransactionNo: 0

TransDate:DLookUp('FiscalYearEndDate","tblMyCompanyInfo")

Nothing has changed except the semantics in the Union SQL and new columns in qryYearEndReBalanceSheetTotals.

An update. I wouldn't change the code Ken. It should be identical in result. I tested it and it is.

I'll keep looking. One quick question, although I think it is the same thing.

QryYearEndReBalanceSheetTotals has AccountID from tblChartOfAccounts, not [EDITED HERE] qryYearEndBalanceSheetTrans which also has an AccountID (tblTransactions). I noticed that. I don't believe the join would cause a problem? The results are the same also.
Was this answer helpful?

0 comments No comments
Anonymous

2011-09-14T15:20:44+00:00

I think I Understand this and Im not completely sure if I leapfrogged a query. In the UnIon ALL SELECT in the code. FROM qryYearEndBalanceSheetProfit (The Last LIne)

If you run this query

Sum([TotalTrans])

This is the SQL

SELECT Sum(qryYearEndBalanceSheetIncome.TotalTrans) AS SumOfProfit

FROM qryYearEndBalanceSheetIncome;

The qryYearEndBalanceSheetProfit only returns 1 Value AS SumOfProfit, i.e ($5100)

Maybe I leapfrogged when I typed the Union query in SQL,by adding alias columns such as ; Forms!frmYrEndProcessing!cboRetainedEarnings As AccountD, 0 As GLTransactionNo, DLookUp("FiscalYearEndDate","tblMyCompanyInfo") As TransDate, "Closing Entrty" as Source, along with the IIf statements for [SumOfProfit] calculating the Debit and Credit Amounts to make this Closing Entry. But SumOfProfit does exist., an Alias, but I think you mentioned it would be okay.

To calculate Sum([FieldName]) It would have to return SumOfFieldName as an alias.

Im trying to step into these queries. I think I may ahve buggered up. Your expalnation of nested queries is good Ken, but I'll have to read it more than afew times to get it, although I believe that I amy be doing somethin wrong, llike I mentioned above.?

Thank you,

Rob
Was this answer helpful?

0 comments No comments
Anonymous

2011-09-14T11:16:21+00:00

A query which uses other queries must use the names of those queries and the column names returned in their result tables. You can't leapfrog a level, however; you can only reference the columns in the result tables of those queries in the current query's FROM clause, not any columns in tables or queries which those queries use if the columns are not returned by the query being referenced by the first query. So if QueryA uses QueryB which returns columns X and Y, then you can reference columns X and Y in QueryA. If QueryB uses QueryC which returns columns X,Y and Z however, you cannot reference Z in QueryA as it is not returned in QueryB's result table. Whether the column names are aliases or the names of columns in base tables is not material.

As far as the error you are receiving is concerned I can't see way this should be the case. The SQL being built for the final INSERT INTO statement looks OK to me. I assume that the queries on which it draws, qryYearEndReBalanceSheetTotals and qryYearEndBalanceSheetProfit, both open idenpendently without any problem, and that neither include parameters which would prevent the final SQL statement being executed in code for the reasons I explained earlier.
Was this answer helpful?

0 comments No comments