Access 2007 Form Table Update

add a date field to the table, and update it when you press the button you use to update.
see http://office.microsoft.com/en-ca/access-help/store-the-date-and-time-when-a-record-is-modified-HA010241284.aspx

Unless you are already storing a date field (Last_Modified is what I generally use to keep track of when records are updated), then adding a date field for every record may be a bit redundant.  

Another method would be to create a table that is used solely for that purpose; for keeping track of when you last clicked on one of those Delete/Update buttons.  Add an update SQL statement that updates the date field in that table for the appropriate table when ever the user clicks on one of those buttons.

Then it would simply be a matter of using a DLOOKUP against that table when the Admin form loads.  You could make the DLOOKUP( ) function the ControlSource for a textbox which is locked or disabled.

If he is already updating n rows he may as well update the date field at the same time (same SQL)

by: fyed (Dale Fye)

I like your suggestion but not sure what to type for the update SQL statement?

sorry Dale, maybe I missed a point.
CMiller
Is the intent to keep track of last 'batch' update, or last time each record is updated?

I use a macro "TransferSpreadSheet" to update my tables

Batch update

Then Dale's plan is best: and (if you are not using SQL) extend you macro to update the new field in the new table.

@coachman99,

You had a valid point, thus my first sentence.  If the table already had a Date/Time column your suggestion would have made lots of sense.

I have several applications where I do bulk updates like this.  I generally store the name of the file uploaded, the date it was uploaded, and have an ID column associated with each upload, all stored in tbl_Uploads.

When I write the data being uploaded to its final destination, I also write the ID value associated with the upload to the table that the data is going in to allow me to quickly reverse an upload if the client realizes there was an error or wants to re-upload the file after making changes.

@fyed (Dale Fye)

How do I add it to my import module? I created a new tbl_Update to store the timestamp date.

DoCmd.TransferSpreadsheet acImport, 9, "TableName", strFile, True, "SheetName!"

@fyed (Dale Fye)

Is it possible to to add  , set timestamp = Now() into my ipmort module?

Over all still not sure how to add the timestamp into the DB

I am getting an error at:

DoCmd.RunSQL "UPDATE tbl_Uploads SET timestamp = Now()"

what is the error?
try
UPDATE Table1 SET Table1.[timestamp] = Now();

I fixed the error but its not updating the timestamp field in the tbl_Uploads table

DoCmd.RunSQL "UPDATE tbl_Uploads SET tbl_Uploads.timestamp = Now()"

what is the error?
is timestamp defined as date/time?

I dont get an error now when I run it due to changing the syntax to the following.

DoCmd.RunSQL "UPDATE tbl_Uploads SET tbl_Uploads.timestamp = Now()"

I checked and timestamp is defined as date/time, format is short date.

When I run it I get a popup stating that "0" rows will be updated.

**********************************************************************************

I just manually picked a date for the timestamp field, now when I run it, it updates the timestamp field.

so are we good?

I believe so, let me check something first. Thanks!

Its all good!

Thanks, guys!!