error 3061(to few parameters, expected 3)

  • Thread starter Thread starter Regan via AccessMonster.com
  • Start date Start date
R

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
 
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 " & _
WHERE (((tblCodes.InvCatagory) said:
=#" & [Forms]![frmcreateinv]![txtFromAmer] & "# And (tblTimesheet.EntryDate)
Click to expand...
<=#" & [Forms]![frmcreateinv]![txtToamer] & "#)) " & _
"GROUP BY tblCodes.InvCatagory;"

works fine now :)

--
Regan,
Paeroa
World famous in New Zealand

Message posted via AccessMonster.com
 
Back
Top