Conditional format of conflicting record

[DocBrown]

I'm sure this has been asked and answered but can't find it here.

I have a continuous form where the user enters date, start and stop times. I
have code in the Form_BeforeUpdate event that validates that new or modified
records do not have overlapping dates/times with data in any other record in
the table. The code has access to the primary key of the filtered recordset
that is currently displayed in the form. So I know the primary key of the
record that is in conflict with the new/modified record.

Currently, I'm not displaying the primary key because it is not needed for
the user to know that. The form/query is sorted on date and time fields.

What I want to do is highlight the field, or whole record if that is easier,
of the record that is in conflict with the new data. I can't get this to
work. I think I have to use a combination of conditional formating based on a
'Expression Is' condition and using a bookmark to manage going to the
offending record, setting the condition and getting back to the current
record. Or Maybe setting a global condition true that is then picked up at
the proper time by the Conditional Formatting.

Is there an example of this or what would be the high level logic to do this?

Thanks in advance.
John

 

[Marshall Barton]

I have a continuous form where the user enters date, start and stop times. I
have code in the Form_BeforeUpdate event that validates that new or modified
records do not have overlapping dates/times with data in any other record in
the table. The code has access to the primary key of the filtered recordset
that is currently displayed in the form. So I know the primary key of the
record that is in conflict with the new/modified record.

Currently, I'm not displaying the primary key because it is not needed for
the user to know that. The form/query is sorted on date and time fields.

What I want to do is highlight the field, or whole record if that is easier,
of the record that is in conflict with the new data. I can't get this to
work. I think I have to use a combination of conditional formating based on a
'Expression Is' condition and using a bookmark to manage going to the
offending record, setting the condition and getting back to the current
record. Or Maybe setting a global condition true that is then picked up at
the proper time by the Conditional Formatting.

If you park the conflicting record's PK in a hidden text
box, it should be easy to use CF to highlight any text box
in the record.

You can also use the saved PK value in a FindFirst to make
that record current (but not in the form's BeforeUpdate
event):

With Me.RecordsetClone
.FinfFirst "PKfield=" & Me.txtConflictPK
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With

Are you certain that there can never be more than one
conflicting record?

If you only want to highlight any/all conflicting records,
then I think it might be possible to come up with a way to
use CF to do it all without any code.

 

[DocBrown]

Thank you! I got the highlighting working. I'm still tweeking it but using
the hidden text box works. This was much easier than I thought.

I added code in the BeforeUpdate as:
Me.txtBadRecID = !LogRecID

Then in conditional formatting for 'expression is' I put:
[txtBadRecID]=[LogRecID]

Yes, it is possible to have more than one record in conflict in certain
conditions. Since I'm dealing with overlapping times periods, I think I can
code to find the record that will contain the earliest or latest time as
needed to resolve the conflict.

Thanks for the point in the right direction

John