How to highlight newly added record/field on a continuous form in Access?

Background: I have a continuous subform (certification) displaying certification data for the selected resident on the form (fResidentMain).  My goal is to highlight a field on the subform (effective date field) for the selected resident on the form If the record for that selected resident is newly added to certification table.  Any records added to the certification table which the subform is pulling from via query needs to be highlighted as long as those records are newly added after loading database.  Data load is achieved through an automated process pulling from an Excel file to populate tables.  Attached file displays the snapshot of the form.

Question: How do I highlight the "effective date" field on the subform under Certifications tab to help the user paying immediate attention as the newly added certification info on a selected resident? Also, for the next load highlighted records should be un-highlighted for the new records with the next load.    

Thank you!
Ray ErdenBusiness Systems AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
I normally have a date field the default to Now()  when a record is added. I use conditional formatting to highlight fields based on the date.

How do you know the records is new? You may be able to use conditional formatting.

See attached example, It will high the name of people added in the
>= Date() -1
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Outside of conditional formatting, there is one other "old school" technique, but if your needs are simple (which it sounds like they are), then go with Boyd's suggestion of using conditional formatting.

Note that you need to have a bound field for this to work in a continuous form.

Ray ErdenBusiness Systems AnalystAuthor Commented:
Attached is my sample Access file with a table and a form.  Take for example ResID=641, there are three records by this ResID on the table and assume that new entries are added to the table with the next db load and one of them happens to be for the same 641 ResID but it will have a different effective date value.  Each time a record is entered to the table for the same ResID it will have a different effective date.  Since the fourth record is added to the table for 641, the effective date field for this record needs to be highlighted on the form as it will be different than the ones loaded during the previous updates.  By the same token when the next data load is made and assume that the fifth record is added again for the ResID 641(not every update loads every ResID each time) with a different effective date then in this case the previous highlight should disappear and the new effective date field for the this fifth record should be highlighted again on the form.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
I have a continuous subform (certification) displaying certification data for the selected resident on the form (fResidentMain).
Your example that you posted was missing the main/parant form. It only had the sub form.

Are you wanting to  always highlight the record with the newest effective date?

Another way to look at it,
If you where to sore the records in descending order by effective date it would be the first (top) record. That  is the record that needs highlighted?

Will there eve be a time when none of the records would need to be highlighted?
Ray ErdenBusiness Systems AnalystAuthor Commented:
I sent the subform only thinking that the effective date needs to be highlighted here on this subform only and on the parent form once the corresponding record is selected then the subform would show that highlighted record also.  I can resend the sample file this time with the parent form too if you need.  

Yes I need to highlight the newest effective date for the particular ResID, like for 641 in my example. Once the database gets updated and if a new record is added for this particular ResID than the effective date field of this record needs to be highlighted.  In other word for any given ResID only the newest effective date should be highlighted as you put above.  As long as this condition holds i.e. the newest record only is always highlighted for the selected ResID after loading the tables I am good for now. It can remain highlighted until the next load.  Record might be de-highlighted once the user is done working on it maybe with a click of a button but at the moment it is not my focus.
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Record might be de-highlighted once the user is done working on it maybe with a click of a button but at the moment it is not my focus.
With what you are asking to do, "de-highlighting" will happen only when a record for that  ResID is imported.

Maybe  de-highlighting should be more in your focus. That way you do not waste all the effort you do no just to highlight when it will not let you reach your ultimate goal..  

If this were my project i have had the record highlighted because it has not been processed.

IMHO, what is a better method would be to highlighted the record based on some trigger that the record has not been processed to completion.

Processing status - highlight color:
Open - green
Pending - yellow
Completed - white (no color)

Using this type of method you have complete control over the highlighting.  By also added a field for status changed date you can get some useful management reports,  

