Solved

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

Posted on 2014-09-08
21
2,399 Views
Last Modified: 2015-11-10
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?
0
Comment
Question by:VirtualKansas
  • 9
  • 6
  • 3
  • +2
21 Comments
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 40310300
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
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40310415
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
 
LVL 84
ID: 40310447
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 40310462
Access db upgrade to Access web app
I would call that an oxymoron.
0
 

Author Comment

by:VirtualKansas
ID: 40310467
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
 

Author Comment

by:VirtualKansas
ID: 40310468
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
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40310481
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
 

Author Closing Comment

by:VirtualKansas
ID: 40310491
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 40310593
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
 

Author Comment

by:VirtualKansas
ID: 40310616
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
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 34

Expert Comment

by:PatHartman
ID: 40310834
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
 

Author Comment

by:VirtualKansas
ID: 40310842
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
 

Author Comment

by:VirtualKansas
ID: 40331016
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
 
LVL 84
ID: 40331059
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
 

Author Comment

by:VirtualKansas
ID: 40331285
Table
0
 

Author Comment

by:VirtualKansas
ID: 40331286
Macro
0
 

Author Comment

by:VirtualKansas
ID: 40331288
Error
0
 

Expert Comment

by:bryanca888
ID: 41214884
was there ever resolution to this?  i have the same issue...
0
 
LVL 84
ID: 41215725
branyca888: You should post your own question to get one-on-one assistance.
0
 

Expert Comment

by:bryanca888
ID: 41216814
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
 
LVL 84
ID: 41219604
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

744 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

16 Experts available now in Live!

Get 1:1 Help Now