Select case with msgBox not working

Posted on 2006-03-22
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

    Exit Sub
Case vbNo
Exit Sub

End Select

End Sub
Question by:gogetsome
    LVL 52

    Accepted Solution

    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.
    LVL 15

    Expert Comment

    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
    LVL 29

    Expert Comment

    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 Comment

    Thank you everyone for your help. Without good people like yourselves, I would not be where I am today.

    Featured Post

    Highfive Gives IT Their Time Back

    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

    Suggested Solutions

    Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

    734 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

    20 Experts available now in Live!

    Get 1:1 Help Now