We help IT Professionals succeed at work.

Select case with msgBox not working

gogetsome
gogetsome asked
on
Medium Priority
444 Views
Last Modified: 2010-04-07
Hello, I'm trying to get confirmation from the user with a yes/no message box before I populate a grid with data from the database. The case vbNo is working correctly by exiting the procedure, yet the case VBYes is not running the code necessary to populate the grid.

Could someone please look at my code and help figure out why?

Here is the code:


Select Case MsgBox("Are you sure you want to use the schedule 70?" _
, vbYesNo)
Case vbYes
'Get 70 data to grid
On Error GoTo ErrorHandler

    CancelLogic = True
   
    Dim userinfo As New RetrieveGlobals.retrieveuserinfo
   
    Dim adCNN As ADODB.Connection
    Dim adrst As ADODB.Recordset
    Dim strcmd As String
    Dim strCnn As String
   
    Dim response As Variant

    sqluid = userinfo.retrieve_user()
    sqlpwd = userinfo.sql_password()
    SQLdb = userinfo.intercompany_id()
    sqlsvr = userinfo.sql_datasourcename()
   
    With CLIN_Filter
        .user = sqluid
        .password = sqlpwd
        .SERVER = sqlsvr
        .DataBase = SQLdb
       
        .Filter_Type = "CLIN"
        .Height = 7000
        .dHeight = 3750
        .Width = 8500
        .dWidth = 7800
        .addSorts = 2
        .reportCount = 4
       
        .sp_Name = "csp_ws_lookup_item_clin_Only70"
         
         response = .load_view

        CLIN_Filter_RowReturned response

    End With
   
    Exit Sub
   
ErrorHandler:

    Exit Sub
   
Case vbNo
Exit Sub

End Select

End Sub
Comment
Watch Question

Senior Systems and Integration Developer
CERTIFIED EXPERT
Commented:
Its possibly erroring, your error handler doesn't do anything. Try adding a msgbox into the error handler to see if it is dropping into there.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Replace the top like this

Select Case MsgBox("Are you sure you want to use the schedule 70?" , vbYesNo)
Case vbYes
MsgBox "I Clicked Yes"
'Get 70 data to grid
On Error GoTo ErrorHandler

I think you'll find it works
Top Expert 2010

Commented:
You dont need to exit the sub if they click no because in the select case statement there is nothing to execute, below is another way to use select case for user choice

Dim Choice As Long
   
    Choice = MsgBox("some message", vbYesNoCancel)
   
    Select Case Choice
        Case vbYes
            Debug.Print "yes"
        Case vbNo
            Debug.Print "no"
        Case vbCancel
            Debug.Print "cancel"
    End Select

Author

Commented:
Thank you everyone for your help. Without good people like yourselves, I would not be where I am today.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.