Runcode macro assist

[chris-uk-lad]

I have a module created that will update a table(TEMP) with the names of folders stored in a file. I want this code to be ran once the access database is opened.

Public Sub NewImages()
    Dim fso As FileSystemObject
    Dim folder As folder
    Dim f As Variant
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim subf As Variant
    Dim f1 As Variant
    Dim strString As String
    Dim subf1 As Variant
    
    
    Set fso = New FileSystemObject
    
    Set folder = fso.GetFolder("c:\Images\")
    
    Set rs = CurrentDb.OpenRecordset("SELECT * from TEMP WHERE SURNAME IS NULL")
    
   ' Set file = f.files
    Set subf = folder.SubFolders
    For Each subf1 In subf 'for all subfolders of the merge folder
        Set f = subf1.Files 'f = files with subfolders
        DoEvents
        Debug.Print subf1.Name
        For Each f1 In f
            rs.AddNew
            rs!Surname = Mid(subf1.Name, InStr(subf1.Name, "_") + 1, Len(subf1.Name))
            rs!PAYREF = Mid(subf1.Name, 1, InStr(subf1.Name, "_") - 1)
            rs!Date = f1.DateCreated
            rs!Path = f1.Path
            rs!FileName = f1.Name
            rs.Update
            Debug.Print f1.Name
        Next
    Next
End Sub

Ive tried Runcode in macros but it wont detect NewImages, any help would be appreciated

 

[pbaldy]

Try changing

Public Sub NewImages()

to

Public Function NewImages()

which will also require a change from

End Sub

to

End Function

 

[chris-uk-lad]

Thanks for the reply,

it accepts the function name, but when i close the tool and open, i get this error:

"the expression you entered has a function name that ViewerDatabase can't find"

ViewerDatabase si the name of my access database.

 

[pbaldy]

Presumably that function resides in a standard module rather than a form/report module, and you should make sure the module itself doesn't have the same name.

 

[chris-uk-lad]

Yes it is in a standard module

 

[pbaldy]

What is the name of the module?

 

[chris-uk-lad]

the module is called NewImages

 

[pbaldy]

You must have missed this:

...you should make sure the module itself doesn't have the same name.

 

[chris-uk-lad]

yeh sorry your right, it works now, thanks for the help