Re: Error 2471



"Dirk Goldgar" wrote:

In news:A9951DF5-8FFD-41D2-A624-D95787BFDC81@xxxxxxxxxxxxx,
JIM <JIM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
"Dirk Goldgar" wrote:

Are you absolutely sure that the table WOProduction contains a field
named "FileName"? Is it possible you've misspelled the field name?

I'm not sure why you're wrapping extra parentheses around your
function
arguments, but I don't see that it has any harmful effect.

I'm not sure how the DLookup works. I pasted directly from an
answer in discussion groups. It's my understanding that before
appending to a production table there is a way to check if Filename
has already been added and to skip in that case. I have a primary
key field that could be checked but not sure how to do that.

The code looks reasonable, for that purpose, but clearly there's
something wrong. In order for it to work, the following things must be
true:

1. There must exist a table or query named "WOProduction". Is there
such a table?

2. That table (or query) must contain a text field named "FileName" and
a date field named "ImportDate". Does it contain these fields? They
must be spelled exactly as shown, except that capitalization isn't
important; for example, "filename" and "Filename" are equivalent to
"FileName", but "File Name" is not.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


Here is my new code to clear it up:
Private Sub LoadNewWorkOrders_Click()
Dim strPath As String
Dim strFileName As String

strPath = "C:\Documents and Settings\CMC\New Work Orders\"
'Path to directory where new w/os are
strFileName = Dir(strPath & "*.xls")
'Dir function returns only Excel files

Do While Len(strFileName) <> 0
'After Dir function returns all .xl files, it returns a zero length string ""
If IsNull(DLookup("[Filename]", "tblImportedFiles", "[FileName] = '" &
strFileName & "'")) Then 'If file is not found then enter loop
DoCmd.TransferSpread*** acLink, , "WOTempTable", strPath &
strFileName, True 'Link Excel file to Temptable

CurrentDb.Execute ("AppendNewWOs"), dbFailOnError
'Execute AppendQuery
that reads from TempTable and Appends the records to production table

DoCmd.DeleteObject acTable, "WOTemptable"
'Delete link, but not the file

CurrentDb.Execute ("INSERT INTO tblImportedFiles(
[FileName],[ImportDate] ) Values ('" & strFileName & "', #" & Date & "#);"),
dbFailOnError
'Insert file into work orders
End If

strFileName = Dir()
Loop

End Sub

I have a table-tblimportedfiles and it has two fields: FileName and
ImportDate. and it has gotten past to error 2471 and now hangs up on
CurrentDb.Execute ("AppendNewWOs"), dbFailOnError
and gives message "Run-time error '3061' Too few parameters, Expected 1.
Something is wrong with my append query.
Any help is appreciated,
JIM
.