Results 1 to 3 of 3
  1. #1
    aceSoft is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2017

    Loop thru records status and change back color of label

    I want to translate this to vba. This is my code in using ms access as database.

    Call myCn()
            Dim stat As String
                Dim i As Integer
                For i = 0 To cb_wetstalls.Items.Count Step 1
                    Dim xSQL As New System.Text.StringBuilder
                    xSQL.AppendLine("SELECT *")
                    xSQL.AppendLine("FROM tbl_stall")
                    xSQL.AppendLine("WHERE StallID = '" & cb_wetstalls.Items(i) & "'")
                    Using cn As New OleDbConnection(ConString)
                        Dim cmd As New OleDb.OleDbCommand(xSQL.ToString, cn)
                        dr = cmd.ExecuteReader
                        If dr.HasRows = True Then
                            While dr.Read()
                                stat = (dr(4).ToString)
                            End While
                            If stat = "Available" Then
                                btnarray(i).backcolor = Color.LightSalmon
                            ElseIf stat = "Lease" Then
                                btnarray(i).backcolor = Color.LimeGreen
                            ElseIf stat = "Rent" Then
                                btnarray(i).backcolor = Color.Yellow
                            End If
                        End If
                    End Using
            Catch ex As Exception
            End Try

    I have a combo box in access populated from a table. It contains ID.
    I want to loop through each item in combobox and check its corresponding status in the table.
    After checking status it shall select which bakcolor is applicable.
    In access I use label for it. Can somebody guide me on how to convert my code.
    Please see image below. Thanks in advance.

    Click image for larger version. 

Name:	Untitled.png 
Views:	15 
Size:	28.7 KB 
ID:	27746

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    i dont understand. It the picture of the table? Tables dont have vertical data in a cell.
    which part of the table is the ID, and which is the color the combo changes to?

    the combo box should have 2 fields. Only 1 need show to the user.
    col.1 is the ID, col.2 is the color you want it to change to. (tho col.2.columnwidth = 0)
    A, 16
    B, 99

    then in the AFTERUPDATE event, the label of the combo will change from what the user picks:
    sub cboBox_afterupdate()
    end sub
    'and if you may want to set it if user changes record
    sub Form_OnCurrent()
    end sub
    sub  SetLblColor()
      label1.backcolor = cboBox.column(1)    'remember in VB, column# start with zero)
    end sub

  3. #3
    aceSoft is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2017
    The picture is in access form. the squares are Label's that's why it has vertical cell.
    There is no user intervention here.
    When this form load I want it to automatically read database table and assign backcolor to each label.
    Say I have a table with 2 column "ID" and "Status".
    Now I load all ID on the combobox.
    Now I want a code that will Loop on combobox each record to read its status on the table.

    If Status is available then A1.backcolor = vbred elseif Status is Lease then A1.backcolor = vbblue something like that.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 20
    Last Post: 04-29-2016, 04:39 PM
  2. Replies: 7
    Last Post: 04-25-2016, 02:13 AM
  3. Replies: 3
    Last Post: 06-19-2014, 12:21 PM
  4. Replies: 5
    Last Post: 09-18-2012, 12:39 PM
  5. Replies: 1
    Last Post: 03-29-2009, 08:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums