Solved

Highlight entire row (datasheet form) -- most previous change

Posted on 2004-09-28
30
783 Views
Last Modified: 2012-06-27
I was wondering if it's possible to highlight an entire row (form; datasheet view) in which a change occured during previous action.

Let's say I have the following records:

Name        Field_X    Field_Y    Total
Tom          50          5            32.0
Helen        20          10          16.0
John         24          4            16.0
Kathy        12          9            10.8
Don          14          3              9.6

The "Total" field is an expression based on the following calculation.
Total: CDbl(Nz([Field_X] * 0.6 + [Field_Y] * 0.4, 0))

More Info...
a. The records are sorted by "Total" field
b. If a change occurs in "Field_X" or "Field_Y", the form is requeried; hence the records would be resorted based on new values in the "Total" field.
c. In the actual database, I have more than just the 2 fields (X & Y).   I also have hundreds of records.   So, potentially, a records that is currently "ranked" in the e.g. 1st position may be re-ranked to e.g. "75" position (dending on what values were selected in X or Y).

Here's what I want to see:
1. Again, as listed under c., a record may be re-ranked.  At this time, it not easy to see as to how the change in X or Y has affected the record.
2. Therefore, it would be tremensdously helpful to highlight e.g. the record "Tom" (entire row in datasheet) if indeed I made a change in record "Tom" during the most previous action.   Then, I could scroll down, realize that record "Tom" has now a rank of "75".   As of right now, I would have to actually search for "Tom" ... which I don't want.


Additional info...
- if there's another alternative to "highlighting entire row", it certainly would be acceptable.   I'm not too hard on this... even though, I think it would be easy to recognize
- whatever approach is chosen, I want to make sure that performance won't be affected too much by much.  I recently learned that using the DCOUNT function has a tremendous (negative) impact on performance... [certainly, this is not to say that DCOUNT is the right approach].


Thanks,
Tom






0
Comment
Question by:TomBock2004
  • 14
  • 6
  • 5
  • +1
30 Comments
 
LVL 26

Expert Comment

by:dannywareham
Comment Utility
If you want to identify the latest entry, you'll need to have some means of identifying it.
Add a column to the table which captures when it was updated (use "=NOW()" without quotes)

You can then use MAX to return the latest value of that field.

This can them be used to identify it visually.
0
 

Author Comment

by:TomBock2004
Comment Utility
Great idea... okay, just a few points to follow up with.

- I added a DateTimeStamp field (data type = date)
- I added the DateTimeStamp field to form
- I added the following into the AfterUpdate event of the form:


Private Sub Form_AfterUpdate()
    Me.DateTimeStamp = Now()
End Sub


At this time, when changin e.g. "Name" from "Joe" to "Mike", the changed record will be updated with a current DST.  

However, I can't "leave" the record after the DST was changed.  

So, my questions are:
1. How do I make the change of DST so I can move onto the next record?
2. Using Conditional Formatting in the form, what does the expression look like to pull for "Max"?  
3. Would I apply the expression to every single field so that it appears the entire record is highlighted or is there a "smarter way" vs. applying the same formatting condition to every field?


Thanks,
Tom
0
 
LVL 26

Expert Comment

by:dannywareham
Comment Utility
One at a time...

>>1. How do I make the change of DST so I can move onto the next record?

You can't leave the record? How is your form bound?
Is it to the table, or to a query?

0
 

Author Comment

by:TomBock2004
Comment Utility
The DST field is linked to the table.

Tom
0
 
LVL 26

Expert Comment

by:dannywareham
Comment Utility
Is the form itself bound...?
You'll need to bind teh form and the recordsource of the control
0
 

Author Comment

by:TomBock2004
Comment Utility
Not sure how to verify this...  pls provide more details as to what values I need to set in what properties.

Thanks for chipping in,
Tom
0
 
LVL 26

Expert Comment

by:dannywareham
Comment Utility
Right click on the form (top left corner theres a small square)
Select properties
Go to tab "Data"
Check teh ControlSource - should be linked to your table or query
0
 

Author Comment

by:TomBock2004
Comment Utility
Yes, the "RecordSource" is linked to the table.

Again, DST updates fine but when moving cursor off record, the form is then locked.

