Access 2103 Web App: date field for record update automatic populate

Grinding out an Access db upgrade to Access web app.  Making great progress, thanks to EE team.  Now to next challenge...

The original desktop db had a field that "... field automatically inserts the date the record was last edited or changed."  On import, the field came in from the root table, as a simple date/time field.

Keeping with the original intent of the field, how to develop the web app. to do the same on record add/update, so that this field is automatically is populated with date and time for record created and updated?
VirtualKansasAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Setting the date created is fairly simple - just set the Default value of the column to Date()

Setting the date updated is more difficult, and most likely cannot be done with the same methods you used previously. You can use the new Data Macro to do this, however. Basically, you create and AfterUpdate macro, and use the SetField action, then set the Name to YourTableName.YourFieldName, and Value to Now. Note that you must use TableName.FieldName, even though you're obviously referring to the table where the action is run.
0
 
sammySeltzerCommented:
If I understand correctly, you want to add new records and after inserting records, current date is automatically inserted, no?

Insert INTO yourTable(datefield) values(date())

and if you wish to grab date and time, then use now()

Same with updating of records

Update Table set datefield = date() or now()

Is this what you are after?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
BTW, here's a screenshot of the macro I used. This will update the value in a field named Date1 anytime I make edits and save them:
DataMacro AfterUpdate
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
PatHartmanCommented:
Access db upgrade to Access web app
I would call that an oxymoron.
0
 
VirtualKansasAuthor Commented:
Hi Scott and thank you.  I've got an unrelated chore am getting through, before I can integrate this answer (yet this afternoon.)  One thought, just as an FYI to make sure I'm forming the question correctly:

The data in the app. currently is an import from Access and as such does have data in the fields.  My goal is to update the date/time field, when a record is either created or edited.

Still OK for testing AfterUpdate macro?
0
 
VirtualKansasAuthor Commented:
Hi Pat RE:  "an oxymoron."  I get that; just struggling to form my questions in a meaningful way, while flying by the set o' pants.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Still OK for testing AfterUpdate macro?
I'm fairly confident the AfterUpdate would work for new records as well - but as I mentioned earlier, just set a Default on the field in Table Design view. This will "stamp" that value for all New Records, and the AfterUpdate will definitely stamp the value for any updated records.
0
 
VirtualKansasAuthor Commented:
Makes sense, thank you.  Apologies for simple details, but seat of the pants making me (more than) a little brain dead.  Really appreciate the speed and thoroughness of replies, helps more than you know...
0
 
PatHartmanCommented:
Sorry Virtual, I wasn't trying to pick on you.  I was just offering an opinion.  MS has been trying to webify Access for at least four versions over the course of more than 10 years and so far has failed whereas the second release of Access was an outstanding success and in the over 20 years since that second release, MS has tweaked and poked and added good stuff and bad but it is still the best client/server RAD tool on the market.  Yet, they fail to understand what people mean when they say "access my data over the web" and keep giving us stuff that is half baked.

Good luck.  Hope you don't run into a total blockage due to the lack of a necessary feature.
0
 
VirtualKansasAuthor Commented:
Hi Pat; flying by the seat of my pants, here.  So far we're pretty excited about the results.  We're bridging the old front end to the new app. back end with very good success.  

Have to say, it is a chore to re-create the data views to look like the old data input forms but that's the fastest way to get the new adopted.  Connections to SQL/Azure for old Excel reports so we don't have to re-create them is the current challenge but have to say also that MSFT support has been pretty responsive, too (wow, didn't think I would ever type that in a sentence - Go,  Nadella Go!!!)

We're a pretty unique application, but everyone is.  The challenge we are solving is in oil & gas, we're a highly distributed bunch with no central office and field offices popping in and out like popcorn.  SharePointify'ing our tried and true database and other CM(S) is opening huge doors for ability to deliver for our clients.  But it don't mean nothing if it don't get used; so starting with tried & true db and then upsizing to the Cloud app. is our mission.  

Thanks to all at EE for helping make American oil & gas transmission more effective!
0
 
PatHartmanCommented:
It is good to hear a success story.  Sounds like you don't have much code in the app so the macro limitation isn't getting in your way and you apparently don't do any automation either.   Think about writing a paper about the challenges you faced and how you overcame them.  

There isn't a single application I have created in the past 10 years that could be converted to a web app.  Too much code, too much event processing, too much interaction with other applications such as Windows, Word, Outlook, and Excel, too many reports.
0
 
VirtualKansasAuthor Commented:
Interesting thing is we're relatively simple in data, but very complex in documents.  We deal with land rights, so we look more like a Frankenstein grafted together of law and real estate practice.  Our clients consume the data for engineering, survey and construction.  They're more data intense than we are, we just have to form and create the data in a way that translates reliably.  

Write a paper?  Oh, boy; the title would be "Smart guy kind of knows what he's doing and better get it done fast before the "old way" wins out."  With a sub-heading of "Thank God for a community of people who actually know what they're doing and are willing to help!"
0
 
VirtualKansasAuthor Commented:
Crud, I'm back to this task.  Followed the process by adding after update macro and get error:

"The data macro was stopped because an endless loop was detected. Please check the data macro for any actions that might be running the macro from within itself."

Thoughts?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you post a screenshot of your Macro, with as much detail as possible (even if it's a couple of screenshots)? Often this means you're modifying a Field in the AfterUpdate macro of that field (which would run forever).
0
 
VirtualKansasAuthor Commented:
Table
0
 
VirtualKansasAuthor Commented:
Macro
0
 
VirtualKansasAuthor Commented:
Error
0
 
bryanca888Commented:
was there ever resolution to this?  i have the same issue...
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
branyca888: You should post your own question to get one-on-one assistance.
0
 
bryanca888Commented:
perhaps i dont understand the forum here, but he did attach screen shots and no answer was provided.  did he get the answer sent to him in a private message?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Not that I'm aware of. He may have figured it out on his own, of course, but it's not unusual for someone to post additional comments, and then accept a solution. They also didn't really post enough information to receive a comment on the "endless loop" issue - the data macro they posted simply set a field value to Now, which wouldn't cause the endless loop.

The reason you post a NEW question is that you cannot award points here, in this question, and also because the Experts won't really see your request for help (other than myself and any other Expert who posted). If you post a new question, you put it in front of ALL Experts, which gives you a much better chance at a solution.
0
All Courses

From novice to tech pro — start learning today.