CODEWITHSUNDEEP
vbams-access
New2Programming
New2Programming

Reputation: 383

MS Access if statement on click event

I am using Ms Access forms and I have created an on click event that locates a folder location but now I want to locate the folder location based on different criteria but when I add the if statement it expects a sub,function or property. Below is some demo code. I really hope someone can explain what is missing? 

    Private Sub Open_Email_Click()
    Dim stAppName As String
    Dim stAppNameA As String
    Dim stAppNameB As String
    stAppName  = "C:\Windows\explorer.exe C:\DEMO\TEST\" & Me.Office & " DEMO\B " & Me.BC & " " & Me.UC & "\"
    stAppNameA = "C:\Windows\explorer.exe C:\DEMO\TEST\" & Me.Office & " DEMO\A\B " & Me.BC & " " & Me.UC & "\"
    stAppNameB = "C:\Windows\explorer.exe C:\DEMO\TEST\" & Me.Office & " DEMO\B\B " & Me.BC & " " & Me.UC & "\"

    If (Me.BC = "60") And Me.UC Like "REF123*" Then stAppNameA
    ElseIf (Me.BC = "60") And Not Me.UC Like "REF123*" Then stAppNameB
    Else: stAppName
    End If

Call Shell(stAppName, 1) 
End Sub

Upvotes: 0

Views: 322

Answers (2)

Lee Mac
Lee Mac

Reputation: 16025

I think the logic of your function could be reduced to the following, which may be more readable with fewer repeating expressions:

Private Sub Open_Email_Click()
    Dim strTmp As String

    If Me.BC = "60" Then
        If Me.UC Like "REF123*" Then
            strTmp = " DEMO\A\B "
        Else
            strTmp = " DEMO\B\B "
        End If
    Else
        strTmp = " DEMO\B "
    End If
    Call Shell("C:\Windows\explorer.exe C:\DEMO\TEST\" & Me.Office & strTmp & Me.BC & " " & Me.UC & "\", 1)
End Sub

Alternatively, using a Select Case statement:

Private Sub Open_Email_Click()
    Dim strTmp As String

    Select Case True
        Case Me.BC <> "60"
            strTmp = " DEMO\B "
        Case Me.UC Like "REF123*"
            strTmp = " DEMO\A\B "
        Case Else
            strTmp = " DEMO\B\B "
    End Select
    Call Shell("C:\Windows\explorer.exe C:\DEMO\TEST\" & Me.Office & strTmp & Me.BC & " " & Me.UC & "\", 1)
End Sub

To test the resulting path, change:

Call Shell("C:\Windows\explorer.exe C:\DEMO\TEST\" & Me.Office & strTmp & Me.BC & " " & Me.UC & "\", 1)

To:

Debug.Print "C:\Windows\explorer.exe C:\DEMO\TEST\" & Me.Office & strTmp & Me.BC & " " & Me.UC & "\"

Upvotes: 3

ArcherBird
ArcherBird

Reputation: 2134

I think your If block is just a bit messy in terms of where you have newlines, and continuation characters (:). Try reformatting your code like this:

    If (Me.BC = "60") And Me.UC Like "REF123*" Then 
        stAppName =stAppNameA
    ElseIf (Me.BC = "60") And Not Me.UC Like "REF123*" Then 
        stAppName = stAppNameB
    Else 
        stAppName =stAppName
    End If

    Call Shell(stAppName, 1)

Upvotes: 1

Related Questions