1. Use ONE field to hold date/time, not two. Two just makes more work for you.
2. I never use macros. If you only want to update the modified date when the quantity changes, then in the AfterUpdate event of the quantity control, put.
Me.DateModified = Now()
FYI, you are already doing this. Now() = date AND time but Date() = just today's date. Therefore, DateModified already includes date + time. If you had really wanted it to only contain just the date, you should have used Date() rather than Now()
3. The code behind the form runs only when the record is changed using the form. If you also run update or append queries and you want those to also reflect the modified date, you would include the ModifiedDate column in the columns to update. Set its value to Now().
3a. You should probably switch to using a Data Macro instead. I don't use Data Macros because they apply only to ACE and most of my BE's are SQL Server so if you can't figure this out from the documentation (I didn't see an example that does what you want to do), someone else can probably help better. I don't know if the Data Macro can be set to run when only a specific column is changed or if the DM can use an IIf() to compare the current/old values for a column and use that to determine when to run. If you can't distinguish, using the data macro whether a specific column was changed, then stick with VBA in the quantity control on the form for the manual changes. Append queries would just populate the ModifiedDate but Update queries would have to have an IIf() to decide whether or not to update the ModifiedDate.
So, Data Macros, if they can be controlled at the column level would be a one-stop-shop way to handle the problem and you would remove the code from the AfterUpdate event. But, if Data Macros can't be controlled at the column level, then you have three different ways to do the update but you will be able to accurately control updating the ModifiedDate.
