Shorten My Code
How can i shorten the code below?
Is there any other to make this code shorter?
Is there any other to make this code shorter?
Private Sub Marker()
Dim i As Integer
For i = 0 To 12
cmdTableIndex(i).BackColor = vbWhite
cmdTableIndex(i).Caption = ""
cmdTabBill(i).BackColor = vbWhite
Next
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Call OPEN_CON(cn, "MyTable", "Winpos")
Set rs = New ADODB.Recordset
sql = "SELECT * FROM The_Table WHERE Table_No in (101,102,103,104,105,106,107,108,109,110,111,112,114)"
rs.LockType = adLockOptimistic
rs.CursorType = adOpenKeyset
rs.Open sql, cn
With rs
Do While Not .EOF
Select Case Trim(.Fields("Table_No").Value)
Case 100
cmdTableIndex(0).BackColor = vbRed
cmdTableIndex(0).Caption = "Accomodated" & vbNewLine & "100"
cmdTabBill(0).BackColor = vbRed
Case 101
cmdTableIndex(1).BackColor = vbRed
cmdTableIndex(1).Caption = "Accomodated" & vbNewLine & "101"
cmdTabBill(1).BackColor = vbRed
Case 102
cmdTableIndex(2).BackColor = vbRed
cmdTableIndex(2).Caption = "Accomodated" & vbNewLine & "102"
cmdTabBill(2).BackColor = vbRed
Case 103
cmdTableIndex(3).BackColor = vbRed
cmdTableIndex(3).Caption = "Accomodated" & vbNewLine & "103"
cmdTabBill(3).BackColor = vbRed
Case 104
cmdTableIndex(4).BackColor = vbRed
cmdTableIndex(4).Caption = "Accomodated" & vbNewLine & "104"
cmdTabBill(4).BackColor = vbRed
Case 105
cmdTableIndex(5).BackColor = vbRed
cmdTableIndex(5).Caption = "Accomodated" & vbNewLine & "105"
cmdTabBill(5).BackColor = vbRed
Case 106
cmdTableIndex(6).BackColor = vbRed
cmdTableIndex(6).Caption = "Accomodated" & vbNewLine & "106"
cmdTabBill(6).BackColor = vbRed
Case 107
cmdTableIndex(7).BackColor = vbRed
cmdTableIndex(7).Caption = "Accomodated" & vbNewLine & "107"
cmdTabBill(7).BackColor = vbRed
Case 108
cmdTableIndex(8).BackColor = vbRed
cmdTableIndex(8).Caption = "Accomodated" & vbNewLine & "108"
cmdTabBill(8).BackColor = vbRed
Case 109
cmdTableIndex(9).BackColor = vbRed
cmdTableIndex(9).Caption = "Accomodated" & vbNewLine & "109"
cmdTabBill(9).BackColor = vbRed
Case 110
cmdTableIndex(10).BackColor = vbRed
cmdTableIndex(10).Caption = "Accomodated" & vbNewLine & "110"
cmdTabBill(10).BackColor = vbRed
Case 111
cmdTableIndex(11).BackColor = vbYellow
cmdTableIndex(11).Caption = "Accomodated" & vbNewLine & "111"
cmdTabBill(11).BackColor = vbRed
End Select
Call .MoveNext
Loop
.Close
End With
Set rs = Nothing
Set cn = Nothing
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry but you mean to say that i must change the index as from 0 to 101 or 1 to 102 and so on..? and then proceed the given code?
yes
emoreau for 113 you're right but I have assumed that he want's to skip it.
ASKER
Ah Thanks!
i will try it and maybe i post another question in relation with this topic.
thanks!
i will try it and maybe i post another question in relation with this topic.
thanks!
ASKER
What about the line 14? is there any other way to shorten that line? If yes then i will post another question for that.
Thanks!