Solved check if listbox contain specific value (1 Viewer)

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:02
Joined
Feb 19, 2002
Messages
42,990
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

Registered User.
Local time
Today, 18:02
Joined
Jul 14, 2015
Messages
193
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

Super Moderator
Staff member
Local time
Today, 12:02
Joined
Feb 19, 2002
Messages
42,990
Then use a textbox. Is that a problem? If the Control Source is a calculation, the textbox won't be updateable anyway.
 

eshai

Registered User.
Local time
Today, 18:02
Joined
Jul 14, 2015
Messages
193
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

Super Moderator
Staff member
Local time
Today, 12:02
Joined
Feb 19, 2002
Messages
42,990
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

Registered User.
Local time
Today, 18:02
Joined
Jul 14, 2015
Messages
193
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

You've got your good things, and you've got mine.
Local time
Today, 12:02
Joined
May 21, 2018
Messages
8,463
@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 nameplaceroombed
nameABuilding1
1​
1​
nameBBuilding1
1​
2​
nameDBuilding1
1​
4​
nameEBuilding1
1​
5​
NameFFBuilding1
2​
1​
NameHHBuilding1
2​
3​
NameGGBuilding1
2​
5​
NameIIIIBuilding1
3​
3​
NameJJJBuilding1
3​
4​
NameLLLBuilding1
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.
Room.jpg

room2.jpg


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

Code:
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
 

Attachments

  • lisboxSelectionsColors (2).accdb
    1.3 MB · Views: 100

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:02
Joined
May 21, 2018
Messages
8,463
If you wanted to you could write the student name in the bed.
roomNames.jpg

Code:
  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
 

Attachments

  • lisboxSelectionsColors 3.accdb
    548 KB · Views: 109

eshai

Registered User.
Local time
Today, 18:02
Joined
Jul 14, 2015
Messages
193
If you wanted to you could write the student name in the bed.
View attachment 98697
Code:
  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

You've got your good things, and you've got mine.
Local time
Today, 12:02
Joined
May 21, 2018
Messages
8,463
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

You've got your good things, and you've got mine.
Local time
Today, 12:02
Joined
May 21, 2018
Messages
8,463
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.

Assign.jpg


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.
 

Attachments

  • AssignBeds V1.accdb
    540 KB · Views: 124

Users who are viewing this thread

Top Bottom