Link to home
Start Free TrialLog in
Avatar of andyb7901
andyb7901

asked on

Ms Access VBA - Function Object Required Error

Hi,
I have a function to import a file. the file is importedperfectly, but when my function ends I get the error;
Run time error '424', Object Required. My code is

ImportFile(txtArtemis.Value, "Artemis") = True

Function ImportFile(strReportFilePath As String, strFileSource As String)
    DoCmd.SetWarnings False
    ProgressTracker (1)
    If strFileSource = "Artemis" Then
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_Artemis", strReportFilePath, True
    ElseIf strFileSource = "Nectar" Then
        DoCmd.TransferText acImportDelim, , "tbl_Nectar", strReportFilePath, True
    End If
    DoCmd.SetWarnings True
End Function

Any help please?
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image
You didn't set a return value anywhere.  How about:

Function ImportFile(strReportFilePath As String, strFileSource As String)
    ImportFile = True
    DoCmd.SetWarnings False
    ProgressTracker (1)
    If strFileSource = "Artemis" Then
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_Artemis", strReportFilePath, True
    ElseIf strFileSource = "Nectar" Then
        DoCmd.TransferText acImportDelim, , "tbl_Nectar", strReportFilePath, True
    Else
        ImportFile = False
    End If
    DoCmd.SetWarnings True
End Function
Avatar of andyb7901
andyb7901

ASKER

I sitll get the same error?
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image
Does the debugger jump to a line in the function, or someplace else?
Avatar of stealthworks
stealthworks
Hi
The way you are calling the function is not right
ImportFile(txtArtemis.Value, "Artemis") = True

just remove the "=True" bit and it should work fine
 - you are calling a function which implies it returns a value. the compiler thinks ImportFile(txtArtemis.Value, "Artemis")  is an object and you are trying to set that object to True!
Avatar of andyb7901
andyb7901

ASKER

If I do not put this in I get a compile error. Expected: =
ASKER CERTIFIED SOLUTION
Avatar of stealthworks
stealthworks
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of stealthworks
stealthworks
Sorry just to explain my reasoning above a bit better
in VBA when you are calling a function that sets a Left hand side variable to the result of a function you need to use the brackets but if just calling a sub-routine or not assigning the result of your call to anything then leave the brackets out. Strictly speaking since your function doesn't need to return anything it could be made just a sub rather than a function.
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image
change your function to read

Function ImportFile(strReportFilePath As String, strFileSource As String) as boolean
ImportFile=false
    DoCmd.SetWarnings False
    ProgressTracker (1)
    If strFileSource = "Artemis" Then
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_Artemis", strReportFilePath, True
    ElseIf strFileSource = "Nectar" Then
        DoCmd.TransferText acImportDelim, , "tbl_Nectar", strReportFilePath, True
    End If
    DoCmd.SetWarnings True

ImportFile=true
End Function

now call the function like this

if ImportFile(txtArtemis.Value, "Artemis") = True then
  'what do you want to do ???
 
end if