Solved Updating A Field Upon Data Entry

[Bean Machine]

Hi Everyone! I have a query that pulls data from a table and allows the user to edit the amount of inventory on hand. They then have to change the "Date Updated" field to the day they changed the on hand amount. I was wondering, to make it a lot easier (since there are about 133 items and counting), can I make it so that when new data is entered into the "On Hand" field the "Date Updated" field auto populates the current system date to save a lot of manual work? Thanks for any help you guys can provide!

 

[theDBguy]

Hi. You said "new data," If so, you can use a Default Value.

 

[Bean Machine]

Hi. You said "new data," If so, you can use a Default Value.

I should have made it more clear but for most items they are updated annually, so the only new data is the updated amount on hand. The default value worked for when I added new items but I need something for when an items on hand amount is being updated. Something like: When "On Hand" changed "Date Updated" = Date()

I hope I cleared things up. Thanks for the reply as always!

 

[zeroaccess]

Before Update event of field

Me.Modified = Now()

Change field name to match yours

 

[theDBguy]

I should have made it more clear but for most items they are updated annually, so the only new data is the updated amount on hand. The default value worked for when I added new items but I need something for when an item is being updated. Something like: When "On Hand" changed "Date Updated" = Date()

I hope I cleared things up. Thanks for the reply as always!

For something like that, I would suggest using a data macro.

 

[Bean Machine]

For something like that, I would suggest using a data macro.

Tried changing the "AfterUpdate()" part of the "On Hand" field and it worked. I can't believe I missed such a simple step, you guys made me think though. Effectively I changed it so that when someone changes the data in the "On Hand" field it will change the "Date Updated" field to today's date like so:

Me.txt_DateUpdated.Value = Date()

 

[theDBguy]

Tried changing the "AfterUpdate()" part of the "On Hand" field and it worked. I can't believe I missed such a simple step, you guys made me think though. Effectively I changed it so that when someone changes the data in the "On Hand" field it will change the "Date Updated" field to today's date like so:

Me.txt_DateUpdated.Value = Date()

Hi. Glad to hear you got it sorted out. Just in case it applies, take a look at this article. Good luck with your project.

Allen Browne - Inventory Control: Quantity on Hand

How do calculate the quantity-on-hand for inventory applications, rather than rely on a stored value.

allenbrowne.com