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
@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.
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
If you wanted to you could write the student name in the bed.
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
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
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
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.