SQL Statement Syntax Help (Error 3061)

[Brian]

Hi All, I'm trying to develop a database in access 2003 which in part will
allow users to attach files to an invoice.

Here's how I'm trying to accomplish this. First I set up a FileDialog box
to allow users to browse for their file, then I'm using this upload button
to copy the file over to a location and rename it.

The part that I'm having trouble with is storing the new file location and
associating it with the invoice. Since there might be more than one file per
invoice, I've created a table called Invoice_Attachments, which only
contains the fields for the invoice number and attachment location.

I thought it would be pretty easy to use an update sql statement to record
the invoice number and location in the Invoice_Attachments table, but so far
I've been running into several errors- which I'm sure is due to my lack of
expertise with SQL statements.

Everything is working with the exception of the last bit of code- the
strSQL part, and the latest error is 3061 too few arguments.

If any one has the time to look over this code to tell me what I'm missing
I'd greatly appreciate it.

THANKS!

___________________________________________



Private Sub UploadFile_Click()



strSrcFile = Me.FileList.RowSource

For i = 1 To Len(strSrcFile)

If Asc(Mid(strSrcFile, i, 1)) <> 0 Then

strTempName = strTempName + Mid(strSrcFile, i, 1)

End If

Next i

strSrcFile = strTempName

' if cancel was pressed exit sub

If Len(strSrcFile) < 1 Then

Exit Sub

End If



' get file extension including dot

strFileExt = Right(strSrcFile, Len(strSrcFile) -
InStrRev(strSrcFile, ".", , vbTextCompare) + 1)

Trim (strFileExt)

' append together full dest file name

Dim strDestFile As String

strDestFile = "c:\" & Me.Invoice & "-" & i & strFileExt

MsgBox "DestFile = " & strDestFile



' FileCopy "strsrc", "strdest"

FileCopy strSrcFile, strDestFile

Dim strDestLocation As String



strDestLocation = "file:///c:/" & Me.Invoice & "-" & i & strFileExt



Dim MyDB As Database

Dim strSQL As String



strSQL = "UPDATE Invoice_Attachments " & _

"SET Invoice_Attachments.attachments = """" & strDestFile & """""
& _

"WHERE Invoice_Attachments.Invoice = " & Me.Invoice_Number2



Set MyDB = CurrentDb()

MyDB.Execute strSQL, dbFailOnError

 

[Brian]

It looks like you have too many quotes characters, but shouldn't you be
inserting a new row into the table rather than updating rows?

strSQL = "INSERT INTO Invoice_Attachments " & _
"(invoice, attachments) " & _
"VALUES(" & Me.Invoice_Number2 & _
",""" & strDestFile & """)"

Ken Sheridan
Stafford, England

Problem solved. Many thanks!

-Brian