My 2 cent:
From 30+ years managing data entry departments, training data entry people, and designing data entry forms I find that once the user gets used to things highlighted for them they get dependent on it.  In your case what happens is an import gets done before the records get processed. Only the last record is highlighted. The user may skip the other record. Sure it maybe should not happen but as a developer you must be prepared for anything that could go wrong..
Ray ErdenBusiness Systems AnalystAuthor Commented:
I agree with highlight vs. de-highlight scenarios covering all possible situations.  Also, the way I structured my question implies that highlighted effective date field will remain in a highlighted status until the next import or data load as well.

I will understand how the user wants to go about that such as clicking on a button indicating that the record has been processed to completion then de-highlight or when the next import is done all highlighted fields lose color before the load or something to that effect This remains subject to further communication with users.  At the moment I need to achieve the goal of highligting the effective date field per described conditions above.  

Reattached my sample file and added two test records for ResID 641 and 593 with effective date 3/26/2015. So the question is - How do I highlight those effective date fields for those ResIDs on the form since they are imported with the most current data load hence they are the new effective date fields now?

Thank you for help
Ray ErdenBusiness Systems AnalystAuthor Commented:
More simply put any records that are added to the table "Certification" for a given ResID will have a new effective date for that ResID.  In this case it would suffice to highlight any new record on the form.   Not every single ResID would be updated every single load.  Highlighted records can stay in this status on the form until the next table update.  Once the next update is done all the exisiting highlights on the form should lose color and the new records should get the highlight on the form for the applicable ResIDs.
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Question:  How do I highlight the "effective date" field on the subform under Certifications tab ....
Reattached my sample file and added two test records for ResID 641 and 593 with effective date 3/26/2015

You forgot the parant form with the  Certifications tab again.

There are several ways to tackle this.  The more detail you provide about your database design the better I can develop
 a solution you will understand be able to reuse.  

I will create a solution that will work on the form you provided as a main form that shows all records.  This may not be the best way to do it on your actual when the form is used as a sub form.  

Note: The way I am proposing it would calculate the most recent effective ate each time and highlight it.  You method are proposing  does not hot define any de-highlight scenarios.
Ray ErdenBusiness Systems AnalystAuthor Commented:
This is certainly in the right direction based on what I provided.  First thing Monday morning I will prepare and send a sample parent form along with its associated tables.  I need to define the logic yet for de-highlight condition as well and will send that one too.  Also, tried to change the light blue color to red for the Effective Date field but it keeps showing the same light blue color.  Played with the backcolor code for the MaxEffectDate box but failed to get it to impact  the Effective Date box backcolor.

Thank you for your help on this!
Ray ErdenBusiness Systems AnalystAuthor Commented:
Please ignore the part of my question on color. Changed the back color to red for the effective date field. My bad.
Ray ErdenBusiness Systems AnalystAuthor Commented:
Status: On the attached sample file you will find the parent form (fResidentMain) with its associated sample sized tables.  The Certification subform works the way I want  within its parent form on the sample file.  I modified the query so it is limited to 2015 records but this is very temporary and will be removed going forward.  

Issue: The problem that I found when I applied the sample setup to actual database is that subform does not run and produces error.  My thought was that if there is a ResID with the same effective date on the Certification table then query errors out.  Experimented with the sample table and entered a record with the same effective date for the same ResID and ended up with the same error without the same efefctive date it runs ok.  

Then I ran the subform with a bigger sample size this time including a record with a ResID with the same effective date. ResID 1543 has the same effective date (6/1/2004) which I thought might be causing this issue and ran the Copy of Certification Subform but it worked without any errors . So at the moment I am not sure what is causing this error.

You can see this problem by running the Copy of Certification Subform_All attached to actual table with full data, it will error out saying "At most one record can be returned by this subquery" then all the fields will show #Name?.  

