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-18T02:05:45+00:00

Ken,

I'm writing to you to let you know that I wanted to do a test. In an earlier post you refernced me to a link about lookup fields. I had asked about usung AccountID from tblChartOfAccounts in the design grid for qryYearEndReBalanceSheetTotals.

I wanted to use, in a test, AccountID from tblTransactions(which is nested in the qryYearEndBalanceSheetTrans,

I removed AccountID Field from TblChartOfAccounts and dragged in AccountID from the query. The results, like I mentioned were AccountNum, not AccountID, as a result of the Lookup Combobox that was created in the table tblTransactions.

So, I deleted this lookup value in the table and made it a text box. Correct. The form has the lookup value and SELECT Statement to get it from tblChartOfAccounts.

When I amde this change I got a parameter popup saying qryYearEndREBalanceSheetTotals.AccountNum

So I though that was the cause. I later found another query where it caused this popup and delted the AccountNum value it was getting from the query,but as Expression 1. where are you? type of error in the Design grid.

I git excitred, because when I ran the union query it was popping as a parameter, but after the deletion, the union query ran fine.

So Itested the code when I opened form and Clicked okay with fieilds fiiled in and, I wanted that error to leave, but no it came up again as error 3131 Synatax error in FROM Clause, and all I can believe now is that it must be a syntax error.

I cannot believe the **** (excuse me) that this has caused .

Anyways, I thought I found it. I know when you suggested never to use a lookup field from a table. And I changed that.

SO I got rid of AccountNum in the queries, it was there to help me identify an account Number rather than an AccountID, a little more meaningless.

Im back at syntax I think.

Rob
Was this answer helpful?

0 comments No comments
Anonymous

2011-09-18T00:25:10+00:00

Good night ken

Thank you. My last post , not right. I just realized you have it in a function. I did not.
Was this answer helpful?

0 comments No comments
Anonymous

2011-09-18T00:11:20+00:00

Error No. 3265 Item not found in this collection.

I added this to the code. I looked at your word mail merge app. and tried my best follow this eval function.

Dim strQuery As String

Dim qdf As DAO.QueryDef

Dim prm As DAO.Parameter

Added the above to my code on frmYrEndProcessing's   Okay_click event

    I copied part of the code and received the aboce error. Is that the error message we're looking for?

                Else

    Set dbs = CurrentDb

    Set qdf = dbs.QueryDefs(strQuery)

    For Each prm In qdf.parameters

    prm = Eval(prm.Name)

    Next prm

    Set rst = qdf.OpenRecordset

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

        & "[qryYearEndGLTransactions] ;"

        CurrentDb.Execute strSQL, dbFailOnError

strTransDate = "#" & Format(DLookup("FiscalYearEndDate", "tblMyCompanyInfo"), "mm-dd-yyyy") & "#"

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

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

    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
Was this answer helpful?

0 comments No comments