Solved check if listbox contain specific value

[Pat Hartman]

any way i don't like to use Conditional Formatting

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

But I need the procedure to be on Form_Load

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

 

[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

 

[Pat Hartman]

Then use a textbox. Is that a problem? If the Control Source is a calculation, the textbox won't be updateable anyway.

 

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

 

[Pat Hartman]

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.

 

[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

 

[MajP]

If you wanted to you could write the student name in the bed.

  Dim rs As DAO.Recordset
  Dim I As Integer
 
 
  For I = 1 To 5
    With Me.Controls("lbl" & I)
      .BackColor = vbGreen
      .Caption = .Tag
    End With
  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
    With Me.Controls("lbl" & rs!bed)
      .BackColor = vbRed
      .Caption = .Tag & vbCrLf & rs![student name]
    End With
   rs.MoveNext
  Loop
  Me.Repaint

 

[eshai]

If you wanted to you could write the student name in the bed.
View attachment 98697

  Dim rs As DAO.Recordset
  Dim I As Integer


  For I = 1 To 5
    With Me.Controls("lbl" & I)
      .BackColor = vbGreen
      .Caption = .Tag
    End With
  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
    With Me.Controls("lbl" & rs!bed)
      .BackColor = vbRed
      .Caption = .Tag & vbCrLf & rs![student name]
    End With
   rs.MoveNext
  Loop
  Me.Repaint

thank you this is great

 

[MajP]

In this example the table only had records for the beds that are assigned. I reality I think you would want a table with every room and every bed listed. That would be a lot easier. So you never add or delete records, you just "unassign" a bed. Unassigned beds should get a value like "Available". Then you modify the loop to check if it is available

Do While Not rs.EOF
if rs![student name] <> "Available" Then

With Me.Controls("lbl" & rs!bed)
.BackColor = vbRed
.Caption = .Tag & vbCrLf & rs![student name]
End With

end if
rs.MoveNext

 

[MajP]

I updated the demo with some bells and whistles to demonstrate some ideas.
I added three rooms. All rooms have 5 beds.
I added conditional formatting in the continuous form.
I added a click event to a "bed" to assign a new student or remove them.

Although the code for all of this is very short, it demonstrates a lot of different concepts.
Creating a common function for each control
Using tag property
looping recordsets
Opening a popup and requerying for new values.