Tom
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
Tom,

In datasheet view, you can't highlight an individual row - in fact, formatting options are *very* limited in this view, you can only apply a format to the entire datasheet. Would a continuous form be acceptable?

BTW, did you have a look back at your earlier question about the dynamic row number?
0
 

Author Comment

by:TomBock2004
Comment Utility
Shane:

Yes, I did... but isn't this a little bit different now.

I think the suggested idea to use an AfterUpdate event which changes the date and then to highlight "something" would be sufficient.  

So, as you suggested, if I can't highlight the entire row, then maybe a single (first) field would be okay.   As "dannywareham" mentioned earlier, couldn't I apply conditional formatting to the DST if MAX?

Do you think that's possible?  

If yes, how could I fix the current problem that I "can't leave" the record after the DST in the record was changed when moving the cursor off the changed record?

Tom
0
 
LVL 26

Expert Comment

by:dannywareham
Comment Utility
If this is just to "see" the latest record, you can put into a continuous form, Sort by The date field and use the code from   http://www.mvps.org/access/forms/frm0047.htm    to change the colour of the current record.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
> but isn't this a little bit different now

It is, I just wondered if you'd seen the other answer in relation to that question :-)

Can I just clarify our terminology - a datasheet form is one which looks and acts the same as a table or query when you open it. That is, you can move the columns around, shrink column and row sizes etc. A continuous form is one where the form shows all records in a table-type view, but the controls are all form-like - that is, you can't alter field positions or sizes. This is what the form wizard calls a "tabular form" (a little bit of a misnomer).

Conditional Formatting isn't possible in a datasheet form, but is in a continuous form.
0
 

Author Comment

by:TomBock2004
Comment Utility
Sorry, that won't work for me... well the users...

The requirement is to use the data in datasheet view, make a change to some factors (which will affect the order of the records), and see if the change was "too dramatic" or "is okay".  

As described earlier, this datasheet form will be utilized for prioritization of projects.   E.g. a record (project) might be the "top priority"; hence, it's ranked #1 in the data set.

Well, the top priority might change so a user will modify one of the factors which recalculates an Overall Score.   As a result of the change in overall score, a record currently listed as #1 might take on a postion as #75.  

Unfortunatetly, having it listed as #75 might be too dramatic.   So, an additional adjustment to the record should be made iot make it e.g. #15.  

To provide the user some visualization as to where the record "went" (after the change of a factor), I just want to highlight something to quickly find it again.

So, the idea to use the most recent date time stamp in conjunction w/ the MAX field plus COND FORMATTING for at least one field (e.g. Project Tracking number) would be perfect.

Does that make sense?

Tom
0
 

Author Comment

by:TomBock2004
Comment Utility
Okay, I see your point.... create a continous form that has the appearance of a datasheet.    Okay, I think that'll work.   Sorry for overlooking this suggestion.

So, assuming this approach, how can I solve the issue at hand... a) update the DST, b) use some means to highlight the record where DST is MAX?

Tom

0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
It does, but again, the important thing is that you can't use conditional formatting in datasheet view. A continous form will still allow you to sort the data and see the record position in the same way as a datasheet, you just can't reorder the columns or shrink/resize the columns and rows (you can still sort and filter).

Have you tried the datestamping code in the BeforeUpdate event of the form rather than the AfterUpdate? I think your code might be causing cascade AfterUpdate events to fire.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
Whoops, I cross posted - I hadn't seen your last post (I'm on a slow dial-up tonight). Try the BeforeUpdate and see if the DST updates.

The only way I can see to pull the Max is to use DMax - this might be slow, but I can give you a replacement SMax function :)
0
 

Author Comment

by:TomBock2004
Comment Utility
Okay, I'm working on the Continous Form so that it appears like a datasheet view.   Although not as "pretty", I can live w/ this (I hope the users too).

As you may remember from an earlier thread, I use 5 factors whose values drive the overall score (and the ranking of each project).

For the 5 fields, I use combo boxes so that only default values can be pulled.

Now, on the continuous form, I see the "drop-down button" on each field for each record... in my opinion, that doesn't "look good"....

Are there some properties for the combo box so that the small black triangle only appears when the record/field is selected?  

