Conditional Format append and updated records

[Stingone]

Hi everyone,

I'd like to know how to use conditional formatting on records that have been append and updates through a update and append query.

What i would like to accomplish is make all append and updated records green so that people can see what has been added/updated.

Regards Tony.

 

[Minty]

Assuming this is all done on one form and you have either a primary key, or a datetime stamp on all your records try the following.

When you open the form store the the last PK ID or the last timestamp the easiest way is in an unbound hidden text box. Now in the conditional formatting compare this txtbox value to the records matching field - if it is later then it must have been added since the form was opened.

 

[Stingone]

Assuming this is all done on one form and you have either a primary key, or a datetime stamp on all your records try the following.

When you open the form store the the last PK ID or the last timestamp the easiest way is in an unbound hidden text box. Now in the conditional formatting compare this txtbox value to the records matching field - if it is later then it must have been added since the form was opened.

I have 2 queries, the append is most important i use indeed a timestamp date()

So you are saying add a txtbox in the form hide it. Then compare the timestamp date with the date of now()? dont get it fully.

 

[Minty]

Nearly - when the form opens you have a timestamp of lets say 29/09/2017 09:30:23. You store this in a hidden text box on the form. Now any records that are added will have a time stamp after the one you have stored in the text box.

If you ensure your update queries also set the time stamp to Now() they will also fit the same criteria.

 

[Stingone]

Ah ok get it. I use a splash screen where it loads the queries. So i should use that timestamp of opening the splash screen and compacte it to the date/time of the queries running after that.

 

[Minty]

Yes - You may want to think of providing some method of resetting the check time, unless you want to see all updates for the whole time the splash form is open.

 

[Stingone]

Yes - You may want to think of providing some method of resetting the check time, unless you want to see all updates for the whole time the splash form is open.

Just created a txtbox on the splash with now() that passes the value to my form that contains the value from a query. The check works however i now ran into the issue that more people work in the same data base. so if someone opens the database and that person is the one that adds the records someone else cant see its new. Is there a option to log the date a user has logged in last time and use that date?

 

[Minty]

Yes - of course but it is not without with issues. You would store in a local table, the last time they closed the database. You then can use that to set the text box value on load.

The issues are - if they don't close the database down in the proper way, the code that updates their "exit time" won't run and your last usage date is now incorrect.

 

[Stingone]

Yes - of course but it is not without with issues. You would store in a local table, the last time they closed the database. You then can use that to set the text box value on load.

The issues are - if they don't close the database down in the proper way, the code that updates their "exit time" won't run and your last usage date is now incorrect.

I'm a step ahead now. i created a database to store username and time they open the splash form. now i need to compare the username on open the from with the username in the table and get that date to compare. any ideas to do this lean?

 

[Minty]

This sounds as if you have multiple users using the same shared database - Have you not split the database and given each user their own local copy of the Front end database?

If not, you need to for a large number of reasons, not least possible corruption, difficulties like you are now experiencing , and easier development going forwards.

 

[Stingone]

This sounds as if you have multiple users using the same shared database - Have you not split the database and given each user their own local copy of the Front end database?

If not, you need to for a large number of reasons, not least possible corruption, difficulties like you are now experiencing , and easier development going forwards.

max 8 users. In couple of months getting access to sharepoint to store the data. So yes you are right but for the time being.. i just created a dlookup works good but it pickes the oldest date instead of the newest.. so need to see if can change the script.. almost there..

 

[Stingone]

This sounds as if you have multiple users using the same shared database - Have you not split the database and given each user their own local copy of the Front end database?

If not, you need to for a large number of reasons, not least possible corruption, difficulties like you are now experiencing , and easier development going forwards.

Got it working using DMax... thanks for the support

 

[Minty]

Good to hear. I would even with only 8 users strongly recommend splitting this now. Reasons (and there are more than this);
A) Good experience in advance of your move to sharepoint.
B) Any issues you encounter now will be only Access related, and if you encounter problems you'll have a better idea of how to cope.
C) Get into the nuts and bolts of maintaining and distributing your front end. You'll need this functionality anyway if you are keeping Access as you FE.
D) Avoid the corruption and record locking nightmare that is a shared database.