Procedure call problem

[XLEAccessGuru]

Hi folks,

I have a procedure written as shown below. When I try to call it from a button click event on a form, I get "Argument Not Optional". What needs adjusted in the procedure to fix that? Any help appreciated. Urgent issue! THANKS!!!

Public Sub GetFileNames(FolderSpec As String)
'  Returns a list of filenames and filesizes that are present in folderSpec
'  Requires that "Microsoft Scripting Runtime" be referenced by the application
   Dim fso As New Scripting.FileSystemObject
   Dim fd As Scripting.Folder
   Dim f As Scripting.File
   Dim fns As String
   Dim rec As Recordset
   SetWarnings = False
   'set recordset
   
   Set rec = CurrentDb.OpenRecordset("tblImportLog", dbOpenDynaset)
   'get a folder object
   Set fd = fso.GetFolder(FolderSpec)
   'traverse the files collection of the folder
   For Each f In fd.Files
      'return information about the file and assign to string
      fns = "C:\" & fns & f.Name
   With rec
        .AddNew
            !Date = Now()
            !FileName = fns
        .Update
    End With

   Next f
   'assign to function
End Sub

 

[Malcy]

Have you tried stepping through the code line by line to see where the error crops up? Once you have identified the line it generally (?!?) gets a bit easier.
HTH

 

[pbaldy]

How do you call it? I suspect the problem is there, from the sound of it.

 

[XLEAccessGuru]

There's a bunch of other code after it but basically when my form button is clicked, I just have a line that says:

GetFileNames

That's where it breaks - right there - with the "Argument Not Optional". Seems the way the function is written, VBA thinks there is supposed to be an argument, but I can't figure it out.

Any ideas?

 

[pbaldy]

Note the procedure:

Public Sub GetFileNames(FolderSpec As String)

FolderSpec is in this case a required argument. While it could be made optional (or taken out), it seems critical to the purpose of the function, unless you're going to hardcode it.

 

[petehilljnr]

In other words, the line:

GetFileNames

Should be saying something like:

GetFileName "C:\My_Folder_Where_The_Files_Are"

Pete.