Conditional formatting based on the values in another table (1 Viewer)

[lbs]

You might want a full calendar view to see all the activities at once. Take a look at the Peter Hibbs calendar with my year view to add codes. You could add codes for MDT, PM. etc. Then you could have visibility of everything going on. You would just have to do an union query on your multiple tables to get it display all activities. You would also need to filter on consult ID.

Date Pickers, Calendar Controls, and Calendar resources

There has been some recent interest in a replacement for the MS calendar control that existed prior to 2010. I have been collecting date pickers, calendar controls, and calendars for a long time and thought it would be a good idea to have one thread with lots of good examples. Some of these...

www.access-programmers.co.uk

Yes, a calendar view is much better. The only problem is how to incorporate such complexity as in my rota in one form.
I think I though about Union query and tried to build it at the beginning but I failed and instead someone on this forum advised and helped me to use concatenate function to accumulate similar activities of multiple consultants into one cell.
This is how a page from my Rota/report looks like.

 

[lbs]

That would drive me absolutely nuts. You get an alert without any context. You need to show the reasons along with the coloring if you want.
This is all you need. This will provide all alerts if you want or you can do conditional formatting if something is returned.

Public Function Consult_Status(dtmDate As Date, Cons_ID As String) As String

  Dim strAlert As String
  Dim PM As String
  Dim MDT As String
  Dim AbsentNextDay As String
  Dim Absent2DaysAfter As String
  Dim Absent3DaysAfter

  Dim formatDate As String
  Dim formatNextDay As String
  Dim format2DaysAfter As String
  Dim format3DaysAfter As String

  formatDate = "#" & Format(dtmDate, "MM/DD/yyyy") & "#"
  formatNextDay = "#" & Format(dtmDate + 1, "MM/DD/yyyy") & "#"
  format2DaysAfter = "#" & Format(dtmDate + 2, "MM/DD/yyyy") & "#"
  format3DaysAfter = "#" & Format(dtmDate + 3, "MM/DD/yyyy") & "#"

  If DCount("*", "t_r_10_MDT_REG", "Cons = '" & Cons_ID & "' AND Date_MDT = " & formatDate) > 0 Then
    MDT = " Has_MDT"
  End If
  If DCount("*", "t_r_15_Pm", "Cons_PM = '" & Cons_ID & "' AND Date_PM = " & formatDate) > 0 Then
    PM = "Has_PM"
  End If

'Absensences
  If DCount("*", "t_r_11_Cons_Absense", "Cons_Abs = '" & Cons_ID & "' AND Date_abs = " & formatNextDay) > 0 Then
    AbsentNextDay = " Absent_" & formatNextDay
  End If

  If DCount("*", "t_r_11_Cons_Absense", "Cons_Abs = '" & Cons_ID & "' AND Date_abs = " & format2DaysAfter) > 0 Then
    Absent2DaysAfter = " Absent_" & format2DaysAfter
  End If

  If DCount("*", "t_r_11_Cons_Absense", "Cons_Abs = '" & Cons_ID & "' AND Date_abs = " & format3DaysAfter) > 0 Then
    AbsentNextDay = " Absent_" & format3DaysAfter
  End If



  Consult_Status = MDT & PM & AbsentNextDay & Absent2DaysAfter & Absent3DaysAfter


End Function

Put this in a calculated control and it will return all the different reasons. Then you can show the reasons and if you want conditional formatting.

Expression is: =Consult_Status([Cons_ID],[Your_DateField])

MajP, I've been playing with your code and what I found that for some reason it doesn't show the 3rd day. It shows next day, 2 days after, but not 3 days after. I can't figure out why.

 

[MajP]

I will look at the code. But thinking about the union query that is a better approach and I personally would do something like this.

This uses the union query and shows more details. The first subform just filters to the active person in the activities subform. The second shows all conflicts for all consultants for that date. FYI, I added some records to RR so I can test. Either way you can use the union query to Alert, Conditional format, build a subform as shown, or multiple. I may need to modify the code a little for refreshing the subforms on new records. I did not try that yet. But if this idea makes sense, I can update.

The union query is built on some other queries starting with q_Conflict. You can use your concatenate on the union to concat the conflicts as well.

Public Function GetConflicts(Cons_ID As String, DtmDate As Date) As String
  GetConflicts = ConcatRelated("ConflictReason", "q_Conflict_Reasons", "Cons_ID = '" & Cons_ID & "' AND ConflictDate = #" & Format(DtmDate, "mm/dd/yyyy") & "#")
End Function

 

[lbs]

I will look at the code. But thinking about the union query that is a better approach and I personally would do something like this.
View attachment 87943

This uses the union query and shows more details. The first subform just filters to the active person in the activities subform. The second shows all conflicts for all consultants for that date. FYI, I added some records to RR so I can test. Either way you can use the union query to Alert, Conditional format, build a subform as shown, or multiple. I may need to modify the code a little for refreshing the subforms on new records. I did not try that yet. But if this idea makes sense, I can update.

