Solved

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

Posted on 2013-11-11
15
356 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

810 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