VBA Code to record/update the date a procedure was last run

[VWSpeedyB]

I have code to extact a table and e-mail to designated e-mail addresses. How can I have Access record/update the "Date of Last Update" when my function is run (button command for on-click processing)

I'm sure that there is already a thread (or more) which has been answered covering this topic. However, no matter how I searched, I could not locate one.

[ssanfu]

You don't state where (which table) the field for "Date of Last Update" is located, but I would update the field for "Date of Last Update" to Date(). Or if you are keeping a history of emails for the data, you could use an append query.

Don't have enough info about your dB to be more specific.

[VWSpeedyB]

Steve - thank you for the quick answer.

I'm going to keep track of when the e-mails are sent out in a table separate from where the data is stored so that I don't have to update thousands of fields with the update. Basically, the e-mail will be sent to 4 separate groups. So, the tracking table will have 4 fields to update each time the extraction takes place. I believe that I can accomplish this with a sub within my extraction code, but 1) i'm not positive, 2) if possible, I don't have the expertise to accomplish it. Additional thoughts or advisment?


Data Table - Import SKU List
Data from this table is output to an excel file and then attached to my e-mail and sent direct from my DB.

Last Updated Table - 2 columns (Distribution Email and Last Update)

So, within my function to extract and e-mail the attachment, how would I write the append/update to overwrite the previous date with the new execution date?

[ssanfu]

So, the tracking table will have 4 fields to update each time the extraction takes place

Table names and field names??

You can create an update query, set the Update To row to Date() for the date columns, then switch to SQL view to see what the SQL looks like and add it to your code (button click).

[VWSpeedyB]

Steve -

Thanks again for the guidance. Ok, I've aded the following code to my existing distribution query:

With cdomsg
Update Table_Name
Set Table_Name.Field_Name = Date
WHERE Table_Name.Field_Name = "Criteria"

I try to run the code, and then receive an error stating:

Run-time error '438'
Object doesn't support this property or method

I tried to debug, but all it does is highlight the lines. I have tried playing around with the syntax, with no success. As stated in my profile, I have zero actual programming or code writing experience, so all your asssitance is very appreciated.

[John_G]

The Update is a SQL statement, and SQL statements need to be in quotes or in a string variable and then executed, for example:

strSQL = "Update Table_Name Set Field_Name1 = Date() WHERE Field_Name2 = 'Criteria' "
currentdb.execute strSQL, dbfailonerror

There are other slightly different versions of the technique - that's the one I use.

I'm assuming the two Field_Name's are different.

[VWSpeedyB]

Steve & John -

While I do appreciate your assistance, I was unable to apply the recommendations you provided. I truly belive this has to do with my lack of programming/coding knowledge and not your explanations or directions. Thank you again for your help. Ultimately, I was able to create the correct coding (converted from a Macro):

DoCmd.OpenQuery "update query", acViewNormal, acEdit
DoCmd.Close acQuery, "Update query"

I added those two lines to my existing command lines, and it works.