error 3061(to few parameters, expected 3)

[Regan via AccessMonster.com]

Hi, don't know why this isn't working, i copied the SQL from the immediate
window and chucked it in SQL view and it runs fine. Just don't know why it
isn't working in VBA

Any help would be appreiacted( i know i can't spell)

Private Sub CmdAutoPop_Click()

On Error GoTo ProcErr

Dim IntInvoiceID As Integer
Dim StrSQLI As String

IntInvoiceID = Forms!frmcreateInv!txtInvoiceID

StrSQLI = "INSERT INTO tblinvoicedetails(InvoiceID, InvCatagory, amount) " &
_
"SELECT """ & IntInvoiceID & """ AS invoiceID, tblCodes.InvCatagory,
Format(Sum(tbltimesheet.units/10*IIf(tblcodes.chargetype=""code""
,tblcodes.chargerate,tblemployconf.chargeoutrate)),'Currency') AS
TotalPerCata " & _
"FROM tblCodes INNER JOIN (tblEmployConf INNER JOIN tblTimesheet ON
tblEmployConf.EmployConfID = tblTimesheet.EmployconfID) ON tblCodes.CodeID =
tblTimesheet.CodeID " & _
"WHERE (((tblCodes.InvCatagory) <> """") And ((tblTimesheet.FileID)
= [Forms]![frmcreateinv]![txtFileID]) And ((tblTimesheet.EntryDate) >= [Forms]
![frmcreateinv]![txtDateFrom] And (tblTimesheet.EntryDate) <= [Forms]!
[frmcreateinv]![txtDateTo])) " & _
"GROUP BY tblCodes.InvCatagory;"

Debug.Print StrSQLI

CurrentDb.Execute StrSQLI, dbFailOnError

DoCmd.RunCommand acCmdRefresh

ProcExit:
Exit Sub

ProcErr:
MsgBox "Error " & Err.Number & "(" & Err.Description & ")"
Resume ProcExit

 

[Regan via AccessMonster.com]

Dir, answered my own question

I didn't put '&' around the references to the form. 'hitting head on own
desk, getting keyboard marks on face.'

StrSQLI = "INSERT INTO tblinvoicedetails(InvoiceID, InvCatagory, amount) " &
_
"SELECT """ & IntInvoiceID & """ AS InvoiceID, tblCodes.InvCatagory,
Format(Sum(tbltimesheet.units/10*IIf(tblcodes.chargetype=""code"",tblcodes.
chargerate,tblemployconf.chargeoutrate)),'Currency') AS TotalPerCata " & _
"FROM tblCodes INNER JOIN (tblEmployConf INNER JOIN tblTimesheet ON
tblEmployConf.EmployConfID = tblTimesheet.EmployconfID) ON tblCodes.CodeID =
tblTimesheet.CodeID " & _

=#" & [Forms]![frmcreateinv]![txtFromAmer] & "# And (tblTimesheet.EntryDate)

<=#" & [Forms]![frmcreateinv]![txtToamer] & "#)) " & _
"GROUP BY tblCodes.InvCatagory;"

works fine now

--
Regan,
Paeroa
World famous in New Zealand

Message posted via AccessMonster.com