Select Case Statement issue:

[gold007eye]

I seem to be stumped and am hoping this is something easy to do. In the code below what I am trying to do is if the users clicks the "Yes" button; then the 'Case vbYes' (See red code) statement will continue processing the event procedure from the 'Else:' (see blue code) statement below it.

My problem is I don't know how to get the code to look to the Else: statement. I have tried "GoTo Else:" & "GoSub Else:" with no luck.

Is there a simple way to do this or something under the vbYes that can be entered like "Continue the code where it left off"?

I just don't want to have to have the same code in twice on the Event Procedure.

Private Sub Search_Results_DblClick(Cancel As Integer)
If IsNull([Search Results]) Then
    'Do Nothing
ElseIf [Search Results].Column(2) = "N" Then
    Select Case MsgBox("The City [" & [Search Results].Column(0) & "] you have selected is NOT an Acceptable USPS City Name." _
    & vbCrLf & vbCrLf & "Please Note:  If you continue to use this City Name the letter could possibly be returned as Non-Deliverable!" _
    & vbCrLf & vbCrLf & "Are you sure you want to use this City Name?", vbYesNo + vbCritical, "Not an Acceptable USPS City Name...")
        
        [COLOR="Red"]Case vbYes: 'Use this City Name anyways.
            'Go to the "ELSE:" Statement and continue processing code.[/COLOR]
        Case vbNo: 'Don't use this City Name; select a different one.
            MsgBox "Please Select a different City Name.", vbInformation, "Select a Different City Name..."
            Exit Sub
    End Select
[COLOR="Blue"]Else:[/COLOR]
    If CurrentProject.AllForms("Provider Letters").IsLoaded Then
        Forms![Provider Letters]![City] = [Search Results].Column(0)
        Forms![Provider Letters]![State] = [Search Results].Column(1)
    Else:
        'Do Nothing for now...
    End If
    DoCmd.Close acForm, "Zip Code Search - Select Record"
End If
End Sub

 

[Brianwarnock]

You GoTo labels ie label1: whether youcan put one in the middle of an If block I don't know and I haven't examined any other feature of your code.

Brian

 

[boblarson]

Get rid of the colon next to ELSE. In your context, you need ELSE not ELSE:.

 

[gold007eye]

What makes the difference between "Else" and "Else:"?

 

[gold007eye]

I tried the GoTo, but it isn't executing the code from the Label1: line forward. It is doing nothing; almost as if I had used "Exit Sub"

 

[gold007eye]

I got it figured out. I was putting the GoTo Label in the wrong place. Here is the final code that is doing what I needed it to now. Thanks for the help.

Private Sub Search_Results_DblClick(Cancel As Integer)
If IsNull([Search Results]) Then
    'Do Nothing
ElseIf [Search Results].Column(2) = "N" Then
    Select Case MsgBox("The City [" & [Search Results].Column(0) & "] you have selected is NOT an Acceptable USPS City Name." _
    & vbCrLf & vbCrLf & "Please Note:  If you continue to use this City Name the letter could possibly be returned as Non-Deliverable!" _
    & vbCrLf & vbCrLf & "Are you sure you want to use this City Name?", vbYesNo + vbCritical, "Not an Acceptable USPS City Name...")
        
        Case vbYes: 'Use this City Name anyways.
            GoTo IgnoreWarning
            
        Case vbNo: 'Don't use this City Name; select a different one.
            MsgBox "Please Select a different City Name.", vbInformation, "Select a Different City Name..."
            Exit Sub
    End Select

Else
IgnoreWarning:
    If CurrentProject.AllForms("Provider Letters").IsLoaded Then
        Forms![Provider Letters]![City] = [Search Results].Column(0)
        Forms![Provider Letters]![State] = [Search Results].Column(1)
    Else:
        'Do Nothing for now...
    End If
    DoCmd.Close acForm, "Zip Code Search - Select Record"
End If
End Sub

 

[Brianwarnock]

A quick reas of Help suggests that you would code

Label1: Else

Did you do that?

Brian

 

[stopher]

Afaik a colon denotes a label (like what Brian is referring to). In theory you can the "goto" a label. However, in practice this is considered bad practice and will get you tied up in knots. Best to avoid Goto and labels (except for error handling).

If you have code appearing twice then it's a good idea to put that code in a subroutine. Then you can call it whenever you need it and it only has to be written/maintained once.

However, here's a suggested way of re-writing your code. I've used bContinue as a flag to show if the "continue" bit should run. This technique can be useful because you might have some more complex rules about whether to continue or not. You can then deal with the rules and only at the end do you need to test for running or not.

If you carry on with the idea of jumping out of code to the end then you risk not following what you are doing.

Private Sub Search_Results_DblClick(Cancel As Integer)
Dim bContinue As Boolean

If IsNull([Search Results]) Then
    'Do Nothing
    bContinue = False
