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-13T23:11:02+00:00

HI Ken,

My mistake it is incrementing. I made a backup of the database(Test) I copied and pasted the code to the backup and the backup worked. The dates were different in both databases.

I made them the same. I couldn't undestand why it incremented on the backup database and not on the original.

I copied and pasted the code from the backup to the original and it does increment. I'm sorry, I just don't understand, because I'm copying the same code. I looked at it in the original db and compared it to the copydb before I copied it over and it looked identical.

My eyes, may be decieving me.

It's okay as far as the incrementing goes.   Thank you.

I still get Error 3131 Syntax in the FROM Clause.

Here, this is what I just copied over and I am just going to work with 1 test database.

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
Anonymous

2011-09-13T22:44:08+00:00

But GLTransactionNo 86 is showing in the immediate window which is good, but should increment by 1 per lngID Dmax function.

You are assigning a value to the lngID variable with:

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

before building the SQL statement, so its value will be the highest in tblGLTransactions and will not change when you execute the SQL statement. If you want it to increment for each row inserted into tblTransactions, then you'd need to insert the rows one by one in a loop, and increment the value of the lngID variable each time. For this you could establish a recordset based on the UNION ALL operation and step through it row by row, building and executing an INSERT INTO statement at each iteration of the loop, getting the values for AccountID, Debit and Credit from the members of recordset's Fields collection, the value for TransDate by looking it up from tblMyCompanyInfo, the value for Source as the constant 'Closing Entry' and the value for GLTransactionNo from lngID incemented by 1 each time.
Was this answer helpful?

0 comments No comments
Anonymous

2011-09-13T21:53:51+00:00

John,

After I inserted the single quotes and removed the " after Closing Entry, I got an error message 3131 syntax error in FROM clause.( I posted the latest str SQL below)

I have the immedite window working again and the the values for TransDate is correct, the AccountID is correct as 36 and the 'Closing Entry' shows with single quotes and later in the code "Closing Entry" with double quotes. It looks right.

But GLTransactionNo 86 is showing in the immediate window which is good, but should increment by 1 per lngID Dmax function. But it does not. It does show up as 86 twice on the same line in the immediate window as per the code.

I ran the code 3 times and the GLTransactionNo is always 86. It should be 88. So, there must be something wrong. When ken gave me the code I didn't eliminate the first strSQL INSERT INTO tblGLTransactions. I think I was right in not removing that strSQL.

I copied the code and here it is updated.

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

Thanks John and Ken if you read this. In all fairness I offered to compensate Ken and I am offering to compensate you John as well. Ken didn't want anything. He's retired and volunteerng on the site. I wish to offer you compensation through Paypal as well. I appreciate the help, John. If you desire let me know and put a price tag on your help. I'll try my best to satify it. It's only fair. I'm grateful to Both You and Ken.

Thanks John,

Rob
Was this answer helpful?

0 comments No comments