The union query is built on some other queries starting with q_Conflict. You can use your concatenate on the union to concat the conflicts as well.

Public Function GetConflicts(Cons_ID As String, DtmDate As Date) As String
  GetConflicts = ConcatRelated("ConflictReason", "q_Conflict_Reasons", "Cons_ID = '" & Cons_ID & "' AND ConflictDate = #" & Format(DtmDate, "mm/dd/yyyy") & "#")
End Function

That's absolutely marvellous! Thank you very much!
The second table All conflicts should be called Potential conflicts. I am going to add 4th and 5th days absent. And I was wondering if I can make sure that if some days absent fall on Saturday/Sunday they would be ignored and the next working day is returned instead. I know it should be done with If criteria and function Week day number. I will try it now but I am not sure I can do it quickly.

 

[lbs]

I will look at the code. But thinking about the union query that is a better approach and I personally would do something like this.
View attachment 87943

This uses the union query and shows more details. The first subform just filters to the active person in the activities subform. The second shows all conflicts for all consultants for that date. FYI, I added some records to RR so I can test. Either way you can use the union query to Alert, Conditional format, build a subform as shown, or multiple. I may need to modify the code a little for refreshing the subforms on new records. I did not try that yet. But if this idea makes sense, I can update.

The union query is built on some other queries starting with q_Conflict. You can use your concatenate on the union to concat the conflicts as well.

Public Function GetConflicts(Cons_ID As String, DtmDate As Date) As String
  GetConflicts = ConcatRelated("ConflictReason", "q_Conflict_Reasons", "Cons_ID = '" & Cons_ID & "' AND ConflictDate = #" & Format(DtmDate, "mm/dd/yyyy") & "#")
End Function

I am sorry I was not clear in the previous post regarding Saturday/Sunday. Actually what may happen, for example, the Absent tomorrow date may be Saturday. Because we don't work during weekends, I will not see this date as conflict. I would like that the query instead picks the next working day

 

[MajP]

To verify, if you are checking Friday and they are absent Monday you want it to show one day out?

 

[lbs]

To verify, if you are checking Friday and they are absent Monday you want it to show one day out?

Yes

 

[MajP]

You would have to modify each absence query
Instead of subtracting 1,2,...5
such as
[date_abs]-1 AS DateAbsent
replace with
Workdaysprior([date_abs], 1) ...2,3,4,5

example

SELECT t_r_11_cons_absense.cons_abs,
       Workdaysprior([date_abs], 1)                            AS DateAbsent,
       "absent 1 day out:" & [t_s_06_full_am_pm].[full_am_pm] AS ConflictReason
FROM   t_s_06_full_am_pm
       INNER JOIN t_r_11_cons_absense
               ON t_s_06_full_am_pm.full_am_pm_id =
                  t_r_11_cons_absense.full_am_pm;

Public Function WorkdaysPrior(AbsentDate As Date, daysPrior As Integer) As Date
    Dim counter As Integer
    WorkdaysPrior = AbsentDate
    Do
      WorkdaysPrior = WorkdaysPrior - 1
      If Weekday(WorkdaysPrior) <> 1 And Weekday(WorkdaysPrior) <> 7 Then
        counter = counter + 1
      End If
    Loop Until counter = daysPrior
End Function

 

[lbs]

You would have to modify each absence query
Instead of subtracting 1,2,...5
such as
[date_abs]-1 AS DateAbsent
replace with
Workdaysprior([date_abs], 1) ...2,3,4,5

example

SELECT t_r_11_cons_absense.cons_abs,
       Workdaysprior([date_abs], 1)                            AS DateAbsent,
       "absent 1 day out:" & [t_s_06_full_am_pm].[full_am_pm] AS ConflictReason
FROM   t_s_06_full_am_pm
       INNER JOIN t_r_11_cons_absense
               ON t_s_06_full_am_pm.full_am_pm_id =
                  t_r_11_cons_absense.full_am_pm;

Public Function WorkdaysPrior(AbsentDate As Date, daysPrior As Integer) As Date
    Dim counter As Integer
    WorkdaysPrior = AbsentDate
    Do
      WorkdaysPrior = WorkdaysPrior - 1
      If Weekday(WorkdaysPrior) <> 1 And Weekday(WorkdaysPrior) <> 7 Then
        counter = counter + 1
      End If
    Loop Until counter = daysPrior
End Function

everything works perfectly well. I am going to use my concatenate to accumulate all potential conflicts in one place and remove unnecessary subforms for MDTs and PMs. That's a huge help and thank you so much! I only wonder what to do to refresh conflicts as I update activities. At the moment I have to jump to another record and then return back.

 

[MajP]

in activity subform

Private Sub cons_AfterUpdate()
  Me.Parent.txtLinkCons = Me.cons
   Me.Parent.q_Conflict_Reasons.Form.Requery
