Conditional formatting based on the values in another table

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

 

[MajP]

I did not get an error which means it is likely a timing issue. When you load a subform, it loads before the main form. So this event could occur before the parent form is loaded, and give you an error. Probably something like object does not exist.

You might get lucky by adding Do events. In other words before that code fires it will give resources back to the other events to occur. Or just ignore the error. In that case the subform event will occur and the error will be ignored, but the event will fire again and work once the main loads.

Private Sub Form_Current()
DoEvents
On Error Resume Next
Me.Parent.txtLinkCons = Me.cons
End Sub

 

[lbs]

I did not get an error which means it is likely a timing issue. When you load a subform, it loads before the main form. So this event could occur before the parent form is loaded, and give you an error. Probably something like object does not exist.

You might get lucky by adding Do events. In other words before that code fires it will give resources back to the other events to occur. Or just ignore the error. In that case the subform event will occur and the error will be ignored, but the event will fire again and work once the main loads.

Private Sub Form_Current()
DoEvents
On Error Resume Next
Me.Parent.txtLinkCons = Me.cons
End Sub

Yes, DoEvents helped. No more error messages. Thanks a lot.