De-highlight Rule: One of the ways that I am thinking about is having the user clicking on a button on the form to indicate that the record is completed. That action would check a checkbox for example for that record on the source query and based on that checkbox the highlight loses color in the effective date field.  This is only what I am thinking but you might comeup with a better method of course. So we can consider that all the exisiting records on the table are in completed status to begin with hence no highlight is needed in the current state of the database but a new record for any given ResID coming in with the next update/import should be highlighted as it will have a new effective date for that ResdID.  Once the user processes that ResID to its completion then she can click on a button and effective date field loses color.
Ray ErdenBusiness Systems AnalystAuthor Commented:
As a follow up to the above post I have attached the sample file with modified test form "Dupscertification subform_All".  When opened this form produces the error "at most one value can be returned".  I created a list of ResID values with multiple effective dates on the text file attachment also.  What I have found is that some of those ResIDKey values even with multiple dates work on the form and some don't.  You can see those  ResIDKey values with multiple effective dates  and their subset showing only those ResIDKey values that do not work on the form hence generating an error.  I could not find what is causing the form working with some ResIDs but not with the others.  Hope this helps some in the process.
Ray ErdenBusiness Systems AnalystAuthor Commented:
One additional thought here hoping that it can simplify the problem some , I can ignore showing those with identical effective dates.  Out of total of 2781 ResIDkey values on the table (Certification3) there are 109 of them with the same effective date and some of them work on the form and some dont for the reasons beyond me.  In this case I consider bypassing any ResIDKey with identical effective date and skip that record on the form.  That way the highllight logic should work without an error.
Ray ErdenBusiness Systems AnalystAuthor Commented:
To better isolate the current  issue and illustrate the holdup I have attached the same sample file again but this time with a new form "Test subform" and a new table"TestCerts" where the form is linked to.  I used two representative ResID values for this problem and those are 2523 and 2349 on TestCerts table.  Both have identical effective dates (2523 has 5/31/2013 and 2349 has 6/1/2004).

With ResID 2349 the form/query works whereas with 2523 it doesn't work and it produces the error mesage "at most one value can be returned".  After I delete one record from the table for the ResID 2523 with one of the identical dates then and only then the form works.  I tried the same experiment with several other sample key values representing the similar condition and it always works only after deletion of the record with one of the identical effective date values.  This is where I hit the wall.  I could not spot why with one of the ResID values with an identical effective dates the form works but not with the other key.  How can I address this issue on the query? Or can I address it?  

Also two more tables are addded, one called "ResIDKeyvaluesworking" and the other one is "NonworkinResIDkey" to display those ResIDs with identical effective dates.  As table names suggest first table shows those key values with identical effective dates yet working on the form and the second table is for the ones also with identical dates but not working on the form.  

I would much appreciate some insight into this problem.  Thank you!
Ray ErdenBusiness Systems AnalystAuthor Commented:
Error Resolved! By modifying the query that populates the MaxEffectDate field I was able to eliminate the error described in my previous post.

Below is the modified and working query:
To test this query TestCerts table and Test subform are used.

SELECT TestCerts.CertStatus, TestCerts.EffectiveDate, TestCerts.CRENT, TestCerts.UA, TestCerts.RA, TestCerts.TPR, TestCerts.CertKey, TestCerts.AdmIDKey, TestCerts.ResIDKey, [EARNED]+[SELF]+[FARM]+[INTEREST]+[SS]+[PA]+[RETIRE]+[CS]+[OTHER]+[ADJUST] AS AdjInc, (SELECT TOP 1 M.EffectiveDate FROM TestCerts as M  WHERE (((M.ResIDKey)=TestCerts.ResIDKey)) ORDER BY M.CertKey DESC;) AS MaxEffectDate
FROM TestCerts
ORDER BY TestCerts.EffectiveDate DESC;

What is changed here from the original statement is that it is ordering by on the CertKey field instead of on the Effective Date field.  Tested this SQL successfully on the form that uses the entire table for its select statement also without any errors or wrong results.

Since this problem is out of the way now, could you please help me with the de-highlight logic per 3/30 post?

Thank you!
Ray ErdenBusiness Systems AnalystAuthor Commented:
Question on de-highlight process:  

