Automate adding record and populating field

bmurrin · January 28th, 2004, 06:50 PM

I would like to add new records to a table (one for each file name) and populate a field with each of the file names in a directory. And I want to do it with the push of a button. I have not figured out how to automate this process and would really appreciate any help you can give me.

Nashville_Bill

Steven · January 28th, 2004, 09:46 PM

Put this code in your button's Click event procedure:
(You might want to declare the variables, which is something I haven't done in this example)

Code:

DoCmd.SetWarnings False
Set fso = CreateObject("Scripting.FileSystemObject")
Set folderObject = fso.GetFolder("C:\Some\Directory")
Set filesObject = folderObject.Files

For Each file In filesObject
DoCmd.RunSQL "INSERT INTO YourTable (YourField) VALUES ('" & file.Name & "');"
Next
DoCmd.SetWarnings True

Of course - you'll need to change the bits
C:\Some\Directory
YourTable
YourField

Steven

I am a loud man with a very large hat. This means I am in charge

bmurrin · January 29th, 2004, 11:57 AM

Steven,

Thanks for the response. I get an compile error message that says the variable file is not defined:

    Dim fso As Object
    Dim folderObject As Object
    Dim filesObject As Object
    DoCmd.SetWarnings False
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folderObject = fso.GetFolder("C:\PICS\Pic\1")
    Set filesObject = folderObject.Files

    For Each file In filesObject

Did I forget to do something?

Nashville_Bill

bmurrin · January 29th, 2004, 12:25 PM

I added a DIM for file and all is well now.

Thanks a lot!

Nashville_Bill