Solved

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

Posted on 2013-11-11
15
368 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39639654
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
ID: 39639680
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
ID: 39639683
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39639691
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
ID: 39639698
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
ID: 39639723
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39639741
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
 

Author Comment

by:peispud
ID: 39639772
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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 39639779
i don't think you can have it in one line...
0
 

Author Comment

by:peispud
ID: 39640059
Thanks everyone for your input.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
ID: 39640489
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
ID: 39649460
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
ID: 39649923
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
ID: 39654480
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

713 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