Solved

Event    VBA if then  Statement 1: Statement 2: Statement 3: endsub

Posted on 2013-11-11
15
333 Views
Last Modified: 2013-11-17
Hi

I have several keypress events that I wish to process the same way.  The following code works fine.  I have quite a few similiar statements for the keypress event.
For this reason,  I would like the following code to be on one line. When I combine the the two lines, I get a compile error at runtime.

Private Sub fltrColour_KeyPress(KeyAscii As Integer): If KeyAscii = 32 Then fltrColour = "":   SetFilters:  fltrColour.SetFocus
End Sub

Open in new window

What am I missing here?
0
Comment
Question by:peispud
  • 6
  • 4
  • 4
15 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
it is better for readability to create separate lines for your conditions


Private Sub fltrColour_KeyPress(KeyAscii As Integer)
If KeyAscii = 32 Then fltrColour = "":   SetFilters:  fltrColour.SetFocus
If KeyAscii = 33 Then fltrColour = "":   SetFilters:  fltrColour.SetFocus

End Sub
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Did you remember to put a :  before the End Sub?  I can't test fully without your SetFilters procedure, but this general syntax works without error for me:

Private Sub fltrColour_KeyPress(KeyAscii As Integer): If KeyAscii = 32 Then fltrColour = "":   SetFilters:  fltrColour.SetFocus: End Sub

Open in new window


However I generally prefer multiple lines, for the same reasons Cap1 posted above.
0
 

Author Comment

by:peispud
Comment Utility
This is true, but I have quite a few keypress events that behave exactly the same.   It's simply a matter of preference.  Can this be put on one line?
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
try terminating each IF conditions with ":"  without the quotes

If KeyAscii = 32 Then fltrColour = "":   SetFilters:  fltrColour.SetFocus: If KeyAscii = 32 Then fltrColour = "":   SetFilters:  fltrColour.SetFocus:
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
I think we cross-posted... try my comment at http:#a39639680.  Cap seems to be suggesting the same thing.  If that doesn't work, post the code you tried.
0
 

Author Comment

by:peispud
Comment Utility
I tried these 3 tests.  In the first one,  I substituted the call to "Setfilter".
Case 1 & 2 fail.
Case 3 works



    Private Sub fltrColour_KeyPress(KeyAscii As Integer): If KeyAscii = 32 Then fltrColour = "":    fltrColour = "":    fltrColour.SetFocus: End Sub
    Private Sub fltrColour_KeyPress(KeyAscii As Integer): If KeyAscii = 32 Then fltrColour = "":  If KeyAscii = 32 Then SetFilters:     fltrColour.SetFocus: End Sub
    Private Sub fltrColour_KeyPress(KeyAscii As Integer): If KeyAscii = 32 Then fltrColour = "":   SetFilters:   fltrColour.SetFocus
    End Sub

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
so now you need to use the third format

 Private Sub fltrColour_KeyPress(KeyAscii As Integer): If KeyAscii = 32 Then fltrColour = "":   SetFilters:   fltrColour.SetFocus
    End Sub
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:peispud
Comment Utility
If I have to use the 3rd format,  then so be it.  But that is two lines.

I would prefer one line.   But I can live with two lines of code.

I'll leave this question open just a bit longer..... just in case.
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points
Comment Utility
i don't think you can have it in one line...
0
 

Author Comment

by:peispud
Comment Utility
Thanks everyone for your input.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
Comment Utility
You closed this one too soon.

When I tested this myself, I was getting no error because I had mistakenly left the Key Preview property set to no, so the code simply wasn't running.

Anyhow, if you are looking to get a block conditional statement into one line, use Select Case instead of If-Then-Else

Private Sub fltrColour_KeyPress(KeyAscii As Integer): Select Case KeyAscii: Case 32: fltrColour = "":   SetFilters:   fltrColour.SetFocus:  End Select: End Sub

Open in new window


We had a question posted some time ago by one of our top Experts as a challenge in unusual programming, which wound up focusing on one-line programming, and some of the code is really amazing:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23579815.html

Use the Request Attention button if you want to reopen this - you might get some input from additional Experts if you give it a little more time.
0
 

Author Closing Comment

by:peispud
Comment Utility
So,  it can't be done in one line using the if / then statement.
But it can be don in one line using a select statement.

Both answers are correct.  I appreciate the insight.

Thanks all.
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Yeah - sorry you didn't get more of a response.

Definitely agree that this won't work with an If-Then-Else block, but there are a variety of other conditional statements that WILL work and get you exactly the same results.

<<
 I have quite a few keypress events that behave exactly the same.   It's simply a matter of preference.
>>
I'm personally not a fan of one-line programming like this,  but I can easily see your point of view in wanting such similar procedures to be stacked up neatly in your code modules.

Another suggestion - kind of a hybrid approach (untested).  The idea here is to put your commands for Case 32 (or any other keys) into their own functions, which you can also write on single lines -- and call those functions from your one-line subs.  The benefit of that approach might be that however many similar keypress events you may have, you will only have to change the actual commands for specific keys in one place (ie: fnSpaceBar in this code).

So you get a similar overall appearance, but less maintenance if the code for handling the keys needs to be updated.

Private Sub Textbox1_KeyPress(KeyAscii As Integer): Select Case KeyAscii: Case 32: fnSpaceBar("Textbox1"):  End Select: End Sub
Private Sub Textbox2_KeyPress(KeyAscii As Integer): Select Case KeyAscii: Case 32: fnSpaceBar("Textbox2"):  End Select: End Sub
Private Sub fltrColour_KeyPress(KeyAscii As Integer): Select Case KeyAscii: Case 32: fnSpaceBar("fltrColour"):  End Select: End Sub

Function fnSpaceBar(sCtlName as string): Me.controls(sCtlName)  = "":   SetFilters:   Me.controls(sCtlName).SetFocus: End Function 

Open in new window

0
 

Author Comment

by:peispud
Comment Utility
I have been using the hybrid approach in my coding in the situation that you describe.  

I am well satisfied with the replies including yours.  Thanks.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now