On Certification subform I came up with a setup which is working for what I need but need more automation.  Red highlights are working ok for effective date fields for those ResIDKey values complying with the logic.  I added toggle button and process status buttons.  Once the user is done working with a highlighted record she can click on the Process Status button to de-highlight the completed record.  To achieve this I used an update query with parameter running from this button.  User has to enter the CertKey to update then the checkbox for this CertKey gets checked on table indicating it is processed.  The form has to be closed and opened to show its impact for the color.  There is another record with blue highlighted toggle button for the record without red highlight.  It is for a record not new anymore after the current update but still needs to be worked on as it is still unchecked.

What I want to achieve here is that once the user clicks on the Process Status button for the record completed query should automatically supply the CertKey value for the parameter and update (i.e. check the checkbox on table to show complete status).  Also after this is done can the user see the impact without closing and opening the form?

Attached Sample File:
The same sample file has been attached here and the objects that need to be used for this question are,
TestCerts table
qHighlight query
qProcessStatus query
Test subform form
Ray ErdenBusiness Systems AnalystAuthor Commented:
If a solution offered handles the above described automation in question section I will consider this entire post as "resolved". Thank you!
Ray ErdenBusiness Systems AnalystAuthor Commented:
Hello Boyd, do you think you will be available to help me with this question?  Like I mentioned the question that I posted as of April 3rd is the final form of my problem and the solution to that particular question will bring the whole thing to its closure.  Thank you!
Helen FeddemaCommented:
See this Access Archon article for a way to highlight the current record in continuous forms type subform:

Here is the form:

Highlight current row
it is not exactly what you want, but you might be able to modify it for your needs.

For highlighting rows in a datasheet subform based on criteria, see this article:

Colorizing datasheet rows
Ray ErdenBusiness Systems AnalystAuthor Commented:
Thank you for the above links, they contain very useful info that I can use for similar problems but to narrow the focus on the core problem I have attached the same sample file that I used on my 4/3 posting and all it needs is that when you open the form "Test Subform" which uses "Test Certs" table you will see a record added today (4/20/2015) as the new record and on the form it shows that record's effective date field in red since it is newly added. So this is working the way I want no need to do any more work on that part.  

What  am trying to accomplish On the form for this record you will see a little blue square button telling the user that he/she needs to click on the Process Status button next to blue button if this record is processed completely.  This button via an update query removes the color indicating that the user is done with processing the record.  

The mechanics of what I need - Once the Process Status button is clicked it activates the update query and the parameter asks for the CertKey.  User needs to manually supply this value, I would like to have this part to be automated so once the button is clicked Access can insert this CertKey value for the query to update.  Overall functionality wise the way it works is just fine with me and this auto insertion of the CertKey value is the only thing left for me to complete.  After the changes are done user needs to close the form and open it again to see the changes take effect, is it possible to see the chanegs without doing that?  Please find the attached sample file thank you!
Ray ErdenBusiness Systems AnalystAuthor Commented:
For attention of the experts!!!

If you could ONLY look at my 4/20 posting and tackle the problem described there this question will be in completely resolved status.  

Rest of the tread can be safely ignored.  Again the main ask is to have Access supplying the CertKey value in the update query instead of entering the certKey value manually.
Helen FeddemaCommented:
This code will do it (replacing the macro and update query):

Private Sub ProcessStatus_Click()
'Created by Helen Feddema 21-Apr-2015
'Last modified by Helen Feddema 21-Apr-2015

On Error GoTo ErrorHandler

   Dim lngCertKey As Long
   Dim strSQL As String
   lngCertKey = Nz(Me![CertKey])
   strSQL = "UPDATE qHighlight SET qHighlight.IsCurrentRecord = Yes " _
      & "WHERE qHighlight.CertKey = " & lngCertKey & ";"
   Debug.Print "SQL string:  " & strSQL
   CurrentDb.Execute strSQL, dbFailOnError
   Exit Sub

   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

Helen FeddemaCommented:
Here is the modified database.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray ErdenBusiness Systems AnalystAuthor Commented:
Helen -  You have solved my problem and thank you very much for your help, this has been the exact solution that I have been expecting and it works awesome.  Much appreciated.
Ray ErdenBusiness Systems AnalystAuthor Commented:
You rock!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.