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-29T05:13:55+00:00

I should explain the nature of this form. The form is a dialog form to close the year end and has the mentioned combo for a user to select an account he/she wishes Profit or loss to be charged to.close the year end.

Income and expense accounts are brought back to zero and appended and the profit/loss is appended to the combo AccountID, and you are exactly right, the name of that field is an Equity type account that the user chooses in his combo selection from all equity accounts. The user only sees the Account Number and Account Name. The AccountID is hidden in Column(0), It may not be right but, it's better if the user chooses an account in the Equity type accounts, as opposed to myself dictating which account to store the profit or loss.

The problem I believe I was having or still am having is this:

The first query I ran filtered only accounts that are Income and expenses. These accounts need to be closed at year end. The sum of the these accounts is the Profit or Loss, and that amount also needs to be closed at year end but, it is'nt an Income and Expense type account, it's an Equity type account. So when I filtered the records I didn't include equity type accounts, although I need to append via the combo box selection. an equity type account.

I felt that it could never happen because, Access only wants, Income and Expense Account types, as per my filter for these records to calculate Net Income/Loss.

I changed the query to include equity, income and expense accounts. I then created another query to zero out the equity account values, remaining with only Income and expense accountid's and I LEFT JOIN the forms!frmYrEndprocessing!cboRetainedEarnings value from qryYearEndBalanceSheetNetIncome. The AccountID is the parameter value included with the Income and expense accountids and the profit/loss is in the query result as a debit or credit.. I used lower case now., just to explain.

My goal here is to append a table with the closing Debit and Credit values of all Income and Expense accounts, as well as Profit/Loss amount. The query results I get are accountID's affected, including the AccountID I enter when the parameter pops up, the amounts for all the accounts affected and zero amounts for Debit and Credit for the Equity accounts that I zeroed out in the query.

I posted the actual value of strSQL in my first post today. Do you mean post it here. Yes, here it is.

SELECT qryYearEndNetOfaccttype3.AccountID, IIf([Prof]<0,[Prof]*-1,[qryYearEndBalanceSheetNetIncome].[Debit]) AS Debit, IIf([Prof]>0,[Prof],[qryYearEndBalanceSheetNetIncome].[Credit]) AS Credit, DLookUp("FiscalYearEndDate","tblMyCompanyInfo") AS TransDate, qryYearEndBalanceSheetNetIncome.Source, qryYearEndBalanceSheetNetIncome.GLTransactionNo, qryYearEndBalanceSheetNetIncome.DateTimeStamp

FROM qryYearEndBalanceSheetNetIncome LEFT JOIN qryYearEndNetOfaccttype3 ON qryYearEndBalanceSheetNetIncome.AccountID = qryYearEndNetOfaccttype3.AccountID;

I kept some code that Ken helped me with. I'm hoping I substituted the strSql correctly, I believe the results are the same. It's not identical to the code in the module.

I hope this is what you mean't by post the ACTUAL VALUE of strSQL

Thanks,

Rob
Was this answer helpful?

0 comments No comments
Anonymous

2011-09-29T03:31:51+00:00

At a VERY WILD GUESS the problem is in the JOIN.

"FROM qryYearEndBalanceSheetNetIncome LEFT JOIN qryYearEndNetOfaccttype3 ON qryYearEndBalanceSheetNetIncome " & [Forms]![frmYrEndProcessing]![cboRetainedEarnings] & " = qryYearEndNetOfaccttype3.AccountID;"

This suggests that cboRetainedEarnings should contain the name of some field in qryYearEndBalanceSheetNetIncome, a field which should contain a valid AccountID. It sould be very unusual to have a list of Fieldnames in a combo box, but maybe you do! What is in fact in this combo? Could you post the ACTUAL VALUE of strSQL right before you try to run it?
Was this answer helpful?

0 comments No comments
Anonymous

2011-09-29T03:12:16+00:00

Sorry abou this John,

I think I did the wrong thing changing that line. I know it has the value for the cbo, 36 but, I changed it back to what it was error 3061 Too few Parameters again.

Rob
Was this answer helpful?

0 comments No comments