And yes, if you please could send me the code for the SMAX function so that I can highlight the record (or single field) where the most recent change occured.

Thanks, I appreciate it !!!

Tom




0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
> Are there some properties for the combo box so that the small black triangle only appears when the record/field is selected?

Unfortunately...no :(

Here's SMax:
Function SMax(strField As String, strTable As String) As Variant
Dim rst As DAO.Recordset

Set rst=CurrentDb.OpenRecordset("SELECT Max(" & strField & ") As MaxVal FROM " & strTable)
If Not rst.EOF Then
   SMax=MaxVal
Else
   SMax=Null
End If
rst.Close
Set rst=Nothing
End Function

SMax is used in the same way as DMax - for example, you could include something like this as a calculated row in your query:
MaxValue: SMax("[DateTimeStemp]","MyTable")=[DateTimeStamp]

Let me know if the BeforeUpdate event works to update the DST. To highlight the whole row, create a textbox set to Enabled=No, Locked=Yes. Send it to the back so that it's behind all other controls, and apply the conditional format to this textbox. For example, you could bind this textbox to the MaxValue field (above) and set it to change format when the MaxValue field is true.
0
 

Author Comment

by:TomBock2004
Comment Utility
Okay, a few more follow up questions:

Query/Module:

I created a new module and put the code in there as you suggested.   I did not make any changes to the code itself.   Save the module name as "LastedUpdate".

Then, I the query, I the SMax into another field & updated the field and table name.

At this time, I don't get any results... I would have thought to see all FALSE except 1 TRUE (for the one records that has the newest DST).

Form:
Since the SMax doesn't work for me at this time, I chose a temporary testing field for the conditional formatting.    I simply added a "Testing" field to the table/queries, entered value "X" into a the Testing field for a single record.

Then in conditional formatting, I applied that "if testing equals X make background color equal to yellow".  

Here's the problem now.... when I line up the Testing field, it covers up all data of the other fields (as one would expect).   For right now, I made the Testing field a bit wider and taller.   I "send it to back".   Then I centered the other data fields so that a can see just a bit from the testing field.   Now, this gives the appearance as if I had a yellow border for the record that has the "X".

Although this can be used, I'm still not too happy with the border... I'd rather see that the actual data field will be highlighted yellow.

So, again, the following question(s) remain:

1. What am I doing wrong that the SMax doesn't find a record based on the the DST?

2. Can I make it that all other data fields turn yellow based on the results of the SMax expression?


Thanks so much in advance again,
Tom

P.S.   I raised the points since this is a long thread... hope that's okay 8)




0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
You could make all of the foreground controls transparent - this would highlight the whole row at the expense of making the fields the same colour as the background - otherwise, you'll need to apply the conditional formatting individually to each control.

Does the SMax return an error? Can you post the expression that you are using?
0
 

Author Comment

by:TomBock2004
Comment Utility
Okay... I'll need to revisit the "highlighting" scenario later on tonight...

as far as the expression is concerned, I used the following:

MaxValue: SMax("[LastUpdate]","tbl01PrioritizationData")=[LastUpdate]

Again, above returns nothing.

Tom
0
 

Author Comment

by:TomBock2004
Comment Utility
Back to the query...

I use the "MaxValue: SMax("[LastUpdate]","tbl01PrioritizationData")=[LastUpdate]" which doesn't return any values.

For testing purposes, I took the SQL statement [SELECT Max(" & strField & ") As MaxVal FROM " & strTable] from the module and replaced it with: "SELECT Max([LastUpdate]) As MaxVal FROM tbl01PrioritizationData"

This expression returns the proper value... then I added another field (TestingNumber) and I got the following error:

"You tried to execute a query that does not include the specified expression 'TrackingNumber' as part of an aggregate function."

This is a simple SELECT query... no grouping is applied.

Any idea what the problem might be?

Tom
0
 
LVL 5

Expert Comment

by:kemp_a
Comment Utility
Depending on how many rows are in your table you may experience some performance hit, but this solution works:

First, ensure your DateTimeStamp field is indexed, this will dramatically increase speed of the query

Create a query called qryLastUpdated with this SQL:
SELECT TOP 1 DateTimeStamp
FROM WhateverYourTableIsCalled
ORDER BY DateTimeStamp DESC;

For each field, select Conditional Formatting pick "Expression is" and enter
[DateTimeStamp]=DFirst("DateTimeStamp","qyrLastUpdated") with whatever formatting you require.

Cheers
0
 

Author Comment

by:TomBock2004
Comment Utility
Great that works perfect... meanwhile I figured a small work-around... that results in another problem:

Let me recap what I'm trying to achieve:

**********************

I use a continuous form in which I want to track most recent changes.

Here's what I have:

Table:
- Bunch of fields
- Date field ("DateTimeStamp"; Format = General Date)

Query:
- Bunch of fields
- DateTimeStamp field
- Expression: "LastUpdate: IIf([DateTimeStamp]=DMax("[DateTimeStamp]","tbl01PrioritizationData"),"True","False")"

Form (Continuous form):
- Bunch of fields
- DateTimeStamp

Events:
- BeforeUpdate event... listed below

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.DateTimeStamp = Now()
    Me.Requery
End Sub


Procedure:
1. In the form, I change any of the fields.
2. BeforeUpdate event kicks in and puts current date-time-stamp into
DateTimeStamp field

Expected result (this is where I need some help with):
- After I made a change in any of the records, conditional formatting for all "bunch of fields" should kick in based on following expression:
- [LastUpdate]="True"
- This should turn all fields (basically entire row/record) into "green color"

Problem at this time:
1. W/o closing/reopen the form, it wouldn't see the record highlighted (green) where the latest change occurred
2. So, I put the "Me.Requery" into the BeforeUpdate event.
3. However, at this time, I get the following error:

"Run-Time Error '2115'.
The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Budget Management from saving the data in the field."

I tried to put the "Me.Requery" into a 2nd event (AfterUpdate).  This, however, gives me the following error:

"The expression Before Update you entered as the event property setting produced the following error:  Procedure declaration does not match description of event or procedure having the same name.


Again, the idea is to make a change in any field, see the DateTimeStamp updated, and then -- most importantly -- see the record that was changed turn "green" (background color).


Does anyone know how to fix either error?


Thanks so much






0
 
LVL 5

Expert Comment

by:kemp_a
Comment Utility
If you want to have the record set requeried after each field change then requery in the AfterUpdate of the field, thus:

Private Sub Value_X_AfterUpdate()
    Me.Total = CDbl(Nz([Value_X] * 0.6 + [Value_Y] * 0.4, 0))
    Me.Requery
End Sub
0
 
LVL 5

Expert Comment

by:kemp_a
Comment Utility
I created a form to emulate most of what you want, with the conditional formatting as I suggested above, the forms Record Source set as "SELECT UpdateTest.* FROM UpdateTest ORDER BY UpdateTest.Total DESC;" the only code in my form is:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.Last_Updated = Now
End Sub
Private Sub Value_X_AfterUpdate()
    Me.Total = CDbl(Nz([Value_X] * 0.6 + [Value_Y] * 0.4, 0))
    Me.Requery
End Sub
Private Sub Value_Y_AfterUpdate()
    Me.Total = CDbl(Nz([Value_X] * 0.6 + [Value_Y] * 0.4, 0))
    Me.Requery
End Sub

This updates the form after either Value_X or Y is changed, sorts it in descending Total and applies the conditional formatting.

So I think move your Me.Requery to the fields that you are allowing updates on, then it should work.
0
 
LVL 5

Accepted Solution

by:
kemp_a earned 500 total points
Comment Utility
Also, for your query which I assume is the Record Source for you Form, change it to use the same as the conditional formatting, it will execute faster as it will only be hitting the index to get the data rather than ploughing through the table.

Query:
- Bunch of fields
- DateTimeStamp field
- Expression: "LastUpdate: IIf([DateTimeStamp]=DFirst("DateTimeStamp","qyrLastUpdated")),"True","False")"
0
 

Author Comment

by:TomBock2004
Comment Utility
kemp a:

Thanks for all the info...  using your suggested function would require lots of rework on my end... I got a bunch of other functions that I didn't even mention in this thread.

At this moment, I'm trying to find an answer to my thread "Comment from TomBock2004
Date: 09/28/2004 08:48PM PDT"...

would you happen to know as to why I get the
a) Run-Time Error '2115' or
b) "The expression Before Update you entered as the event property setting produced the following error:  Procedure declaration does not match description of event or procedure having the same name.

