VBA - Function not returning expected result

keschuster
keschuster used Ask the Experts™
on
I have the sub btn_ImportOPTCollectors_Click() that calls the function ImportDataTXT_D below.  I'm trying to figure out why the value thisResult in the sub btn_ImportOPTCollectors_Click never pickes up the value returned from ImportDataTXT_D.

Everything processes in the sub btn_ImportOPTCollectors_Click() but the value returned is always false.

Any ideas?
Private Sub btn_ImportOPTCollectors_Click()
     Dim thisResult As Boolean
        thisResult = ImportDataTXT_D("sup_collectors", "opt_collectors_import", "Import OPT Collector TEXT File.")
     Debug.Print "this is the result: "; thisResult
     If thisResult = "True" Then
        MsgBox ("complete")
     Else
        MsgBox ("failed")
     End If
End Sub
 
 
 
Public Function ImportDataTXT_D(destTable As String, specName As String, Optional popMsg As String) As Boolean
 
    '   Supply destination table
    '   Supply Specification
    '   Supply optional popup message
    Dim result As Boolean
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    Dim vrtSelectedItem As Variant
    If popMsg = "" Then
        MsgBox ("Import The Source TEXT File.")
    Else
        MsgBox (popMsg)
    End If
    With fd
        .Title = "Please Select the Source Text File"
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "Text Files", "*.txt", 1
        'Use the Show method to display the File Picker dialog box
        'The user pressed the action button.
        If .Show = -1 Then
                'Check for existance of table and drop if present
                If findTable(destTable) Then
                   DoCmd.RunSQL "DROP TABLE " & destTable
                End If
            'Step through the FileDialogSelectedItems collection.
            For Each vrtSelectedItem In .SelectedItems
                ' Perform Import
                DoCmd.TransferText acImportDelim, specName, destTable, vrtSelectedItem, True
            Next vrtSelectedItem
                r = DCount("*", destTable)
                MsgBox r & " Records Data Imported."
            
            result = "True"
           
        Else
            'The user pressed Cancel.
            MsgBox "Import Canceled"
            result = "False"
        End If
    End With
    'Set the object variable to Nothing.
    Set fd = Nothing
    Debug.Print result
    ImportDataTEXT_D = result
End Function

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
If thisResult = "True" Then
must be
If thisResult = True Then

also:
 result = "True"
resp
 result = "False"

must be
 result = True
resp
 result = False


your variables are boolean, but you try to assign a string, which all evaluate to false.

Author

Commented:
ok I did that. It appears ImportDataTXT_D is setting True/False correctly now.

The calling sub btn_ImportOPTCollectors_Click is still not picking up the/a returned value

I even changed the calling Sub to

Private Sub btn_ImportOPTCollectors_Click()
     If ImportDataTXT_D("sup_collectors", "opt_collectors_import", "Import OPT Collector TEXT File.") = True Then
        MsgBox ("Collector Import Completed")
     Else
        ' no need to post msg - the importDataTXT_D handles it.
     End If
End Sub
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
I don't see anything wrong about the code, otherwise...
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Most Valuable Expert 2014

Commented:
Take the  parens out of the msgbox.
------------------------------------
 MsgBox  "Collector Import Completed"
------------------------------------
You only need them if you are expecting a click other than OK.

Author

Commented:
jimpen - are you suggesting that is what's causing me not to get a return value?
Most Valuable Expert 2014

Commented:
The best way to try and determine what is happening is to use the Debug.Print to track what is going on. Then use the Immediate window <Ctrl>+G to see what it prints.

Repeat as needed throughout the code.

As in:

 
        Else
            'The user pressed Cancel.
            MsgBox "Import Canceled"
            result = False
        End If
 
  Debug.Print "Result from ImportDataTXT_D: " &  result

Open in new window

>>> Public Function ImportDataTXT_D(destTable As String, specName As String, Optional popMsg As String) As Boolean
>>>                         ImportDataTEXT_D = result

Wrong name on the return statement!
- Shows the importance of starting every module with:
 Option Explicit
Most Valuable Expert 2014

Commented:
Mike,

Good catch!!

>> - Shows the importance of starting every module with:
>> Option Explicit

Just as a reference comment. Go into the VBA window --> Tools --> Options and turn on the Require Variable Declaration and it adds it in automatically on new modules.
VBA-Option-Explicit.jpg

Author

Commented:
thanks guys!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial