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"

 

[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 =...

 

[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

 

[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

 

[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?

 

[eshai]

You may not like it but it is your only option for continuous forms

If the form is bound, the code should run in the Current event.

label dont have Conditional Formatting i will have to do it on textbox

 

[eshai]

ok I solved it in another method
I put the original query as a subform
and on Form_Load "If [sfQryrooms].Form!" place,room,bad=

 

[eshai]

Not sure why you are bothering with us. You haven't listened to a single expert suggestion. But as long as it works for you, it is yours to maintain.

Just remember, whatever happens in the Load event happens ONCE and so when it is data related, all subsequent records will be disconnected.

i did listened but sometimes you have to answer Q' that no one try to did before in access because that what the client ask for and client is
always right .i can say no
this forum help a lot before so i keep Ask here Q'
And the form is for viewing only

 

[MajP]

@eshai,
Did you get a solution, because I have no idea what what you and @Pat Hartman are discussing? Is that a different problem? I thought there was a graphic that you wanted to color certain controls if a student was assigned to a bed. I do not understand or see any discussion about a continuous form and I can think of no way to do that in Conditional formatting. Maybe we are talking two different problems.

I would assume you have something like this to assign students to a bed.
Query1 Query1

student name	place	room	bed
nameA	Building1	1​	1​
nameB	Building1	1​	2​
nameD	Building1	1​	4​
nameE	Building1	1​	5​
NameFF	Building1	2​	1​
NameHH	Building1	2​	3​
NameGG	Building1	2​	5​
NameIIII	Building1	3​	3​
NameJJJ	Building1	3​	4​
NameLLL	Building1	3​	5​

Now this only works if all your rooms have the same bed layout. You could have different be amounts and layouts per room, but that would require a lot more code.

In this example you scroll through the room using the navigation button. A subform shows the assigned students. And the beds are then colored.

Code to do this is a lot easier then looping a listbox.

Private Sub Form_Current()
  Dim rs As DAO.Recordset
  Dim I As Integer
 
 
  For I = 1 To 5
    Me.Controls("lbl" & I).BackColor = vbGreen
  Next I
  DoEvents
  Set rs = Me.qryAssignments_subform.Form.RecordsetClone
  If Not (rs.EOF And rs.BOF) Then rs.MoveFirst
  Do While Not rs.EOF
   Me.Controls("lbl" & rs!bed).BackColor = vbRed
   rs.MoveNext
  Loop
  Me.Repaint
End Sub