Call A function

[aspen]

Hi All I am trying to save all attachments in a table to a folder. So I found this function and is saved as module one. I tested the function in the imidiate window and it works fine ?SaveAttachments("C:\Users\Shiham\Desktop\New"). I am trying to run this function from code behind a button. some thing like this for hours Call SaveAttachments ("C:\Users\Shiham\Desktop\New"). Some body please help how to call it from behind a button. Thank you
his is one of the ways I tried. Private Sub SaveAll_Enter()
Call SaveAttachments("C:\Users\Shiham\Desktop\New")
End Sub


I get the error type mismatch.
Please see the module and help.
Thank you

Public Function SaveAttachments(strPath As String, Optional strPattern As String = "*.*") As Long
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset2
    Dim rsA As DAO.Recordset2
    Dim fld As DAO.Field2
    Dim strFullPath As String
    
    'Get the database, recordset, and attachment field
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblAttachments")
    Set fld = rst("Attachments")
    
    'Navigate through the table
    Do While Not rst.EOF
    
        'Get the recordset for the Attachments field
        Set rsA = fld.Value
        
        'Save all attachments in the field
        Do While Not rsA.EOF
            If rsA("FileName") Like strPattern Then
                strFullPath = strPath & "\" & rsA("FileName")
                
                'Make sure the file does not exist and save
                If Dir(strFullPath) = "" Then
                    rsA("FileData").SaveToFile strFullPath
                End If
                
                'Increment the number of files saved
                SaveAttachments = SaveAttachments + 1
            End If
            
            'Next attachment
            rsA.MoveNext
        Loop
        rsA.Close
        
        'Next record
        rst.MoveNext
    Loop
    
    rst.Close
    dbs.Close
    
    Set fld = Nothing
    Set rsA = Nothing
    Set rst = Nothing
    Set dbs = Nothing
End Function

[June7]

Make it a Sub instead of Function.

Purpose of function is to return a value to calling procedure and must be called as part of an expression, like:

x = SaveAttachments("string here")

A Sub can simply be called and that is what you are doing in your code.

[aspen]

Thanks june. But I am not very familiar with vba. so I don't know how to write it as a sub. When I save the code as a module and test it in the imidiate window
like this. ?SaveAttachments("C:\Users\Shiham\Desktop\New"). It works. Can you please Give an example of changing the function to a sub.
thanks

[aspen]

Ok june I paste it on top and called as you said and it works. Thanks a lot. But Is there really no way of calling it when saved in a module. I am asking because it works in the imidiate window?

[June7]

Just change "Function" to "Sub".

Aren't you calling it in the SaveAll_Enter sub? What is SaveAll?

[aspen]

Thanks
Save all is a button on the form bound to the attachment table. What I was trying to do is to click the button and call the function in the module. module1. This my path SaveAttachments("C:\Users\Shiham\Desktop\New").
do you mean change the "function" part in the module to "sub". If then how would you call the function. let me try and error. Thanks for being there

[aspen]

Thanks june you must have meant to paste it on top of form window. It works smoothly like that when I pressthe button. x = SaveAttachments("C:\Users\Shiham\Desktop\New")
So this thread is solved. thank you very much. have a nice day

[June7]

Call a Function:

x = SaveAttachments("C:\Users\Shiham\Desktop\New")


Call a Sub:

Call SaveAttachments("C:\Users\Shiham\Desktop\New")

[aspen]

Call a Function:

x = SaveAttachments("C:\Users\Shiham\Desktop\New")


Call a Sub:

Call SaveAttachments("C:\Users\Shiham\Desktop\New")

Thanks June. x = SaveAttachments("C:\Users\Shiham\Desktop\New") works. But I just wonder why It works in the imidiate window as a module and doesn't work behind a button when called Call Call SaveAttachments("C:\Users\Shiham\Desktop\New")

[June7]

Are you saying it won't work when it's a Sub? It should. What happens - error message, wrong results, nothing?

[ipisors]

Aspen,

Your last question has been answered twice by June. Just stepping in to see if I can say it differently, and also add an additional thought - not disagreeing with anything to-date.

If you want to have it as a function, then the reasoning for that would be only sensible IF you need to return some value from that function. In the case of this code, there appears to be absolutely zero reason why it is being coded as a function.

Therefore, there are 2 options:

1. Use it as a Function - exactly the way it is. Meaning, in your Button code you'd have to say something like you've discovered:
   
   1. Dim X as Long
   2. X = FunctionName(params)
   3. When structured as a function, you can use the Immediate window to ask Access what the value of that function is: ?FunctionName(params)
   4. Yes, you could also use the Immediate window to run a Sub - you just weren't doing it correctly. You could have this code as a sub, but you don't call a sub in the immediate window using a question mark, you simply run it as:
   5. SubName(any params)
   
   
2. Use it as a Sub - as June recommended. If you do this, you won't need to assign it to a variable (like x=, etc), and you will test it in the Immediate window as explained in 1(5) above.

There are some valid reasons for structuring procedures as Functions which return a value. The main reason is to capture errors and return a value if there is an error. For example, the code you originally found could have been written like this:

Public Function SaveAttachments(strPath As String, Optional strPattern As String = "*.*") As Long
    On Error GoTo Errhandler
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset2
    Dim rsA As DAO.Recordset2
    Dim fld As DAO.Field2
    Dim strFullPath As String
    
    'Get the database, recordset, and attachment field
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblAttachments")
    Set fld = rst("Attachments")
    
    'Navigate through the table
    Do While Not rst.EOF
    
        'Get the recordset for the Attachments field
        Set rsA = fld.Value
        
        'Save all attachments in the field
        Do While Not rsA.EOF
            If rsA("FileName") Like strPattern Then
                strFullPath = strPath & "\" & rsA("FileName")
                
                'Make sure the file does not exist and save
                If Dir(strFullPath) = "" Then
                    rsA("FileData").SaveToFile strFullPath
                End If
                
                'Increment the number of files saved
                SaveAttachments = SaveAttachments + 1
            End If
            
            'Next attachment
            rsA.MoveNext
        Loop
        rsA.Close
        
        'Next record
        rst.MoveNext
    Loop
    
    rst.Close
    dbs.Close
    
    Set fld = Nothing
    Set rsA = Nothing
    Set rst = Nothing
    Set dbs = Nothing
    SaveAttachments=0
exit function
errhandler:
SaveAttachments=1
End Function

Then, you could call the function and see if it erred, by coding like:

If SaveAttachments(params)=1 then
Msgbox "not all attachments were saved - an error has occurred"
Else
Msgbox "all attachments were saved"
End If

There would be better and more informative ways to code it, but hopefully that gives you the idea. PS, sorry for my lack of indentation on that, but I seem to be having trouble using the Tags here.