Sub or Function not defined

[Seth Schrock]

I am trying to compile my VBA code in Access 2010, however, I keep getting this error: Sub or Function not defined. When I click OK, it highlights the following code:

Me.txtFileLoc

'Full code line
If Me.txtFileLoc & "" <> "" And FileExists(Me.txtFileLoc) Then

I know that the spelling is correct because I used the inteliSense prompt to finish the typing for me. I tried renaming the control, same problem (after I renamed the control reference of course). I tried referencing it with the long reference through the form like this:

Forms("frmFileImport").Controls("txtFileLoc")

The error that I get then highlights the .Controls. Further up the form's code module, I have a reference to the same textbox and it doesn't have a problem with it. I discovered it because when I renamed the control and tried to compile the database, it then caught it. Once I fixed the name, it worked and then went back down to this same problem line. I have compacted and repaired the database. I have rebooted my computer. I tried removing my reference to the Microsoft Office xx.x Object Library that I had added and still nothing (I didn't think that it would, but I had to try). I don't know what else to do at this point. Any ideas?

[Seth Schrock]

I think that I just fixed it, but I have no idea how it fixed it. I switched the order of the criteria in line #4 and it works now. So my code is now

If FileExists(Me.txtFileLoc) And Me.txtFileLoc & "" <> "" Then

Can anyone tell me why this made a difference?

[NeoPa]

I can only imagine the issue is somewhere other than the code you've shown Seth. The compiler often gets confused when paired statements are not paired (Like Do / Loop, For / Next, If / End If, etc). It seems to report the issue as something further down the code.

If all you say is fully accurate then I cannot see why it would report it as you've said.

[Seth Schrock]

Here is the full procedure in its current state. The only thing that got changed was the If statement on line 7 (line 20 of the incode line numbers)

Private Sub cmdImport_Click()
10    On Error GoTo Error_Handler

      Dim PF As TSC_PF_Simple
      Dim strFilePath() As String

20    If FileExists(Me.txtFileLoc) And Me.txtFileLoc & "" <> "" Then
30        If Not IsNull(Me.cboScanDate) And Not IsNull(Me.cboBranch) Then
40            Set PF = New TSC_PF_Simple
              
50            PF.Title = "Importing Scan Results..."
60            PF.UpdateTask 0, "Preparing for Import"
70            PF.Show
              
80            DoCmd.SetWarnings False
90            DoCmd.RunSQL "DELETE FROM VulnerabilitiesImport"
100           DoCmd.SetWarnings True
              
110           strFilePath = Split(Me.txtFileLoc, "\")
              
120           PF.UpdateTask 0.05, "Importing File: " & strFilePath(UBound(strFilePath))
130           DoCmd.TransferText acImportDelim, , "VulnerabilitiesImport", Me.txtFileLoc, True
              
              'Run the information setup Files
140           PF.UpdateTask 0.5, "Processing Risk Levels"
150           InsertRiskLevels
              
160           PF.UpdateTask 0.6, "Processing Hosts"
170           InsertHosts Me.cboBranch
              
180           PF.UpdateTask 0.7, "Processing Vulnerabilities"
190           InsertVulnerabilities
              
200           PF.UpdateTask 0.8, "Merging Data"
210           CopyVulnerabilities Me.cboScanDate
              
220           PF.Title = "Import Complete"
230           PF.UpdateTask 1, "Operation Successful"
              
240       Else
250           MsgBox "Please select a scan description and branch"
260       End If
270   Else
280       MsgBox "Either no file has been selected or the selected file does not exist.  Please select an existing file."
290   End If

Exit_Procedure:
300       Set PF = Nothing
310       Exit Sub

Error_Handler:
320       If Err.Number = 3501 Then
330           MsgBox "The file you have selected is open.  Please close it and then try importing it again."
340           Resume Exit_Procedure
350       End If
          
360       If TempVars("RunMode") = "Test" Then
370           Call ErrorMessage(Err.Number, Err.Description, "Form_frmFileImport: cmdImport_Click")
380       Else
390           TSCs_ReportUnexpectedError "cmdImport_Click", "Form_frmFileImport", "Custom info"
400       End If
410       Resume Exit_Procedure
420       Resume
          

End Sub

[jimatqsi]

I think the lack of parenthesis in the complex IF / THEN has contributed to this problem. When in doubt, and especially when not in doubt, use ()

Jim

[Seth Schrock]

I didn't think of using parenthesis. That might explain why I had the issue. Unfortunately, I can't duplicate the error by just switching the order back to how it was since it doesn't produce the error any more. Maybe it just figured out what I was trying to do :)

[NeoPa]

Although it can often make the code easier to read when parentheses (plural) are added, it's rarely necessary. This is because all operators have a level of precedence which usually ensures they are processed in the order you would expect.

Personally, I'd code it as :

With Me
    If .txtFileLoc > "" Then
        If FileExists(.txtFileLoc) Then
            ...
        End If
    End If
End With

But that's not because your way wouldn't be expected to work fine TBF.

[jimatqsi]

I like NeoPa's way, it adds clarity at the cost of nothing.

Jim

[Seth Schrock]

For some reason I hate nesting IF statements. Two I'm okay with, but after that, I try to figure out another way. I guess that is why I do most of mine in a single IF statement when possible. However, I will take the recommendation of an expert over my self-taught practices any day. Thanks for all your help.

[NeoPa]

Normally I'd use And and Or within a single statement but in this case one depends on the other, so it fits better to test one first then only even check the other if it's worth it. When using And and Or it's easier to read and maintain if parentheses are used.