Tom
0
 
LVL 5

Expert Comment

by:kemp_a
Comment Utility
Let me recap what I believe your trying to achieve:
**********************
You use a continuous form in which you want to track the last change.

Here's what you have:
Table:
- Bunch of fields, which includes fields called:
Total; Format=Number/Double which contains the result of your calculation, your example was CDbl(Nz([Value_X] * 0.6 + [Value_Y] * 0.4, 0))
DateTimeStamp; Format = General Date; Indexed

A Query called qryLastUpdatedOrWhatever with SQL (which will always return 1 field called DateTimeStamp containing the last date/time a record was modified):
SELECT TOP 1 DateTimeStamp
FROM WhateverYourTableIsCalled
ORDER BY DateTimeStamp DESC;

A second query, called Query2:
- Bunch of fields from the previously mentioned table, including the Total & DateTimeStamp fields, plus
- a calculated field which is: "LastUpdate: IIf([DateTimeStamp]=DFirst("DateTimeStamp","qryLastUpdatedOrWhatever")),"True","False")"
(You dont need this field by the way and it will slow you down!)

A Form bound to Query2 (set as Continuous form):
- Bunch of fields bound to fields from Query2, which includes both the Total & DateTimeStamp fields (& LastUpdate field which is not needed!),
- Each field on the form has 'Conditional Formatting' thus:
"Expression is" [DateTimeStamp]=DFirst("DateTimeStamp","qyrLastUpdated") and whatever formatting you require.

