Conditional formatting based on the values in another table (2 Viewers)

[lbs]

Hello
I would appreciate any advice. I have a form showing dates and within it several subforms showing data from other child tables all related by dates.

When I write a rota, I am struggling with errors like I allocate someone for an activity when they are already are allocated to another activity on the same day, or they will be away shorty after that and will not be able to finish activity properly.
So what I want is to highlight a consultant in Activity register if they already have had MDT on the same day or PM on the same day or if they will be absent on the next day or day after next day or 2 days after next day.
Is it possible at all?

 

[bob fitz]

Why don't you write some code to validate the data being entered. The forms Before Update event is the best place for the code because this event always fires before a record is saved and the update can be canceled if the validation fails.

 

[Uncle Gizmo]

When I write a rota, I am struggling with errors like I allocate someone for an activity when they are already are allocated to another activity on the same day

I suspect a unique index in the table would prevent this.

See my blog here:-

Prevent Duplicates - Nifty Access

Prevent Duplicates When entering data into a subform, particularly when you select The data from a combobox, then you probably don't want the user to be able to select the same item twice. You may want to count the various entries at some stage and having duplicates would just make this...

www.niftyaccess.com

 

[Uncle Gizmo]

Actually, I don't know what I was thinking, rereading my post I realise it don't make sense!

However, I know I've answered a similar question in the past, will post a link when I find it ....

 

[lbs]

Why don't you write some code to validate the data being entered. The forms Before Update event is the best place for the code because this event always fires before a record is saved and the update can be canceled if the validation fails.

Because I don't want access to prevent validation. There will be situations when we are short of staff and I will need to allocate two activities on the same day. I just want this is highlighted and then make my own decision keep it or change it.
For the same reason unique indexes will not help me.

 

[bob fitz]

Because I don't want access to prevent validation. There will be situations when we are short of staff and I will need to allocate two activities on the same day. I just want this is highlighted and then make my own decision keep it or change it.
For the same reason unique indexes will not help me.

You wouldn't have to prevent the record from being saved. When the validation fails, you could use a message box to alert the user and allow them to decide if saving the record is appropriate.

 

[lbs]

You wouldn't have to prevent the record from being saved. When the validation fails, you could use a message box to alert the user and allow them to decide if saving the record is appropriate.

But the thing the data is in different tables. For example, I allocated 1 Cutup to TM on 28/12. And made sure she is not allocated anything else on that day. But later, I had to swap MDTs (data stored in different table) so that TM got Gynae MDT. I don't want it and it happened only because I missed that TM has already got 1 Cutup. But the record in Activities register was saved before.

 

[bob fitz]

But the thing the data is in different tables. For example, I allocated 1 Cutup to TM on 28/12. And made sure she is not allocated anything else on that day. But later, I had to swap MDTs (data stored in different table) so that TM got Gynae MDT. I don't want it and it happened only because I missed that TM has already got 1 Cutup. But the record in Activities register was saved before.

Then you would need to use validation code in the other form as well. You would use something like DLookup() or DCount() functions to check for the existence of data in another table.

I'd be very happy to help you with this if you're not familiar with this kind of validation.

 

[lbs]

Then you would need to use validation code in the other form as well. You would use something like DLookup() or DCount() functions to check for the existence of data in another table.

I'd be very happy to help you with this if you're not familiar with this kind of validation.

You are absolutely right. It's too complex for me. I will read about DLookup () and Count(). I'd very much appreciate if you show me how to do this in one form so that I can do it in the others.

 

[bob fitz]

Post a copy of your db with a couple ficticious records

 

[lbs]

Post a copy of your db with a couple ficticious records

 

[bob fitz]

Can you confirm that the Main form is "Activities" and that we are looking to alert a user when is added to the forms "Activities Register" and "MTDs on the Day".

 

[lbs]

Can you confirm that the Main form is "Activities" and that we are looking to alert a user when is added to the forms "Activities Register" and "MTDs on the Day".

Actually Activities, MDTs, PMs are quite similar in hierarchy. First I usually allocate MDTs, then PMs, then Activities. But later I can edit records of any of them and I wanted to be prompted if there is an overlap.

 

[bob fitz]

Actually Activities, MDTs, PMs are quite similar in hierarchy. First I usually allocate MDTs, then PMs, then Activities. But later I can edit records of any of them and I wanted to be prompted if there is an overlap.

OK. I'm about to cook dinner now, but I'll deal with this first thing in the morning

 

[lbs]

OK. I'm about to cook dinner now, but I'll deal with this first thing in the morning

That's great!

 

[MajP]

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])

 

[bob fitz]

lbs

Take a look at the attached db.
I have created a function in Module1 called “fnConIsUsed” which takes two parameters (Consultant and Date) and then returns True if the consultant being entered has already been used in form “Activities Register” or has been used in form "MDTs on the Day".

fnConIsUsed is used In the Before Update event of the form “Activities Register” to determine whether or not the consultant has been used and if the consultant has been used then the code asks the user to confirm that it’s OK to continue. If the user declines then the update is cancelled.

If you need any further explanation or help please post back.

 

[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])

Many thanks MajP. It works! Now I will write similar functions to use it when I allocate MDTs and PMs.

 

[lbs]

lbs

Take a look at the attached db.
I have created a function in Module1 called “fnConIsUsed” which takes two parameters (Consultant and Date) and then returns True if the consultant being entered has already been used in form “Activities Register” or has been used in form "MDTs on the Day".

fnConIsUsed is used In the Before Update event of the form “Activities Register” to determine whether or not the consultant has been used and if the consultant has been used then the code asks the user to confirm that it’s OK to continue. If the user declines then the update is cancelled.

If you need any further explanation or help please post back.

Thanks a lot Bib Fitz. The messaging system works. The only thing is it shows a message when it should not. I understand you coded that a message displays when a consultant is allocated some activities on another day. Actually I meant that there was no overlap of Activities with MDTs or PMs on the same day and activities were not followed by absence during next three days after Activity.

 

[MajP]

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