Solved

Access Highlight Current Record -- Nice and easy

Posted on 2013-01-11
23
2,869 Views
Last Modified: 2016-08-13
Hi

I am using Access 2010.  I am using a continuous form.

I was doing some research on how to highlight the current record in a continuous form.  I found several solutions that were close --- but no cigar!

I found (for me) a perfect solution.....  but it needs refining.

I came up with the idea of creating a boolean field in the underlying table...   Field name is ................   [IsCurrentRecord]..    The idea is that this field will be "True" for a record when the recordset.currentrecord  points to that record.



Now,  some coding for the form
Private Sub Form_Current()
                    [IsCurrentRecord]   =    True
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
                      [IsCurrentRecord]   =    False
End Sub

Conditional formatting on AnyField  -----------   If [IsCurrentRecord] = True  <then color background>



This works perfectly for me.  It highlights the current record.  It even highlights newly created records!   It's perfect except for one caveat.

The record becomes dirty  (edit mode in case I have the lingo wrong) even though the user really is not wanting to edit the record.  Instead,  I would like the following behaviour.

A)  When I land on this record I wish to set [IsCurrentRecord] to true  and then  save the record (to get rid of the Edit Flag).  

B)   When I exit this record, then  I want to set  [IsCurrentRecord] to false and again save the record.  If the focus goes on another record,  go back to "A"
0
Comment
Question by:peispud
  • 11
  • 6
  • 4
  • +2
23 Comments
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
Well, you actually don't need to set a field in your table if you a field that identifies each row as unique.  For instance, if you have a field in your table called CustomerID, just add a textbox to the header of your subform called txtCurrentRecord, then in the subform's OnCurrent event, you would add:
Me.txtCurrentRecord = CustomerID

Then add a wide textbox  and put it behind your fields with its background color set to the background color of your subform.   Set it's Enabled property to False and it's locked property to True.  Then add conditional formatting to this textbox using Expression Is
[CustomerID]=[txtCurrentRecord]

Doing it this way, you don't have to worry about the record being in Edit mode.
1
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 125 total points
Comment Utility
Try this:
Table a(aID Text, f1, f2)

An unbound control,
Name: txtCurrentRecord
Visible: No
stores the value of CurrrentRecord + primaryKey value in Form_current event

Private Sub Form_Current()
   txtCurrentRecord = CurrentRecord + CInt(Nz(aID, 0))
End Sub

Open in new window

Condtional Formatting for all fields in record (select all fields and Format-Condtional Formatting: Expression Is txtCurrentrecord-CInt(aid)=CurrentRecord
Fill color: Red
Q27993007-Hilight-current.accdb
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
@hnasr,
I'm not sure why you need to include CurrentRecord when you have a primary key.  Setting txtCurrentRecord to [aID] is all that is needed on the OnCurrent event.  Then the Conditional Formatting expression would be just [aID]=[txtCurrentRecord].

Ron
0
 

Author Comment

by:peispud
Comment Utility
Thank you for the excellent replies.

I am leaning on Hnasr's reply.  It works perfectly,  but I would like to highlight the row when a new record is being created (in edit mode).   I'm wondering??
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 375 total points
Comment Utility
Add an invisible textbox to your form's header and call it txtCurrentRecord. In the OnCurrent event, add Me.txtCurrentRecord = [NameOfKeyField].

Use Expression Is in conditional formatting with this:
([txtCurrentRecord] = [NameOfKeyField])) OR [NewRecord]
0
 

Author Comment

by:peispud
Comment Utility
Analysis : Adding the additional condition affects every record when it executes.

Here is what i did.

I've added a 2nd textbox  --- txtbox2.   I modified the Form_Current() as follows.

Private Sub Form_Current()
    txtCurrentRecord = CurrentRecord + CInt(Nz([ID], 0))
    Me.txtbox2 = [ID]
End Sub


I've modified the conditions for the highlighbting textbox as follows.

Condition 1  ---  txtCurrentrecord-CInt(id)=CurrentRecord   ---- Highlighting textbox goes orange
Condition 2  ---  ([txtbox2]=[ID]) Or [NewRecord]    ---- Highlighting textbox goes green



Both conditions execute properly, but when condition 2 occurs,  every record in the form turns green.

I've included a short demo database for illustration.
Highligint-Current-Record.accdb
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
Where you wanting a different color for new records?  If so, then this is all you need in your OnCurrent event:
Me.txtCurrentRecord = ID

