Solved check if listbox contain specific value

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