MS Access if statement on click event
0
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:Windowsexplorer.exe C:DEMOTEST" & Me.Office & " DEMOB " & Me.BC & " " & Me.UC & ""
stAppNameA = "C:Windowsexplorer.exe C:DEMOTEST" & Me.Office & " DEMOAB " & Me.BC & " " & Me.UC & ""
stAppNameB = "C:Windowsexplorer.exe C:DEMOTEST" & Me.Office & " DEMOBB " & 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
vba ms-access
asked Nov 19 '18 at 15:31
New2ProgrammingNew2Programming
110113
add a comment |
0
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:Windowsexplorer.exe C:DEMOTEST" & Me.Office & " DEMOB " & Me.BC & " " & Me.UC & ""
stAppNameA = "C:Windowsexplorer.exe C:DEMOTEST" & Me.Office & " DEMOAB " & Me.BC & " " & Me.UC & ""
stAppNameB = "C:Windowsexplorer.exe C:DEMOTEST" & Me.Office & " DEMOBB " & 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
vba ms-access
asked Nov 19 '18 at 15:31
New2ProgrammingNew2Programming
110113
add a comment |
0
0
0
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:Windowsexplorer.exe C:DEMOTEST" & Me.Office & " DEMOB " & Me.BC & " " & Me.UC & ""
stAppNameA = "C:Windowsexplorer.exe C:DEMOTEST" & Me.Office & " DEMOAB " & Me.BC & " " & Me.UC & ""
stAppNameB = "C:Windowsexplorer.exe C:DEMOTEST" & Me.Office & " DEMOBB " & 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
vba ms-access
asked Nov 19 '18 at 15:31
New2ProgrammingNew2Programming
110113
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:Windowsexplorer.exe C:DEMOTEST" & Me.Office & " DEMOB " & Me.BC & " " & Me.UC & ""
stAppNameA = "C:Windowsexplorer.exe C:DEMOTEST" & Me.Office & " DEMOAB " & Me.BC & " " & Me.UC & ""
stAppNameB = "C:Windowsexplorer.exe C:DEMOTEST" & Me.Office & " DEMOBB " & 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
vba ms-access
vba ms-access
asked Nov 19 '18 at 15:31
New2ProgrammingNew2Programming
110113
asked Nov 19 '18 at 15:31
New2ProgrammingNew2Programming
110113
asked Nov 19 '18 at 15:31
New2ProgrammingNew2Programming
110113
asked Nov 19 '18 at 15:31
New2ProgrammingNew2Programming
110113
asked Nov 19 '18 at 15:31
New2ProgrammingNew2Programming
110113
110113
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
3
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 = " DEMOAB "
Else
strTmp = " DEMOBB "
End If
Else
strTmp = " DEMOB "
End If
Call Shell("C:Windowsexplorer.exe C:DEMOTEST" & 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 = " DEMOB "
Case Me.UC Like "REF123*"
strTmp = " DEMOAB "
Case Else
strTmp = " DEMOBB "
End Select
Call Shell("C:Windowsexplorer.exe C:DEMOTEST" & Me.Office & strTmp & Me.BC & " " & Me.UC & "", 1)
End Sub
To test the resulting path, change:
Call Shell("C:Windowsexplorer.exe C:DEMOTEST" & Me.Office & strTmp & Me.BC & " " & Me.UC & "", 1)
To:
Debug.Print "C:Windowsexplorer.exe C:DEMOTEST" & Me.Office & strTmp & Me.BC & " " & Me.UC & ""
answered Nov 19 '18 at 15:56
Lee MacLee Mac
4,17931441
Lee this is a much cleaner way, I have tried to use this but it is not opening to the file path to the new location only to DEMOBB. also in your code you have BD which I think is a typo where it should be BC.
– New2Programming
Nov 20 '18 at 9:57
@New2Programming Good spot! I have now corrected theBDtypo.
– Lee Mac
Nov 20 '18 at 13:25
@New2Programming With the typo fixed, is the code now operating successfully?
– Lee Mac
Nov 21 '18 at 13:25
I am not getting an error now but the folder location is not opening to the correct location and only to my documents instead of the actual folder, I have location names but it is like something is missing. Can I print out the location name in the console to see what location the event is trying to find?
– New2Programming
Nov 21 '18 at 14:39
1
Thanks Lee that works perfectly and resolves the issue :)
– New2Programming
Nov 22 '18 at 12:47
|
show 1 more comment
1
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)
answered Nov 19 '18 at 15:34
ArcherBirdArcherBird
783219
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53377882%2fms-access-if-statement-on-click-event%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
3
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 = " DEMOAB "
Else
strTmp = " DEMOBB "
End If
Else
strTmp = " DEMOB "
End If
Call Shell("C:Windowsexplorer.exe C:DEMOTEST" & 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 = " DEMOB "
Case Me.UC Like "REF123*"
strTmp = " DEMOAB "
Case Else
strTmp = " DEMOBB "
End Select
Call Shell("C:Windowsexplorer.exe C:DEMOTEST" & Me.Office & strTmp & Me.BC & " " & Me.UC & "", 1)
End Sub
To test the resulting path, change:
Call Shell("C:Windowsexplorer.exe C:DEMOTEST" & Me.Office & strTmp & Me.BC & " " & Me.UC & "", 1)
To:
Debug.Print "C:Windowsexplorer.exe C:DEMOTEST" & Me.Office & strTmp & Me.BC & " " & Me.UC & ""
answered Nov 19 '18 at 15:56
Lee MacLee Mac
4,17931441
Lee this is a much cleaner way, I have tried to use this but it is not opening to the file path to the new location only to DEMOBB. also in your code you have BD which I think is a typo where it should be BC.
– New2Programming
Nov 20 '18 at 9:57
@New2Programming Good spot! I have now corrected theBDtypo.
– Lee Mac
Nov 20 '18 at 13:25
@New2Programming With the typo fixed, is the code now operating successfully?
– Lee Mac
Nov 21 '18 at 13:25
I am not getting an error now but the folder location is not opening to the correct location and only to my documents instead of the actual folder, I have location names but it is like something is missing. Can I print out the location name in the console to see what location the event is trying to find?
– New2Programming
Nov 21 '18 at 14:39
1
Thanks Lee that works perfectly and resolves the issue :)
– New2Programming
Nov 22 '18 at 12:47
|
show 1 more comment
3
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 = " DEMOAB "
Else
strTmp = " DEMOBB "
End If
Else
strTmp = " DEMOB "
End If
Call Shell("C:Windowsexplorer.exe C:DEMOTEST" & 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 = " DEMOB "
Case Me.UC Like "REF123*"
strTmp = " DEMOAB "
Case Else
strTmp = " DEMOBB "
End Select
Call Shell("C:Windowsexplorer.exe C:DEMOTEST" & Me.Office & strTmp & Me.BC & " " & Me.UC & "", 1)
End Sub
To test the resulting path, change:
Call Shell("C:Windowsexplorer.exe C:DEMOTEST" & Me.Office & strTmp & Me.BC & " " & Me.UC & "", 1)
To:
Debug.Print "C:Windowsexplorer.exe C:DEMOTEST" & Me.Office & strTmp & Me.BC & " " & Me.UC & ""
answered Nov 19 '18 at 15:56
Lee MacLee Mac
4,17931441
Lee this is a much cleaner way, I have tried to use this but it is not opening to the file path to the new location only to DEMOBB. also in your code you have BD which I think is a typo where it should be BC.
– New2Programming
Nov 20 '18 at 9:57
@New2Programming Good spot! I have now corrected theBDtypo.
– Lee Mac
Nov 20 '18 at 13:25
@New2Programming With the typo fixed, is the code now operating successfully?
– Lee Mac
Nov 21 '18 at 13:25
I am not getting an error now but the folder location is not opening to the correct location and only to my documents instead of the actual folder, I have location names but it is like something is missing. Can I print out the location name in the console to see what location the event is trying to find?
– New2Programming
Nov 21 '18 at 14:39
1
Thanks Lee that works perfectly and resolves the issue :)
– New2Programming
Nov 22 '18 at 12:47
|
show 1 more comment
3
3
3
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 = " DEMOAB "
Else
strTmp = " DEMOBB "
End If
Else
strTmp = " DEMOB "
End If
Call Shell("C:Windowsexplorer.exe C:DEMOTEST" & 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 = " DEMOB "
Case Me.UC Like "REF123*"
strTmp = " DEMOAB "
Case Else
strTmp = " DEMOBB "
End Select
Call Shell("C:Windowsexplorer.exe C:DEMOTEST" & Me.Office & strTmp & Me.BC & " " & Me.UC & "", 1)
End Sub
To test the resulting path, change:
Call Shell("C:Windowsexplorer.exe C:DEMOTEST" & Me.Office & strTmp & Me.BC & " " & Me.UC & "", 1)
To:
Debug.Print "C:Windowsexplorer.exe C:DEMOTEST" & Me.Office & strTmp & Me.BC & " " & Me.UC & ""
answered Nov 19 '18 at 15:56
Lee MacLee Mac
4,17931441
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 = " DEMOAB "
Else
strTmp = " DEMOBB "
End If
Else
strTmp = " DEMOB "
End If
Call Shell("C:Windowsexplorer.exe C:DEMOTEST" & 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 = " DEMOB "
Case Me.UC Like "REF123*"
strTmp = " DEMOAB "
Case Else
strTmp = " DEMOBB "
End Select
Call Shell("C:Windowsexplorer.exe C:DEMOTEST" & Me.Office & strTmp & Me.BC & " " & Me.UC & "", 1)
End Sub
To test the resulting path, change:
Call Shell("C:Windowsexplorer.exe C:DEMOTEST" & Me.Office & strTmp & Me.BC & " " & Me.UC & "", 1)
To:
Debug.Print "C:Windowsexplorer.exe C:DEMOTEST" & Me.Office & strTmp & Me.BC & " " & Me.UC & ""
answered Nov 19 '18 at 15:56
Lee MacLee Mac
4,17931441
edited Nov 21 '18 at 17:21
answered Nov 19 '18 at 15:56
Lee MacLee Mac
4,17931441
answered Nov 19 '18 at 15:56
Lee MacLee Mac
4,17931441
answered Nov 19 '18 at 15:56
Lee MacLee Mac
4,17931441
4,17931441
Lee this is a much cleaner way, I have tried to use this but it is not opening to the file path to the new location only to DEMOBB. also in your code you have BD which I think is a typo where it should be BC.
– New2Programming
Nov 20 '18 at 9:57
@New2Programming Good spot! I have now corrected theBDtypo.
– Lee Mac
Nov 20 '18 at 13:25
@New2Programming With the typo fixed, is the code now operating successfully?
– Lee Mac
Nov 21 '18 at 13:25
I am not getting an error now but the folder location is not opening to the correct location and only to my documents instead of the actual folder, I have location names but it is like something is missing. Can I print out the location name in the console to see what location the event is trying to find?
– New2Programming
Nov 21 '18 at 14:39
1
Thanks Lee that works perfectly and resolves the issue :)
– New2Programming
Nov 22 '18 at 12:47
|
show 1 more comment
Lee this is a much cleaner way, I have tried to use this but it is not opening to the file path to the new location only to DEMOBB. also in your code you have BD which I think is a typo where it should be BC.
– New2Programming
Nov 20 '18 at 9:57
@New2Programming Good spot! I have now corrected theBDtypo.
– Lee Mac
Nov 20 '18 at 13:25
@New2Programming With the typo fixed, is the code now operating successfully?
– Lee Mac
Nov 21 '18 at 13:25
I am not getting an error now but the folder location is not opening to the correct location and only to my documents instead of the actual folder, I have location names but it is like something is missing. Can I print out the location name in the console to see what location the event is trying to find?
– New2Programming
Nov 21 '18 at 14:39
1
Thanks Lee that works perfectly and resolves the issue :)
– New2Programming
Nov 22 '18 at 12:47
Lee this is a much cleaner way, I have tried to use this but it is not opening to the file path to the new location only to DEMOBB. also in your code you have BD which I think is a typo where it should be BC.
– New2Programming
Nov 20 '18 at 9:57
Lee this is a much cleaner way, I have tried to use this but it is not opening to the file path to the new location only to DEMOBB. also in your code you have BD which I think is a typo where it should be BC.
– New2Programming
Nov 20 '18 at 9:57
@New2Programming Good spot! I have now corrected the
BD typo.– Lee Mac
Nov 20 '18 at 13:25
@New2Programming Good spot! I have now corrected the
BD typo.– Lee Mac
Nov 20 '18 at 13:25
@New2Programming With the typo fixed, is the code now operating successfully?
– Lee Mac
Nov 21 '18 at 13:25
@New2Programming With the typo fixed, is the code now operating successfully?
– Lee Mac
Nov 21 '18 at 13:25
I am not getting an error now but the folder location is not opening to the correct location and only to my documents instead of the actual folder, I have location names but it is like something is missing. Can I print out the location name in the console to see what location the event is trying to find?
– New2Programming
Nov 21 '18 at 14:39
I am not getting an error now but the folder location is not opening to the correct location and only to my documents instead of the actual folder, I have location names but it is like something is missing. Can I print out the location name in the console to see what location the event is trying to find?
– New2Programming
Nov 21 '18 at 14:39
1
1
Thanks Lee that works perfectly and resolves the issue :)
– New2Programming
Nov 22 '18 at 12:47
Thanks Lee that works perfectly and resolves the issue :)
– New2Programming
Nov 22 '18 at 12:47
|
show 1 more comment
1
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)
answered Nov 19 '18 at 15:34
ArcherBirdArcherBird
783219
add a comment |
1
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)
answered Nov 19 '18 at 15:34
ArcherBirdArcherBird
783219
add a comment |
1
1
1
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)
answered Nov 19 '18 at 15:34
ArcherBirdArcherBird
783219
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)
answered Nov 19 '18 at 15:34
ArcherBirdArcherBird
783219
answered Nov 19 '18 at 15:34
ArcherBirdArcherBird
783219
answered Nov 19 '18 at 15:34
ArcherBirdArcherBird
783219
answered Nov 19 '18 at 15:34
ArcherBirdArcherBird
783219
783219
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53377882%2fms-access-if-statement-on-click-event%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
