Inserting the Code

Posted on 2009-04-09
Last Modified: 2013-11-25
The code below is working for identifying the existing of the data. How would i add some kind of code that if the file is not existing the backcolor will turn into vbwhite?
Private Sub Marker()

    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)"

    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

            End Select

            Call .MoveNext



    End With

    Set rs = Nothing

    Set cn = Nothing

End Sub

Open in new window

Question by:Whing Dela Cruz
LVL 69

Accepted Solution

Éric Moreau earned 500 total points
ID: 24115168
before you start looping thhrough your data, reset your controls:

for i = 0 to 4
                     cmdTableIndex(i).BackColor = vbWhite
                     cmdTableIndex(i).Caption = "" 
                     cmdTabBill(i).BackColor = vbWhite

Author Closing Comment

by:Whing Dela Cruz
ID: 31568811
Thanks, Its work
I post other question regarding with the length of this code. How can i make this code shorter. Hope you'll monitor on it..
Thanks to you more Power

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

863 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