Solved check if listbox contain specific value

[eshai]

hi:
i have a listbox 2 Column that get the value from a query
in the form there is also textbox
my vba code should change the background color of the textbox if some value exist in the listbox
and if not set different background
i have like a blank in my mind and i can't remember

If Me.listbox = "1" Then Me.textbox.BackColor = RGB(255, 0, 0) Else Me.textbox.BackColor = RGB(0, 255, 0)

.value
.Column
.rowsource
.???

 

[June7]

Try the code you show. Every instance of control will show same value.

Use Conditional Formatting and no VBA needed and color will be dynamic for each record.

 

[Eugene-LS]

Use ListIndex property :

If Me.listbox.ListIndex > -1 Then 'exist in the listbox
       Me.textbox.BackColor = RGB(255, 0, 0)
Else
       Me.textbox.BackColor = RGB(0, 255, 0)
End If

 

[eshai]

Use ListIndex property :

If Me.listbox.ListIndex > -1 Then 'exist in the listbox
       Me.textbox.BackColor = RGB(255, 0, 0)
Else
       Me.textbox.BackColor = RGB(0, 255, 0)
End If

there are all kind of numbers 1,1a,1b,2 ect'
your code is giving the "Else"

 

[Pat Hartman]

I am mystified by the logic. Why are you trying to find out if the RowSource of the listbox contains a specific value?

 

[eshai]

Try the code you show. Every instance of control will show same value.

Use Conditional Formatting and no VBA needed and color will be dynamic for each record.

thank you
my confuse its a label not a textbox
any way i don't like to use Conditional Formatting access is not a graphical software

 

[Eugene-LS]

there are all kind of numbers 1,1a,1b,2 ect'
your code is giving the "Else"

you asked: if some value exist in the listbox ? ...
The ListIndex property returns -1 when it's value not exist in ListBox.RowSouce ...

 

[eshai]

I am mystified by the logic. Why are you trying to find out if the RowSource of the listbox contains a specific value?

I'm building a student dormitory drawing and the list box well show me the name and the label well show my Whether the bed is occupied or not

 

[eshai]

you asked: if some value exist in the listbox ? ...
The ListIndex property returns -1 when it's value not exist in ListBox.RowSouce ...

thank you
so in words the code should be like "if listbox contain "1b" then background =...

 

[Pat Hartman]

If you are using a listbox, the listbox can show that information since it shows multiple columns. You don't need code to populate a label.

 

[Eugene-LS]

so in words the code should be like "if listbox contain "1b" then background =...

No! I think not that way.
Please publish your form here - it well be easier to answer

 

[Pat Hartman]

Maybe you should post the database with instructions regarding what you want to see.

my vba code should change the background color of the textbox if some value exist in the listbox

This statement does not make sense. What is the criteria for the RowSource of the listbox? Why do you want to know if a value is in the listbox's RowSource? Why do you not want to know what is selected?

 

[eshai]

Maybe you should post the database with instructions regarding what you want to see.


This statement does not make sense. What is the criteria for the RowSource of the listbox? Why do you want to know if a value is in the listbox's RowSource? Why do you not want to know what is selected?

my db is in other language
il explain what i did. I built a room drawing took my original drawing and put it in the back of the form Next to each room is a list box
that show the name and the bad number on the drawing i used labels as bads 1,2,3,4 ect' now if the number of the bad is in the list box
so the bad should be red and if not green
for every list box i used query builder as Row Source that take the data from the main query

 

[Pat Hartman]

Access isn't very good at graphic stuff. Your table also sounds like it is unnormalized. you can open a recordset and load it into an array since it sounds like it will be small. Then you have to loop through the controls for each record and look in the array for the control's value. Use that to change the color of the control. The alternative to the array is to use dlookup to the table/query holding the bad items.

 

[Gasman]

thank you
my confuse its a label not a textbox
any way i don't like to use Conditional Formatting access is not a graphical software

Yet, you are trying to use it as such?

 

[eshai]

Yet, you are trying to use it as such?

haha first this end user is working on 27" screen an a powerful pc
second i did some crazy stuff with access graphics
this only small part of one room a needed to edit it sorry

 

[MajP]

Private Sub List16_AfterUpdate()
  LoopSelections
End Sub

Public Sub LoopSelections()
  Dim lst As Access.ListBox
  Dim i As Integer
  Dim Val As Variant
  Set lst = Me.List16

  'Unselect all colors
  For i = 0 To lst.ListCount - 1
    Val = lst.Column(0, i)
    UnSelectedColor Val
Next i
'Reselect
For i = 0 To lst.ItemsSelected.Count - 1
    Val = lst.Column(0, lst.ItemsSelected(i))
    SelectedColor Val
  Next i

End Sub

Public Sub SelectedColor(Val As Variant)
  Me.Controls("Label" & Val).BackColor = vbRed
End Sub
Public Sub UnSelectedColor(Val As Variant)
  Me.Controls("Label" & Val).BackColor = vbGreen
End Sub

 

[eshai]

View attachment 98677

Thank you so much appreciating your effort
But I need the procedure to be on Form_Load Without selecting a record
in my db the code is wrong

 

[MajP]

The code is written to call from any event. Probably want to call from current event.

 

[eshai]

The code is written to call from any event. Probably want to call from current event.

Can you check what I did here?