ElseIf [Search Results].Column(2) = "N" Then
    bContinue = MsgBox("The City [" & [Search Results].Column(0) & "] you have selected is NOT an Acceptable USPS City Name." _
    & vbCrLf & vbCrLf & "Please Note:  If you continue to use this City Name the letter could possibly be returned as Non-Deliverable!" _
    & vbCrLf & vbCrLf & "Are you sure you want to use this City Name?", vbYesNo + vbCritical, "Not an Acceptable USPS City Name...")
    
    If bContinue = False Then  'Don't use this City Name; select a different one.
        MsgBox "Please Select a different City Name.", vbInformation, "Select a Different City Name..."
        Exit Sub
    End If
Else
    bContinue = True    'we are happy with the input and can continue
End If

If bContinue Then
    If CurrentProject.AllForms("Provider Letters").IsLoaded Then
        Forms![Provider Letters]![City] = [Search Results].Column(0)
        Forms![Provider Letters]![State] = [Search Results].Column(1)
    Else
        'Do Nothing for now...
    End If
    DoCmd.Close acForm, "Zip Code Search - Select Record"
End If

End Sub

Chris

 

[gold007eye]

A quick reas of Help suggests that you would code

Label1: Else

Did you do that?

Brian

Just for reference. I tried this and it didn't execute the "Else" code.

 

[gold007eye]

@Stopher:

I did try replacing the existing code with what you suggested, and it only semi works. When the user selects "No" it populates the field anyway and closes the pop-up form instead of having them select a different city.

 

[stopher]

Sorry, I was being stupid and mixing variable types. How about this (with your case statements back in!)...

Dim bContinue As Boolean

If IsNull([Search Results]) Then
    'Do Nothing
    bContinue = False
ElseIf [Search Results].Column(2) = "N" Then
    Select Case MsgBox("The City [" & [Search Results].Column(0) & "] you have selected is NOT an Acceptable USPS City Name." _
    & vbCrLf & vbCrLf & "Please Note:  If you continue to use this City Name the letter could possibly be returned as Non-Deliverable!" _
    & vbCrLf & vbCrLf & "Are you sure you want to use this City Name?", vbYesNo + vbCritical, "Not an Acceptable USPS City Name...")
    
    Case vbYes 'Use this City Name anyways.
        bContinue = True
    Case vbNo 'Don't use this City Name; select a different one.
        bContinue = False
        MsgBox "Please Select a different City Name.", vbInformation, "Select a Different City Name..."
        Exit Sub
    End Select
Else
    bContinue = True    'we are happy with the input and can continue
End If

If bContinue Then
    If CurrentProject.AllForms("Provider Letters").IsLoaded Then
        Forms![Provider Letters]![City] = [Search Results].Column(0)
        Forms![Provider Letters]![State] = [Search Results].Column(1)
    Else
        'Do Nothing for now...
    End If
    DoCmd.Close acForm, "Zip Code Search - Select Record"
End If

 

[namliam]

Afaik a colon denotes a label (like what Brian is referring to). In theory you can the "goto" a label. However, in practice this is considered bad practice and will get you tied up in knots. Best to avoid Goto and labels (except for error handling).

I would like to take this a step further... simply dont use labels and goto's except for error handling....

It will give you huge problems in maintainnig your code if you do...

 

[gemma-the-husky]

i find it very hard to follow the control flow with code like this

i prefer to break code into smaller chunks, and I would save the result from a msgbox, if i need to use it

the else: with a colon is generally used as in case statements to pick up non specified options i think

so i would have something like

Private Sub Search_Results_DblClick(Cancel As Integer)
dim searchflag as string
dim msgboxresult as long

If IsNull([Search Results]) Then
   'Do Nothing
    exit sub
end if

searchflag = nz([Search Results].Column(2) ,"N")
if searchflag =  "N" Then
    msgboxresult = MsgBox(question,responses)
    select case msgboxresult
       case vbok:
       case vbcancel:
       case else:
    end select
end if

etc

 

[gold007eye]

@ Stopher:

That worked perfectly. NOw for my next bump in the road.

The form "Provider Letters" has 3 sections that contain Zip Code, City, & State. How from this popup form can I determine which field on the "Provider Letters" form currently has the focus, and based on that Update the City and State?

Example:

If

Main Address Section: Zip Code currently has the focus on that form so populate the City/State fields for that section

ElseIf

Pay To Address Section: Zip Code currently has the focus on that form so populate the City/State fields for that section

ElseIf

Practice Address Section: Zip Code currently has the focus on that form so populate the City/State fields for that section

Does that make sense? Somehow I need it to determine the Active Control on the "Provider Letters" (or which ever form is open) form and then take the appropriate action(s) from the popup form.

 

[jarico75@yahoo.]

If I understand you correctly, You are better off setting focus yourself rather than trying to determine where access has placed the focus.
Below is a very simple way of setting specific focus.

Form.FirstFieldName.SetFocus
'Run your Code here
Form.NextFieldname.SetFocus
'Run you next set of code here

 

[gold007eye]

Well I wasn't trying to set the focus (per say); I was more trying to determine which field the original data (Zip Code) was entered to bring up the Popup form. Then based on that run the code.