excel - 如何在 Excel VBA 中考虑不同的案例选择？

Question

我想做的很简单。我希望用户提供一个有效的输入（“IR1”或“IR2”或“IR3”），弹出一条消息说“IR1 Selected”或“IR2 Selected”或“IR3 Selected”，然后让用户点击提交按钮和报告应该填充来自 MySQL 数据库的表和相关数据。

这是我现在拥有的 VBA 代码

Sub querydatafromMySQL_Report_2()

Dim cntMyConnection As ADODB.Connection
Set cntMyConnection = New ADODB.Connection
cntMyConnection.ConnectionString = "DRIVER={MySQL ODBC 8.0 ANSI 
Driver};Server=localhost;Database=ie465hw3;Uid=root; pwd=12345678; OPTION=3;"

cntMyConnection.Open
Dim rstFirstRecordset As ADODB.Recordset
Set rstFirstRecordset = New ADODB.Recordset
 Dim src As String

src = InputBox("Please Enter RoomID", "Room ID")
If src = Empty Then Exit Sub

Select Case src
Case "IR1"
src = "SELECT * FROM exam WHERE StartTime>'17:00:00' AND RoomID = 'IR1'"
MsgBox "Room IR1 Selected "


Case "IR2"
src = "SELECT * FROM exam WHERE StartTime>'17:00:00' AND RoomID = 'IR2'"
MsgBox "Room IR2 Selected "


Case "IR3"
RoomID = "SELECT * FROM exam WHERE StartTime>'17:00:00' AND RoomID = 'IR3'"
MsgBox "Room IR3 Selected "

Case Else
MsgBox "Invalid RoomID, choose either RoomID: IR1, IR2, or IR3"


End Select

rstFirstRecordset.Open Source:=src, ActiveConnection:=cntMyConnection
 Worksheets("Report_2").Activate
 Dim column As Integer, row As Integer
 With ActiveSheet

 row = 11
 While Not rstFirstRecordset.EOF
 For column = 1 To rstFirstRecordset.Fields.Count
 .Cells(row, column) = rstFirstRecordset(column - 1).Value
 Next column
 rstFirstRecordset.MoveNext
 row = row + 1
 Wend
 End With
 Set rstFirstRecordset = Nothing
 cntMyConnection.Close
 Set cntMyConnection = Nothing
 MsgBox "MySQL DB Connected & Data Report Printed"
 End Sub

IR1 和 IR2 工作正常，但是当我提供“IR3”时出现此错误

然后当我尝试调试这是它的第一行代码时，有什么问题？

 rstFirstRecordset.Open Source:=src, ActiveConnection:=cntMyConnection

这是用户做任何事情之前的样子

这是用户提供有效输入并单击提交按钮后的样子。

标签： excelvbamysql-workbench

Answer 1

正确的语法是

Select Case src
    Case "IR1"
        ' Code here
    Case "IR1"
        ' Code here
    Case "IR1"
        ' Code here
    Case Else
        ' Code here
End Select