End Sub

 

[lbs]

in activity subform

Private Sub cons_AfterUpdate()
  Me.Parent.txtLinkCons = Me.cons
   Me.Parent.q_Conflict_Reasons.Form.Requery
End Sub

Fantastic! Works well. It's a big step forward in my database. I am very grateful to you MajP!

 

[MajP]

One question. Seems like you have a lot of similar forms. Like Activities, MDT, Super, Absenses. I think you could provide a simpler UI and a much easier database to manage if you just tabbed your different forms. The user gets all the controls in one places. As the user enters an activity, PM, MDT, etc they see the conflicts and all data is on one form. Controls generic to the rotation are at the bottom, specific controls are on the tabs.

 

[lbs]

One question. Seems like you have a lot of similar forms. Like Activities, MDT, Super, Absenses. I think you could provide a simpler UI and a much easier database to manage if you just tabbed your different forms. The user gets all the controls in one places. As the user enters an activity, PM, MDT, etc they see the conflicts and all data is on one form. Controls generic to the rotation are at the bottom, specific controls are on the tabs.
View attachment 87965

View attachment 87963

Yes, that's definitely looks more elegant. I also would like to save more space by arranging dates in parent form on the left and the child content on the right. But it seems impossible. The best would be to have some sort of a horizontal linear calendar but it's beyond my capabilities.

 

[MajP]

I also would like to save more space by arranging dates in parent form on the left and the child content on the right. But it seems impossible. The best would be to have some sort of a horizontal linear calendar but it's beyond my capabilities.

I was going to recommend that. You owe me a beer. I think you will like this. Need to make sure in all the subforms on current and cons after update to set txtLinkCons on the parent form. That will keep the conflicts updated.

 

[lbs]

I was going to recommend that. You owe me a beer. I think you will like this. Need to make sure in all the subforms on current and cons after update to set txtLinkCons on the parent form. That will keep the conflicts updated.
View attachment 87975

I owe much more than beer. How do I repay???
This is exactly how I wanted to. But unfortunately now when I play with all sunforms every now and then it stops with error messages. Is this because before all the forms were in one Form footer before and now they are in different Tabs?

 

[MajP]

The main form is now unbound.
1. The main form links to the dates subform by the combo box when you pick a rotation.
2. In the dates subform the current event sets the textbox in the header (txtLinkDate). This is used to link all the other subforms. That is its purpose. There are other ways to link subforms, I find this easiest. This txtLinkDate could be hidden, but I leave it visible until I got everything working.

So that line should likely replace me.Parent![Date] with Me.Parent.TxtLinkDate

 

[lbs]

The main form is now unbound.
1. The main form links to the dates subform by the combo box when you pick a rotation.
2. In the dates subform the current event sets the textbox in the header (txtLinkDate). This is used to link all the other subforms. That is its purpose. There are other ways to link subforms, I find this easiest. This txtLinkDate could be hidden, but I leave it visible until I got everything working.

So that line should likely replace me.Parent![Date] with Me.Parent.TxtLinkDate

Yes it worked. No more errors

 

[lbs]

The main form is now unbound.
1. The main form links to the dates subform by the combo box when you pick a rotation.
2. In the dates subform the current event sets the textbox in the header (txtLinkDate). This is used to link all the other subforms. That is its purpose. There are other ways to link subforms, I find this easiest. This txtLinkDate could be hidden, but I leave it visible until I got everything working.

So that line should likely replace me.Parent![Date] with Me.Parent.TxtLinkDate

MajP, how did you manage to create dropdown box cbo_rota_choose situated on unboud form and it filters records in subform Dates?

 

[MajP]

MajP, how did you manage to create dropdown box cbo_rota_choose situated on unboud form and it filters records in subform Dates?

You can link a subform by a field (normally) or a control. The combo returns a rotationID. so the master link is simply the name of the combobox.

That is the purpose of the textboxes in the header. Same technique.

You could have also bound the main form to the rotations query. Then have the combo box move to that rotation

You can take this to the extreme. You can have an unbound textbox on a form. Link the subform to that textbox. Type in the textbox and it will move to those records..

 

[lbs]

You can link a subform by a field (normally) or a control. The combo returns a rotationID. so the master link is simply the name of the combobox.

That is the purpose of the textboxes in the header. Same technique.

You could have also bound the main form to the rotations query. Then have the combo box move to that rotation

You can take this to the extreme. You can have an unbound textbox on a form. Link the subform to that textbox. Type in the textbox and it will move to those records..

Hello MajP
Many thanks for all your tips. I learnt a lot from you.
I am keep tuning my DB. Now I have this problem. I set f_f_00_Main as starting form and the app starts with error message:

Then, when I close debug and choose the rota, it's OK. How can I sort it? I think I need to force the form on opening immediately go to one of the rotas. But I don't know haw to do this. I would prefer of course current rota, according to the calendar, but ultimately any rota.