Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.


Automatic date for single column modification

Automatic date for single column modification


I'm new to this website and to MS Access (and my job too, but that's less relevant). I'm trying to create a database for several hundred components of a machine for tracking which components are in stock, which need to be made, what they're made of, technical drawings etc etc.

My question is:

If I have a column for "Number in stock" and one for "Stock updated", can I automatically fill in the latter with the date that the former is updated?

I know there is a way to automatically date stamp when any of the columns for an entry are modified, but I want to just track when the stock number column is changed.

Any advice would be much appreciated.

RE: Automatic date for single column modification


Are you only keeping track of components for ONE machine?

Is this machine a company capital asset or a product that gets produced for sale?


glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Automatic date for single column modification

It is for one model of machine that my company produces for sale. It has a standard template but several optional extras. Most systems are built to order with the buyers choice of options but we also want to maintain a small stock of standard units.

The database will make it easier to keep track of the parts we have and which we need to send to the machine shop for manufacture, but it will be good to know how up to date the stock list is.


RE: Automatic date for single column modification

WOW!  Your company has no inventory system?  

Do they have any sort of MRP system?


glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Automatic date for single column modification

It's a small company. There is an inventory system for materials and standard components that are bought in but each system that is built is in some way different to the others and an inventory of machined components is unneccesary because they are machined to order for each job. The exception is one particular model of machine which is becoming more standardised and is looked after by a small team which I have just joined. This database is separate from the main company inventory system. We're getting off topic though. Any thoughts on the date column?

RE: Automatic date for single column modification

It is not at all off topic. Your small company is proceeding down a very dangerous path by isolating various data repositories that would benefit the company tremendously if they were better inetgrated. As you grow, it will become more aparent that this isolation of data will inhibit your company from competing in your target market.

Someone in your company ought to explore integrating your data/systems.

Are you using a FORM as the user interface to update your table?  That is where the date can be assigned when a change occurs.


glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Automatic date for single column modification

Interesting, thanks. The system they have here seems to work pretty well though. They've been going for a few decades and have more or less stabilised in terms of size. Without going into the particulars of how the company is set up (which I'm still only learning myself) it's hard to convey, but my team has very little overlap with most of the others and the data that does overlap is already well integrated with the main inventory system.

I'm sure there's always room for improvement, like anywhere, and they're probably thinking about it but it's not a priority at the moment. Perhaps when I've been here longer I can bring it up.

Anyway, I was going to use a form to update the table, yes. Should I use an expression?


RE: Automatic date for single column modification

You can just assign Now() to that row's date stamp when a change occurs.

However, when I do analysis on an inventory system, I often want to know ALL the dates when things happened to a particular inventory item.  This can be done using a TRANSACTION table, where you record individual transactions with a designation to tell you what kind of transaction it is, the quantity associated with that transaction and the date/time of the transaction, maybe an inventory location.  The inventory balance is then CALCULATED each time you run a query against the table.

For instance first you have an Inventory Adjustment to assign the starting value.  Then you Issue a quantity to a job, Your Receive a quantity from a vendor.  You Issue a quantity to another location and you Receive the quantity into a location, etc.


glassesJust traded in my old subtlety...
for a NUANCE!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close