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

    Loop thru records status and change back color of label

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


    Code:
    Call myCn()
            Dim stat As String
            Try
                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)
                        cn.Open()
    
    
    
    
                        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
    
    
                        Else
    
    
                        End If
                        cmd.Dispose()
                        'dr.Close()
                    End Using
    
    
                Next
    
    
            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
    Location
    Kentucky
    Posts
    8,344
    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:
    Code:
    sub cboBox_afterupdate()
      SetLblColor
    end sub
    
    'and if you may want to set it if user changes record
    sub Form_OnCurrent()
       SetLblColor
    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
    Posts
    8
    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