The Form has Bound Events:
- Form_BeforeUpdate event which is:
Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.DateTimeStamp = Now()
End Sub

Each field that you want to use to calculate the total/requery the recordset, you have a bound subroutine that does the following
Private Sub Field_Name_AfterUpdate()
    Me.Total = WhateverYourFormulaOrFunctionIs ' Your example was CDbl(Nz([Value_X] * 0.6 + [Value_Y] * 0.4, 0))
    Me.Requery
End Sub

Expected result:
- After a change in any of the fields containing the Field_Name_AfterUpdate sub, the form executes the Field_Name_AfterUpdate subroutine, which updates the Total field. Then requests the form to Requery, forcing the Form_BeforeUpdate subroutine, updating the DateTimeStamp, prior to saving the record. The form requeries the recordset applying the conditional formatting turning all specified fields (basically entire row/record) into "green color"

You need to have the bound Field_Name_AfterUpdate() subroutines on the form for the recordset to requery after updating a field, if you dont then you will have to have the user press the [Tab] key until you get to the next record, which will then force the Form_BeforeUpdate & requery the recordset automatically.

As to your errors:
would you happen to know as to why I get the
a) Run-Time Error '2115': It would not be inadvisable to try to Requery the recordset while in the Form_BeforeUpdate as it will loose any changes made, this is causing the error. We're in Form_[BeforeUpdate] remember so dont try to Requery.
b) "The expression Before Update you entered as the event property setting produced the following error:  Procedure declaration does not match description of event or procedure having the same name": Your declaration of the subroutine is wrong, probably from trying to get his stuff working, ensure that the declaration for the subroutine is:

Private Sub Form_BeforeUpdate(Cancel as Integer)

Check this out for clarification: http://support.microsoft.com/default.aspx?scid=kb;en-us;286595

I'm sure you have lots of other functions, and validation and code, and database lookups etc etc what you need to do is incorporate this feature into what you already have, once you see it working you'll be really impressed how easy it is to achieve.

Perhaps what you need to do is create a new form to play with as outlined above (and on previous posts), so that it doesn't interfere with your existing form/code. A simple, non complicated form. That way you will get a feel for what is going on. Then incorporate the functionality of the simple form into your existing form. I find doing things that way really makes me understand what is going on, and when, which is sometimes really difficult to comprehend in Access forms!

Have fun!
0
 

Author Comment

by:TomBock2004
Comment Utility
Thanks for your very detailed information.... this is really great stuff... I'll attempt to incorporate this and "have fun" as I go along.

Again, thanks, I appreciate your help in this matter.

Tom
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

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…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

771 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