Re: Error 2471

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance



"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.TransferSpreadsheet 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
.

Relevant Pages

  • Re: Error 2471
    ... JIM wrote: ... appending to a production table there is a way to check if Filename ... There must exist a table or query named "WOProduction". ...
    (microsoft.public.access.formscoding)
  • RE: renaming a file
    ... > The numbers in the filename representing the date and then a ... The letters representing various filenames. ... > and appending a .txt to the end to make the format a little more ...
    (perl.beginners)
  • Re: random numbers in filenames
    ... is there anyway to prevent appending of a character on the excel filename? ... For some reason, Outlook ... > So it figures that it can't use that name again and appends a character to ...
    (microsoft.public.excel)
  • Re: Parse an xml file
    ... Of course I'm appending the filename at the end of the command, ... I an ksh script I need to get the value of a specified xml tag into a ...
    (comp.unix.shell)
  • CFile open question
    ... working with the exception of 1 problem. ... How can I force the file to open in the directory that the exe is sitting ... Appending the path to the filename isn't an option for this project as ...
    (microsoft.public.windowsce.embedded.vc)