Search code examples
vbams-accesspath-finding

(Access, VBA) Changing the color of a button on a Form based on CheckBox values from a Table located on the Forms's Record Source

I am working on creating a database for the Aquaculture sector. In a Form I have placed buttons for each "pool" that exists on place: Form interface So you get an idea, the buttons have following code that forwards to another Form with specific information about given pool:

Private Sub cmd2_Click()
    On Error GoTo cmd2_Click_Err
    codBaseNum = "AT 1/2"

DoCmd.OpenForm "InfoBotonTanque", acNormal, "", "[Base-Número]=""AT 1/2""", , acLast

cmd2_Click_Exit:
    Exit Sub

cmd2_Click_Err:
    MsgBox Error$
    Resume cmd2_Click_Exit

End Sub

Based on two different checkboxes on a Table with information about individual pools, I want the buttons to change color. Here the data from the Table:

enter image description here

Checkbox names: [Reserva] and [Condicion?]. [Base-Número] is the pools ID. All three values are in the Form's RecordSource:

enter image description here

EDIT: Managed to acomplish what I wanted: buttons change color based on CheckBox values! The following code is not ellegant, for I have to type the same with small changes for each button and there are about 150 of them, but it works... The used code is:

Private Sub Form_Load()    
If DLookup("[Reserva]", "Tanques", "[Base-Número] = 'AT 1/2'") = True And DLookup("[Condicion?]", "Tanques", "[Base-Número] = 'AT 1/2'") = False Then
    Me.cmd2.BackColor = RGB(255, 215, 0)
ElseIf DLookup("[Reserva]", "Tanques", "[Base-Número] = 'AT 1/2'") = False And DLookup("[Condicion?]", "Tanques", "[Base-Número] = 'AT 1/2'") = True Then
    Me.cmd2.BackColor = RGB(255, 0, 0)
ElseIf DLookup("[Reserva]", "Tanques", "[Base-Número] = 'AT 1/2'") = False And DLookup("[Condicion?]", "Tanques", "[Base-Número] = 'AT 1/2'") = False Then
    Me.cmd2.BackColor = RGB(51, 171, 249)
Else
    Me.cmd2.BackColor = RGB(120, 120, 120)
End If
End Sub
Solution

  • In order not to repeat code 150 times, use a loop that constructs command button name as well as [Base-Número] parameter with a variable. Consider:

    Private Sub Form_Load()
Dim x As Integer
For x = 1 to 150
    If DLookup("[Reserva]", "Tanques", "[Base-Número] = 'AT 1/" & x & "'") = True _
               And DLookup("[Condicion?]", "Tanques", "[Base-Número] = 'AT 1/" & x & "'") = False Then
        Me("cmd" & x ).BackColor = RGB(255, 215, 0)
    ElseIf DLookup("[Reserva]", "Tanques", "[Base-Número] = 'AT 1/" & x & "'") = False _
               And DLookup("[Condicion?]", "Tanques", "[Base-Número] = 'AT 1/" & x & "'") = True Then
        Me("cmd" & x).BackColor = RGB(255, 0, 0)
    ElseIf DLookup("[Reserva]", "Tanques", "[Base-Número] = 'AT 1/" & x & "'") = False _
               And DLookup("[Condicion?]", "Tanques", "[Base-Número] = 'AT 1/" & x & "'") = False Then
        Me("cmd" & x).BackColor = RGB(51, 171, 249)
    Else
        Me("cmd" & x).BackColor = RGB(120, 120, 120)
    End If
Next
End Sub