Open in new window

And your Condition1 should be:
[txtCurrentRecord]=[ID]

Your Condition2 with a different  color should be:
[NewRecord]
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
FWIW Stephen Lebans has something the may be of interest:

http://www.lebans.com/conditionalformatting.htm
0
 

Author Comment

by:peispud
Comment Utility
Followed IrogSinta's instructions ... (see attachment)

Effect is the same..  When the condition for NewRecord occurs, everything lights up.

I really only want the row in focus to light up in my continious form.  I hope that Access makes this easier in their next version.
Highlight-Current-2-Record.accdb
0
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 375 total points
Comment Utility
Try IsNull(ID) And [NewRecord] for Condition2
0
 

Author Comment

by:peispud
Comment Utility
Sweet!

Very very close.    I find this very agreeable.  I have included a pic of what the screen looks like when the user is entering a new record.   I would have preferred that the highlighting would happen over the row which is currently in the edit mode.  If you can pull that rabbit out of the hat,  I would be delighted.

Either way,  I thank you.     I will be adding this question to the knowledgebase.  But first, I will wait to see if you make this even better.


For others doing research.

 Form prep
1) Form is Continuous
2) Form has invisible textbox called txtCurrentRecord in header
3) Form has a textbox the full width of the combined fields in the  detail section. This textbox is "sent to back".  (zorder)
4)  Data fields in detail..... Back Style  = Transparent

For the large textbox in the detail section
Condition 1  :   [txtCurrentRecord]=[ID]                        <select fill color>
Condition 2  :   IsNull([ID]) And [NewRecord]                <select fill color>



Private Sub Form_Current()
    Me.txtCurrentRecord = ID
End Sub
Almost-there.JPG
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
I'll get back with you tonight when I get home.
0
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 375 total points
Comment Utility
Unfortunately, highlighting a new record when it is being edited would also highlight the last row for entering a new record.  There is a way to go around this but it involves a lot more code than you probably would want.  It would be simpler instead to have a form or subform used to add new records.  If you go that route, you just need to prevent adding new records in the continuous form by setting the form's AllowAdditions property to false.
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
@IrogSinta
Thanks for the info. Not sure why I used this, but looks like there was a reason.

Try this version:
Private Sub Form_Current()
    txtCurrentRecord = CInt(Nz(aID, 0))
End Sub


Conditional formatting,
Expression Is [txtCurrentrecord]=Nz([aid],0)
Q27993007-Highlight-current-2.accdb
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
@hnasr
This still ends up with the same problem.  It's okay until you try to edit the new record, then you get 2 highlighted rows as you can see here:

Ron

highlights
0
 

Author Closing Comment

by:peispud
Comment Utility
Thank you.

I appreciate your help.  I expect others do as well.
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
I did not like the last part of highlighting the last 2 records when adding a new record.
I spent sometime to find a way because many questions around asking for such a requirement.

I'll hope to post a reply if a new relevant question is asked. The idea is to have a bound field to store the CurrentRecord value.
0
 

Author Comment

by:peispud
Comment Utility
I would like that.
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Open a new related question starting from your last post with the image. Ask to remove the last highlighting when inserting a new record in the continuous form.

Posting here is irrelevant because answer seekers won't read all posts, they just ,in general, navigate  to accepted and assisted solutions.
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
This ended up not being as involved as I thought it would, and it looks like hnasr has found a solution as well.  As hnasr said, open a new question and we'll both post our solutions.  They may both be the same or if not, it would be good to see the various methods to make this work.
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
@hnasr,
Just in case you might have overlooked it, the OP opened a followup question here:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27993988.html

Ron
0
 

Expert Comment

by:Member_2_7971753
Comment Utility
I realize this is an old thread, too late for another question?

On a continuous form (does not allow new records, so no issue there), IrogSinta's approach to highlighting the entire row of the current record works like a charm, except for an ugly-looking problem I had: when moving to a new record, the form seems to requery, although there's no me.refresh / paint or anything like that in the code.  If I remove the wide text box with the conditional format, or its conditional format, this problem goes away (as does, of course the highlighting of the record).  I found discussion of a problem like this, see link below, but found no help there. I'd be grateful for any thoughts.
Thanks, Rick


http://www.access-programmers.co.uk/forums/showthread.php?t=211223
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
You really should create a new thread with your question and just refer to this thread for reference.  Then if you wanted to get the experts here involved, you could add a post here with a link to your new question.

Ron
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now