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-14T02:11:07+00:00

A question to Both Ken and John. I'm just thinking, but I beilieve Access remembers everything when it comes to queries based on queries.

I redid some quieries this morning.. It was the same concept when I posted this question, but I just summed a grand total for the Affected accounts for the Entire Year, less records, in other words.

OFF the top of my head it's something like this.

SumOfProfit is an Alias( So I have a name that is suitable to the data).

It actually is the Sum of TotalTrans. Which in simple terms is the Net of all Debits and all Credits that are Income Statement accounts. This is where AccountTypeID in my 2nd query filters all Transactions where AccountTypeID>=4.......The Income Statement Accounts, so that I could figure out the Net Income or Loss.

SumOfProfit = Sum[TotalTrans]

TransDate has criteria in the first query I produce that has all transactions for the Fiscal Year.

Critera is as follows:

Between DLookUp("StartDate","tblMyCompanyInfo") And DLookUp("FiscalYearEndDate","tblMyCompanyInfo")

Query1 = All Transactions for the Fiscal Year.

Query2 = Filtered Transactions for Income Statement Accounts only

Query 3 = Sum Of Debits and Sum of Credit Income Statement accounts This does not have Profit or loss, because, the Net Income or Loss is Equity, Not Income or Exoenses.

(Thus the need for the Union Query requirement to add the SumOfProfit to an AccountID as a Debit or Credit.)

Query 4 = Sum Of Profit (In essence is Net Income Or Loss) = Sum Of TotalTrans(IncomeStatement accounts only) Query 2

(A) Query 4 Alias names. The second part ot theUnion query, Forms!frmYrEndProcessing! cboRetainedEarnings As AccountID, Alias SumOfProfit, as Debit Alias Sum Of Profit as Credit,, DLookup the FiscalYearEndDate as TransDate Closing Entry As Source, 0 As GLTransactionNo

The first query in the Union Query pulls in the IncomeStatement Balances [TotalTrans} as Debit and [TotalTrans] as Credit , AccountID, "Closing Balance" as Source, 0 as GLTransactionNo., DLookuo FiscalYearEndDate as TransaDate

I know this isn't exactly like the query, this just a quick rundown,if you will.

My question is that all the Alias names I use in (A) to form the Union query, could that cause a problem? It runs right and joins the SumOfProfit to an AccountID for the closing Entry when the form is open and this is the append to tblTransactions. It works.

Sorry about the accounting lesson, sort of. I'm just wondering if access is catching something I missed. in the queries I don't think so, though

Thanks, If anything sticks out. let me know. I know AccountTypeID is required for me to get these numbers, but isn't part of tblTransactions.. Does Access expect this AccountTypeID?

Rob
Was this answer helpful?

0 comments No comments
Anonymous

2011-09-14T00:41:39+00:00

ken. the :" are there, I just couldn't see them. I don't think I answered your question about assigning a GLTransactionNo to each record. No, there should be just 1 GLTransactionNo that is equal to the Parent form (The first strSQL that commits the record) It is correct the way it now. The tblGLTransactionNo is incremnting by 1 because of the Autonumber field. I did not include GLTransactionNo in my first strSQL. This is assigning and executing properly. It is the second table, tblTransactions which only requires the same GLTransaction with he DMax function, and GLTransactionNo is Long Integer. No index.

It is the recordsource for the subform and will have many records with the same GLTransaction No on the main form. Everything should be fine.

This error 3131 synatax error in FROM Clause. I don't even know why? it looks good in the Immediate window.

Do you believe it has something to do with the Select statement I used in the union query.

When I run the union query from the Query Objects in Access, it looks right with the exception of 0 as the GLTransactionNo. I ran it with form open and the correct AccountID is there. This happening in the code you supplied. I can see the correct date and AccountID, "Closing Entry and GLTransactionNo. But the Debit and Credit fields are not in the immediate window with values. I'm wondering now why the Union Query shows everything in the Access query Objects after I run the query, but won't show up here in the strSQL, whick looks right. I think you really isolated everything well with your code. and wanting to do the whole kit and caboodle. That mind of yours will not rust. John is sharp too. I want to mark answer to both of you, is that possible? I'be been at this for almost a week now. What could I be doing wrong. I don't understnd the semantics of syntax. i.e yesterday John reoved a space before FROM and to day I see "From and " From, how do I know which is right?

Sorry I guess the nerves are kicking in.

Take care guys,

Rob

Rob
Was this answer helpful?

0 comments No comments
Anonymous

2011-09-13T23:19:31+00:00

Ken, I don't think it pasted the code in it'sentirety.

Let me try again:

it left out the last part ;" It leaves it out the semi-colon and quote.

strSQL = "INSERT INTO [tblTransactions] " & _

    "(TransDate, AccountID, Debit, Credit, Source, GLTransactionNo) " & _

    "SELECT " & _

    strTransDate & ", " & _

    "AccountID, " & _

    "IIf([TotalTrans]<0,[TotalTrans]*-1,0) AS Debit, " & _

     "IIf([TotalTrans]>0,[TotalTrans],0) AS Credit, " & _

     "'Closing Entry' AS Source, " & _

     lngID & " AS GLTransactionNo " & _

     "FROM qryYearEndReBalanceSheetTotals " & _

     "UNION ALL " & _

     "SELECT " & _

     strTransDate & ", " & _

     [Forms]![frmYrEndProcessing]![cboRetainedEarnings] & ", " & _

     "IIf([SumOfProfit]>0,[SumOfProfit],0), " & _

     "IIf([SumOfProfit]<0,[SumOfProfit]*-1,0), " & _

     """Closing Entry"", " & _

     lngID & _

    " FROM qryYearEndBalanceSheetProfit;"
Was this answer helpful?

0 comments No comments